When we say a relationship has "1:N cardinality," we're specifying the maximum associations: each entity on the 'one' side can associate with at most N entities on the 'many' side, and each entity on the 'many' side with at most one on the 'one' side. But this tells only half the story.

Minimum cardinality addresses the other half: Is participation required or optional? Must every employee belong to a department (minimum = 1)? Or may some employees exist without department assignment (minimum = 0)? This constraint—often called the participation constraint—has profound implications for schema design, data integrity, and business rule enforcement.

The distinction between maximum cardinality (1:1, 1:N, M:N) and minimum cardinality (0 or 1+) is fundamental, yet often confused. Together, they form the complete cardinality specification. This page untangles the concepts, demonstrates their practical impact, and shows how to implement minimum cardinality constraints in relational databases.

Minimum cardinality specifies the least number of relationship instances in which an entity must participate. For practical purposes, minimum cardinality is typically:

• 0 = Optional participation (entity may exist without this relationship)
• 1 = Mandatory participation (entity must participate at least once)
• n > 1 = Specific minimum (rare, but possible—e.g., 'a team must have at least 5 members')

Formal Definition:

For a relationship R between entity sets E₁ and E₂, the minimum cardinality constraint from E₁'s perspective specifies the minimum number of E₂ entities that each E₁ entity must relate to through R.

The Complete Cardinality Specification:

A fully specified cardinality constraint includes both minimum and maximum:

(min, max) where:
- min ∈ {0, 1, 2, ...}    (lower bound)
- max ∈ {1, N}            (upper bound, often unbounded)

Common patterns:

When we say "1:N," we're stating max cardinality. Complete specification requires also stating minimum (participation).

Minimum cardinality is often expressed through the lens of participation constraints:

Total Participation (min ≥ 1): Every entity in the set MUST participate in the relationship. No entity exists without at least one relationship instance.

Partial Participation (min = 0): Entities MAY participate but are not required to. Some entities in the set have no relationship instances.

These terms are more intuitive than 'minimum cardinality' and are widely used in database design discussions.

Diagrammatic Representation:

Chen Notation:

Total participation: Double line connecting entity to relationship
Partial participation: Single line

[EMPLOYEE]════════<WORKS_IN>────────[DEPARTMENT]
     ↑                                    ↑
  Double line                        Single line
  (total: every                      (partial: dept
   employee must                      may have no
   work somewhere)                    employees)

Crow's Foot Notation:

Total participation: Bar symbol (|)
Partial participation: Circle symbol (○)

[DEPARTMENT] |○──────────⅄| [EMPLOYEE]
              ↑             ↑
           Optional      Mandatory
          (dept side)    (emp side)

Min-Max Notation:

Total participation: min ≥ 1
Partial participation: min = 0

[DEPARTMENT]──(0,N)──<EMPLOYS>──(1,1)──[EMPLOYEE]
               ↑                  ↑
            min=0              min=1
           (partial)           (total)

Minimum cardinality constraints are implemented through various SQL mechanisms, depending on which side of the relationship and what constraint level is needed.

Case 1: Minimum = 0 (Optional) on Many Side

Allowing NULL in the foreign key column:

CREATE TABLE employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    department_id   INT,              -- NULL allowed: optional participation
    FOREIGN KEY (department_id) REFERENCES department(department_id)
);

-- Valid: employee without department
INSERT INTO employee (employee_id, name, department_id)
VALUES (1, 'John Doe', NULL);

Case 2: Minimum = 1 (Mandatory) on Many Side

Using NOT NULL on the foreign key column:

CREATE TABLE employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    department_id   INT NOT NULL,     -- NOT NULL: mandatory participation
    FOREIGN KEY (department_id) REFERENCES department(department_id)
);

-- Invalid: employee without department
INSERT INTO employee (employee_id, name, department_id)
VALUES (1, 'John Doe', NULL);  -- ERROR: violates NOT NULL

The NOT NULL constraint is the primary mechanism for enforcing minimum = 1 on the FK (many) side.

Case 3: Minimum ≥ 1 on One Side (Parent must have children)

This is harder—SQL doesn't natively support 'at least one child' constraints. Options:

Option A: Trigger-based enforcement

-- Postgres example: prevent empty departments
CREATE OR REPLACE FUNCTION prevent_empty_department()
RETURNS TRIGGER AS $$
BEGIN
    -- After employee is deleted or moved
    IF NOT EXISTS (
        SELECT 1 FROM employee 
        WHERE department_id = OLD.department_id
    ) THEN
        RAISE EXCEPTION 'Department % must have at least one employee', 
            OLD.department_id;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_dept_not_empty
    AFTER DELETE OR UPDATE ON employee
    FOR EACH ROW
    WHEN (OLD.department_id IS NOT NULL)
    EXECUTE FUNCTION prevent_empty_department();

Option B: Deferred constraints (for initial creation)

-- Allow initial insert, check at transaction end
ALTER TABLE department
ADD CONSTRAINT dept_must_have_employees
    CHECK (
        EXISTS (SELECT 1 FROM employee e 
                WHERE e.department_id = department.department_id)
    )
    DEFERRABLE INITIALLY DEFERRED;

Note: Not all databases support this syntax. PostgreSQL supports deferrable constraints; MySQL support is limited.

Case 4: Specific Minimum (min = n > 1)

Example: A team must have at least 5 players.

-- Using CHECK constraint with subquery (limited support)
CREATE TABLE team (
    team_id         INT PRIMARY KEY,
    name            VARCHAR(100),
    CONSTRAINT min_players CHECK (
        (SELECT COUNT(*) FROM player WHERE player.team_id = team.team_id) >= 5
    )
);

This won't work in most databases—CHECK constraints typically can't reference other tables.

Alternative: Trigger-based enforcement

CREATE OR REPLACE FUNCTION enforce_min_team_size()
RETURNS TRIGGER AS $$
DECLARE
    player_count INT;
BEGIN
    SELECT COUNT(*) INTO player_count 
    FROM player 
    WHERE team_id = OLD.team_id;
    
    IF player_count < 5 THEN
        RAISE EXCEPTION 'Team must have at least 5 players (current: %)', 
            player_count;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_min_team_size
    AFTER DELETE OR UPDATE ON player
    FOR EACH ROW
    EXECUTE FUNCTION enforce_min_team_size();

Minimum cardinality constraints are direct translations of business rules into database schema. Understanding this connection helps you extract accurate constraints from requirements and defend design decisions.

Eliciting Participation Requirements:

When gathering requirements, ask specific questions:

Example dialogue:

Q: Can an employee exist without being in a department? A: "New hires are without department for their first week during onboarding." → Partial participation (min = 0) for EMPLOYEE in WORKS_IN

Q: Can a department exist with no employees? A: "Yes, when we create a new department before hiring begins." → Partial participation (min = 0) for DEPARTMENT in EMPLOYS

Both sides have min = 0 in this case, reflecting business reality.

Case Study: E-Commerce Order System

Entities: CUSTOMER, ORDER, ORDER_ITEM, PRODUCT

Business rules and their translations:

Rule: "Every order must belong to a customer."
→ ORDER.customer_id NOT NULL

Rule: "An order must have at least one item."
→ Trigger: prevent ORDER creation without ORDER_ITEM
   OR: Create ORDER and first ITEM atomically

Rule: "Customers can exist without orders (prospective customers)."
→ No constraint on CUSTOMER (partial participation in PLACES)

Rule: "Products can exist without being ordered (catalog items)."
→ No constraint on PRODUCT (partial participation in CONTAINS)

Rule: "Every order item must reference a valid product."
→ ORDER_ITEM.product_id NOT NULL + FK constraint

Resulting schema with participation annotations:

CREATE TABLE customer (
    customer_id   INT PRIMARY KEY,
    name          VARCHAR(100)
    -- Partial participation in PLACES (may have 0 orders)
);

CREATE TABLE "order" (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL,   -- Total participation in BELONGS_TO
    order_date    TIMESTAMP
);

CREATE TABLE order_item (
    line_id       INT PRIMARY KEY,
    order_id      INT NOT NULL,   -- Total participation in CONTAINS
    product_id    INT NOT NULL,   -- Total participation in REFERENCES
    quantity      INT NOT NULL
);

Real-world requirements often involve participation constraints that depend on conditions—an entity's participation in one relationship depends on its state or participation in other relationships. These conditional participation constraints require careful modeling.

Pattern 1: Status-Dependent Participation

Rule: "Published articles must have at least one category; drafts may have no category."

This means participation depends on the article's status:

CREATE TABLE article (
    article_id    INT PRIMARY KEY,
    title         VARCHAR(200),
    status        VARCHAR(20) CHECK (status IN ('draft', 'published'))
);

CREATE TABLE article_category (
    article_id    INT NOT NULL,
    category_id   INT NOT NULL,
    PRIMARY KEY (article_id, category_id)
);

-- Constraint: published articles must have categories
-- Cannot express directly; use trigger
CREATE OR REPLACE FUNCTION check_published_has_category()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'published' AND NOT EXISTS (
        SELECT 1 FROM article_category 
        WHERE article_id = NEW.article_id
    ) THEN
        RAISE EXCEPTION 'Published articles must have at least one category';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The participation constraint isn't constant—it depends on the entity's status attribute.

Pattern 2: Exclusive Participation (XOR)

Rule: "A payment must be by credit card OR bank transfer, not both, and not neither."

This is exclusive participation across multiple relationships:

CREATE TABLE payment (
    payment_id           INT PRIMARY KEY,
    amount               DECIMAL(10,2),
    credit_card_id       INT REFERENCES credit_card(card_id),
    bank_transfer_id     INT REFERENCES bank_transfer(transfer_id),
    -- XOR constraint: exactly one must be non-null
    CONSTRAINT exclusive_payment CHECK (
        (credit_card_id IS NOT NULL AND bank_transfer_id IS NULL)
        OR
        (credit_card_id IS NULL AND bank_transfer_id IS NOT NULL)
    )
);

Alternative: Polymorphic association (less common in traditional ER)

CREATE TABLE payment (
    payment_id       INT PRIMARY KEY,
    amount           DECIMAL(10,2),
    payment_type     VARCHAR(20) NOT NULL,   -- 'credit_card' or 'bank_transfer'
    payment_ref_id   INT NOT NULL            -- References appropriate table
);

This sacrifices FK integrity but simplifies the constraint.

Pattern 3: Dependent Participation

Rule: "If an employee is a manager, they must supervise at least one employee."

CREATE TABLE employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100),
    is_manager      BOOLEAN DEFAULT FALSE,
    manager_id      INT REFERENCES employee(employee_id)
);

-- Constraint: if is_manager = true, must have subordinates
CREATE OR REPLACE FUNCTION validate_manager_has_subordinates()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.is_manager = TRUE AND NOT EXISTS (
        SELECT 1 FROM employee 
        WHERE manager_id = NEW.employee_id
    ) THEN
        RAISE EXCEPTION 'Managers must have at least one subordinate';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The participation constraint on SUPERVISES depends on the is_manager attribute.

Participation constraints often have a temporal dimension: they may apply differently at creation time, during the entity's lifecycle, or based on historical vs. current state. Understanding temporal participation prevents common modeling errors.

Creation-Time vs. Lifetime Participation:

Rule: "Every order must have items."

Question: Must an order have items at creation time, or can items be added later?

Interpretation A: At creation The order and its first item must be created atomically:

BEGIN TRANSACTION;
    INSERT INTO orders (order_id, customer_id, order_date) 
    VALUES (1, 100, NOW());
    
    INSERT INTO order_items (order_id, product_id, quantity) 
    VALUES (1, 500, 2);
COMMIT;
-- Only commits if both succeed

Interpretation B: Eventually Order can be created first, items added later, but validation occurs before finalization:

-- Order created
INSERT INTO orders (order_id, customer_id, status) 
VALUES (1, 100, 'draft');

-- Items added later
INSERT INTO order_items (order_id, product_id) VALUES (1, 500);

-- Constraint checked at submission
UPDATE orders SET status = 'submitted' WHERE order_id = 1;
-- Trigger validates: must have items to submit

Current State vs. Historical Participation:

Rule: "An employee must belong to a department."

Question: Does this mean currently, or at all times in history?

Scenario: Employee joins Dept A, transfers to Dept B, Dept A is dissolved.

Current-state model:

CREATE TABLE employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100),
    department_id   INT NOT NULL,  -- Current department only
    FOREIGN KEY (department_id) REFERENCES department(department_id)
);

Historical model:

CREATE TABLE department_assignment (
    assignment_id   INT PRIMARY KEY,
    employee_id     INT NOT NULL,
    department_id   INT NOT NULL,
    start_date      DATE NOT NULL,
    end_date        DATE,          -- NULL = current
    FOREIGN KEY (employee_id) REFERENCES employee(employee_id),
    FOREIGN KEY (department_id) REFERENCES department(department_id)
);

The historical model tracks the complete assignment history but requires more complex queries to determine current department.

Hybrid approach:

-- Current in employee table for fast access
CREATE TABLE employee (
    employee_id         INT PRIMARY KEY,
    current_department  INT NOT NULL REFERENCES department(department_id)
);

-- History in separate table
CREATE TABLE department_history (
    employee_id     INT NOT NULL,
    department_id   INT NOT NULL,
    start_date      DATE NOT NULL,
    end_date        DATE
);

Many-to-many relationships introduce additional complexity for participation constraints because the junction table adds an intermediate structure between the entities.

Example: Student-Course Enrollment

Rules:

• Every student must enroll in at least one course (total participation)
• Every course must have at least one student (total participation)

The schema:

CREATE TABLE student (
    student_id   INT PRIMARY KEY,
    name         VARCHAR(100)
);

CREATE TABLE course (
    course_id    INT PRIMARY KEY,
    title        VARCHAR(200)
);

CREATE TABLE enrollment (
    student_id   INT NOT NULL,
    course_id    INT NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Problem: The NOT NULL on FKs in ENROLLMENT doesn't enforce that every student appears in at least one enrollment row.

Enforcement requires triggers:

-- Enforce: every student must have at least one enrollment
CREATE OR REPLACE FUNCTION check_student_enrolled()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        IF NOT EXISTS (
            SELECT 1 FROM enrollment 
            WHERE student_id = OLD.student_id
        ) THEN
            RAISE EXCEPTION 'Student must remain in at least one course';
        END IF;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_student_enrollment
    AFTER DELETE ON enrollment
    FOR EACH ROW
    EXECUTE FUNCTION check_student_enrolled();

Similar trigger needed for course side.

Asymmetric M:N Participation:

More commonly, participation differs by side:

Rules:

• Students may exist without courses (new students, graduated, etc.)
• Courses must have at least one student (courses with no enrollment are cancelled)

This creates:

• Partial participation of STUDENT in ENROLLS
• Total participation of COURSE in ENROLLS

Schema implications:

-- No constraint on student side (partial)
-- Trigger only on course side (total)

CREATE OR REPLACE FUNCTION check_course_has_students()
RETURNS TRIGGER AS $$
BEGIN
    -- After enrollment deleted, check if course is empty
    IF NOT EXISTS (
        SELECT 1 FROM enrollment 
        WHERE course_id = OLD.course_id
    ) THEN
        -- Option A: Raise error
        RAISE EXCEPTION 'Course must have at least one student';
        
        -- Option B: Auto-cancel course (depends on business rule)
        -- UPDATE course SET status = 'cancelled' 
        -- WHERE course_id = OLD.course_id;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

We have thoroughly explored minimum cardinality—the essential complement to maximum cardinality that specifies whether relationship participation is mandatory or optional. Let's consolidate the key takeaways:

Module Complete:

With minimum cardinality mastered, you now possess the complete cardinality toolkit:

1. Maximum cardinality types: 1:1, 1:N, M:N
2. Notation fluency: Chen, Crow's Foot, Min-Max, UML
3. Minimum cardinality: Mandatory vs. optional participation

These concepts form the foundation for precise relationship modeling. As you design databases, you'll specify both what associations are allowed (maximum cardinality) and what associations are required (minimum cardinality)—capturing business rules in the schema itself.

The next module explores Participation Constraints in greater depth, connecting cardinality to integrity rules and enforcement strategies.