One-to-One, One-to-Many, and Many-to-Many Relationships

Learning outcomes

Requirements describe how occurrences are associated: one customer places many orders, one person may have one profile, and many learners attend many offerings. Cardinality and optionality determine where foreign keys belong and which uniqueness constraints are required.

Cardinality and optionality

Cardinality describes the maximum number of related occurrences. Optionality describes whether participation is required. Together they produce ranges such as zero-or-one, exactly one, zero-or-many, or one-or-many.

One-to-many relationships

One-to-many is the most common pattern. The foreign key is stored on the many side.

CREATE TABLE customer (    customer_id INTEGER PRIMARY KEY,    email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE purchase_order (    order_id INTEGER PRIMARY KEY,    customer_id INTEGER NOT NULL,    ordered_at TEXT NOT NULL,    FOREIGN KEY (customer_id)        REFERENCES customer (customer_id)) STRICT;CREATE INDEX idx_order_customer    ON purchase_order (customer_id);

Do not store an array of order IDs inside the customer row. The child rows already express the relationship and remain independently queryable and constrained.

One-to-one relationships

A relational one-to-one is usually a foreign key with a UNIQUE constraint. The foreign key chooses the dependent side; uniqueness prevents more than one dependent from referencing the same principal.

CREATE TABLE learner (    learner_id INTEGER PRIMARY KEY,    email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE learner_profile (    learner_id INTEGER PRIMARY KEY,    biography TEXT,    avatar_url TEXT,    FOREIGN KEY (learner_id)        REFERENCES learner (learner_id)        ON DELETE CASCADE) STRICT;

This shared-primary-key design says that every profile belongs to exactly one learner and no learner can have more than one profile. A learner may still exist without a profile. Making profile participation mandatory for every learner cannot be guaranteed by this foreign key alone because the parent can be inserted before the dependent.

Should the tables be combined?

Sometimes one-to-one tables represent different security, lifecycle, size, ownership, or optionality boundaries. If no such boundary exists, one table may be simpler.

Many-to-many relationships

A direct many-to-many relationship is implemented with a junction—also called association, bridge, or link—table. The junction has one foreign key to each side, and the combination is normally unique.

CREATE TABLE learner (    learner_id INTEGER PRIMARY KEY,    email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE offering (    offering_id INTEGER PRIMARY KEY,    course_code TEXT NOT NULL,    starts_on TEXT NOT NULL) STRICT;CREATE TABLE enrollment (    learner_id INTEGER NOT NULL,    offering_id INTEGER NOT NULL,    enrolled_at TEXT NOT NULL,    status TEXT NOT NULL        CHECK (status IN ('active', 'completed', 'withdrawn')),    PRIMARY KEY (learner_id, offering_id),    FOREIGN KEY (learner_id)        REFERENCES learner (learner_id)        ON DELETE CASCADE,    FOREIGN KEY (offering_id)        REFERENCES offering (offering_id)        ON DELETE CASCADE) WITHOUT ROWID;

enrolled_at and status describe the relationship, not the learner or offering independently. A junction table is therefore often a real domain entity rather than invisible plumbing.

Relationship attributes and identity

The junction key depends on the rule. If a learner may enroll in the same offering only once, (learner_id, offering_id) is sufficient. If repeated attempts are allowed, include attempt_no or use a surrogate enrollment_id plus an appropriate alternate uniqueness rule.

Self-referencing relationships

A table can reference itself. This models trees, reporting lines, replacements, predecessors, and parent-child categories.

CREATE TABLE employee (    employee_id INTEGER PRIMARY KEY,    manager_id INTEGER,    full_name TEXT NOT NULL,    FOREIGN KEY (manager_id)        REFERENCES employee (employee_id)        ON DELETE SET NULL,    CHECK (manager_id IS NULL OR manager_id <> employee_id)) STRICT;CREATE INDEX idx_employee_manager    ON employee (manager_id);

The row-local check prevents direct self-management, but it does not detect longer cycles such as A manages B while B manages A. Cross-row graph rules may require recursive validation, a trigger, or carefully controlled application workflows.

Optionality belongs on the dependent side

Foreign keys are excellent at validating references that exist. They do not generally enforce a minimum child count for every parent. “Every course must have at least one instructor” may need transaction workflow, deferred validation, or another modeling approach.

Lab: model an academy relationship set

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS enrollment;DROP TABLE IF EXISTS learner_profile;DROP TABLE IF EXISTS offering;DROP TABLE IF EXISTS learner;CREATE TABLE learner (    learner_id INTEGER PRIMARY KEY,    email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE learner_profile (    learner_id INTEGER PRIMARY KEY,    biography TEXT,    FOREIGN KEY (learner_id)        REFERENCES learner (learner_id)        ON DELETE CASCADE) STRICT;CREATE TABLE offering (    offering_id INTEGER PRIMARY KEY,    course_code TEXT NOT NULL,    starts_on TEXT NOT NULL) STRICT;CREATE TABLE enrollment (    learner_id INTEGER NOT NULL,    offering_id INTEGER NOT NULL,    enrolled_at TEXT NOT NULL,    PRIMARY KEY (learner_id, offering_id),    FOREIGN KEY (learner_id)        REFERENCES learner (learner_id)        ON DELETE CASCADE,    FOREIGN KEY (offering_id)        REFERENCES offering (offering_id)        ON DELETE CASCADE) WITHOUT ROWID;INSERT INTO learner VALUES    (1, 'nadia@example.com'),    (2, 'omar@example.com');INSERT INTO learner_profile VALUES    (1, 'Data engineer and SQL learner.');INSERT INTO offering VALUES    (101, 'SQL-101', '2026-09-01'),    (102, 'DB-201', '2026-10-01');INSERT INTO enrollment VALUES    (1, 101, '2026-08-05'),    (1, 102, '2026-08-05'),    (2, 101, '2026-08-06');SELECT l.email, o.course_code, e.enrolled_atFROM enrollment AS eJOIN learner AS l ON l.learner_id = e.learner_idJOIN offering AS o ON o.offering_id = e.offering_idORDER BY l.email, o.course_code;

Common mistakes

Putting the foreign key on the wrong side

For one-to-many, the many side stores the reference. A customer row should not contain repeating order columns.

Calling a relationship one-to-one without uniqueness

A foreign key alone permits many children to reference one parent. Add UNIQUE or use a shared primary key.

Hiding junction attributes in another table

Quantity, role, and enrollment date belong to the association occurrence.

Assuming ORM navigation defines database integrity

Object navigation can be convenient, but database keys and constraints remain the durable relational enforcement layer.

Checkpoint and practice

Summary and next lesson

One-to-many uses a foreign key on the many side. One-to-one adds uniqueness or shares the primary key. Many-to-many uses a junction table, often with its own attributes. Nullability models optional dependent participation, while some parent minimum-participation rules need more than a foreign key. The next lesson classifies the integrity rules that protect these structures.

References

• Microsoft Learn: One-to-many relationships
• Microsoft Learn: Many-to-many relationships
• Microsoft Learn: Foreign and principal keys
• SQLite: Foreign Key Support