One-to-one, one-to-many, and many-to-many describe how rows can be associated. They do not mean the same thing as INNER JOIN, LEFT JOIN, or other query operators. A schema can contain a one-to-many relationship and still be queried with several join types. This reference explains the three relationship patterns, their keys, and optional links. You will model customers and profiles, departments and employees, and students and sessions. The examples use explicit IDs and generic SQL constraints, with engine-specific enforcement noted separately. Understanding these patterns helps you prevent duplicate links and avoid storing lists inside a single text column.
Count relationships in both directions
Ask how many B rows can relate to one A row, then ask the reverse. One department can have many employees, while each employee may belong to one department under the business rule. That is one-to-many. A student can attend many sessions, and a session can have many students, producing many-to-many. A customer may have at most one profile, and a profile belongs to one customer, producing one-to-one. Minimum counts describe optionality separately: a department may currently have no employees, and an employee may be temporarily unassigned if the schema permits it. Avoid choosing the type from today's sample data. One existing order does not imply the customer-order relationship is one-to-one.
Implement one-to-many on the many side
Put department_id in employees and reference departments.department_id. Each employee row then names its parent. Use NOT NULL when the assignment is mandatory; allow NULL only when an unassigned employee is a meaningful state. Do not add UNIQUE to department_id in employees unless you intend at most one employee per department. The parent table does not need a stored employee-ID list, because a query can retrieve children through the reference. Indexing the foreign-key column can help lookups and referential operations, but automatic index behavior differs across engines. A foreign key protects valid references, not every business rule about headcount, manager availability, or assignment dates.
Implement one-to-one with uniqueness
A profile table can use customer_id as both its primary key and a foreign key to customers. That shared-key design allows at most one profile for each customer and requires each profile to refer to a customer. Alternatively, use a separate profile_id and UNIQUE(customer_id). The latter can be useful when the profile has its own external identity. Neither pattern automatically requires every customer to have a profile row. If the business requires both records, create them within an appropriate transaction or choose a combined table when their lifecycle and access needs do not justify separation. Do not split columns into a second table merely to demonstrate one-to-one; separation should support a real responsibility, optionality, or access policy.
Implement many-to-many with a junction
Create enrollments with student_id and session_id foreign keys. A composite primary key on the pair prevents duplicate enrollment when the business permits only one enrollment per pair. Store enrollment_date, status, or grade there because those facts describe the association rather than the student or session alone. If repeated attempts or retained cancellation history need separate rows, give enrollment events their own key and define active uniqueness separately. A junction transforms one conceptual many-to-many into two one-to-many references. Query through it using joins; never keep session IDs in a comma-separated field. That text-list design makes constraints, searching, updates, and duplicates much harder to manage.
Choose referential actions according to lifecycle
Deleting a parent can be rejected, cascade to children, or set optional references to NULL depending on the schema and engine. These actions express lifecycle policy. CASCADE fits some dependent records, such as disposable junction links, but may be unacceptable for retained financial history. SET NULL requires a nullable child column and a meaningful unassigned state. A soft-deleted parent still exists for the foreign key, so application queries need an explicit active-state policy. Updating a natural key can require ON UPDATE behavior, while a stable surrogate key reduces that pressure. Review every action with the business meaning rather than selecting CASCADE everywhere to avoid errors.
Query without confusing schema cardinality
An INNER JOIN returns matching combinations, while a LEFT JOIN can retain parents without children. A many-to-many join can produce several rows for one student because each enrollment is a separate fact. Aggregate at the intended grain when reporting counts. COUNT(child_key) after a left join counts actual child rows rather than the placeholder row. Duplicate junction rows can inflate reports if the schema lacks pair uniqueness. Test a parent with no children, several children, a missing parent attempt, and a duplicate link. Verify constraints are enforced in your chosen engine; SQLite requires its foreign-key setting, and MySQL needs suitable engine and version support. The relationship design and query result shape must agree.
Practice task and worked solution
Model teams and players with a rule that a player may belong to one current team, while historical memberships must be retained. The solution distinguishes a current assignment from membership history rather than forcing all requirements into one nullable team_id. Define start and end dates and the rule for overlapping active memberships. Choose how the selected database enforces that rule, because ordinary foreign keys alone do not prevent temporal overlaps. Write a current-roster query and a historical-membership query. Test an unassigned player, a transferred player, and a forbidden overlapping membership. Explain why today's two sample players do not determine the maximum relationship cardinality.
Continue learning
Review relationships, constraints, and joins.
The SQLite foreign-key guide explains enforcement and referential-action behavior in that dialect.
One-to-One, One-to-Many and Many-to-Many Relationships Examples
Each example shows the code, the output it produces and what each part does.
A student-session junction
Use a disposable schema with foreign-key enforcement enabled. These explicit-ID definitions avoid engine-specific identity syntax.
Ada attends two sessions while Sam attends one. The pair key rejects a second identical student-session enrollment under this fixture's policy.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
name VARCHAR(80) NOT NULL
);
CREATE TABLE sessions (
session_id INTEGER PRIMARY KEY,
title VARCHAR(80) NOT NULL
);
CREATE TABLE enrollments (
student_id INTEGER NOT NULL REFERENCES students(student_id),
session_id INTEGER NOT NULL REFERENCES sessions(session_id),
PRIMARY KEY (student_id, session_id)
);
INSERT INTO students VALUES (1, 'Ada'), (2, 'Sam');
INSERT INTO sessions VALUES (10, 'SQL'), (20, 'PHP');
INSERT INTO enrollments VALUES (1, 10), (1, 20), (2, 10);
SELECT s.name, c.title
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN sessions c ON c.session_id = e.session_id
ORDER BY s.student_id, c.session_id;name | title
Ada | SQL
Ada | PHP
Sam | SQLOne-to-One, One-to-Many and Many-to-Many Relationships FAQ
Is one-to-many the same as LEFT JOIN?
No. Cardinality describes data rules: how many rows on one side can relate to the other. Joins describe query behaviour: which rows a particular query returns. A one-to-many relationship can be queried with an inner join, a left join or a subquery depending on the question.
Why add uniqueness to one-to-one?
A foreign key alone permits many children for the same parent. Adding UNIQUE to the child's foreign key column limits each parent to at most one child, which is what makes the relationship one-to-one.
Can a junction have extra columns?
Yes. Facts about the relationship itself belong there, such as the quantity of a product in an order, the role of a person in a project or the date a tag was added. They don't belong in either of the two linked tables.
Does nullable mean optional both ways?
No. A nullable foreign key describes the child reference: a child may exist without a parent. It says nothing about every parent-side minimum, such as whether each parent must have at least one child, which constraints alone can't easily enforce.
When should I use a one-to-one relationship?
When some attributes are optional, sensitive or rarely used, such as a customer's payment details or an employee's medical information. Keeping them in a separate table with a unique foreign key allows different permissions and keeps the main table lean. Otherwise, a single table is simpler.
One-to-One, One-to-Many and Many-to-Many Relationships Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
One department needs many employees. Should employees.department_id be unique?
One-to-One, One-to-Many and Many-to-Many Relationships Quiz
2 questions to check what you learned.
What normally implements many-to-many?
- A comma-separated list of related IDs in one parent column
- A foreign key on one table that can name only one related row
- A repeated set of session1 and session2 columns
- A junction table with two foreign keys
What changes a child foreign key into an at-most-one link?
- Uniqueness on the referencing key
- NOT NULL alone on the referencing key
- A foreign key alone without a unique constraint
- Using LEFT JOIN in every report