A one-to-many relationship is a relationship between two entities in which one instance of the first entity can be associated with many instances of the second, while each instance of the second is associated with at most one instance of the first. In data modeling for relational databases, it is the most common relationship type: one customer places many orders, one department employs many people, one blog post has many comments.
In a relational schema, a one-to-many relationship is implemented by placing a foreign key in the child table that references the primary key of the parent table. The parent is the one side, the child is the many side, and the foreign key column is where the relationship physically lives.
How a one-to-many relationship is implemented
The implementation rule is simple: the foreign key always goes on the many side. Each child row stores the identifier of exactly one parent, and any number of child rows may store the same identifier. Placing a list of child identifiers in the parent row would violate first normal form and make queries and integrity checks far harder.
A foreign key constraint makes the database enforce the relationship: it rejects a child row that points to a nonexistent parent and decides what happens when a parent is deleted or its key changes. An index on the foreign key column is strongly recommended, because joins from parent to children and the integrity checks on parent deletes both search by that column; PostgreSQL does not create this index automatically, while MySQL InnoDB does.
| Element | Location | Purpose |
|---|---|---|
| Primary key | Parent table | Uniquely identifies each parent row |
| Foreign key column | Child table | Stores the identifier of the related parent |
| Foreign key constraint | Child table definition | Enforces that the parent exists |
| Index on the foreign key | Child table | Speeds joins and parent deletes |
| NOT NULL on the foreign key | Child table | Makes the parent mandatory |
Cardinality and optionality
A full description of a one-to-many relationship states both cardinality, meaning the maximum number on each side, and optionality, meaning the minimum. ER diagrams express both with crow’s foot notation or with min-max pairs such as (0,N) and (1,1).
| Variant | Meaning | Implementation |
|---|---|---|
| Mandatory parent, optional children | Every order belongs to a customer; a customer may have no orders | NOT NULL foreign key |
| Optional parent | An employee may be unassigned to a department | Nullable foreign key |
| At least one child required | An invoice must have at least one line | Application logic or deferred constraint trigger |
| Self-referencing | An employee reports to one manager who is also an employee | Foreign key to the same table |
Relational constraints enforce the maximum of one parent and the presence of the parent directly, but a minimum number of children is not expressible as a simple constraint. Rules such as at least one line per invoice usually live in the application or in triggers that run at commit time.
One-to-many compared with other relationship types
Recognizing the correct cardinality is a core skill in data modeling, because the choice determines the table structure. The comparison shows how one-to-many relates to its neighbors.
| Relationship | Example | Relational implementation |
|---|---|---|
| One-to-one | Person and passport record | Foreign key with a unique constraint, or shared primary key |
| One-to-many | Customer and orders | Foreign key in the child table |
| Many-to-one | Orders and customer, seen from the order | The same structure read from the other side |
| Many-to-many | Students and courses | Junction table with two foreign keys |
Many-to-one is the same relationship viewed from the child side, not a different structure. A many-to-many relationship, by contrast, cannot be stored with a single foreign key; it is decomposed into two one-to-many relationships that meet in a junction table. In a star schema, every link from a fact table to a dimension table is a one-to-many relationship from the dimension to the facts.
Referential actions on delete and update
When a parent row is deleted or its key changes, the database must decide what happens to its children. Referential actions declared on the foreign key make that decision explicit and consistent.
| Action | Effect on child rows | Suitable when |
|---|---|---|
| RESTRICT or NO ACTION | Parent change is rejected while children exist | Children have independent value, such as orders |
| CASCADE | Children are deleted or updated with the parent | Children are owned parts, such as order lines |
| SET NULL | Foreign key becomes NULL | The relationship is optional |
| SET DEFAULT | Foreign key takes its default value | A placeholder parent exists |
CASCADE deletes deserve caution. A cascade chain across several levels can remove far more data than intended with a single statement, and the deleted rows may lock large parts of the child tables while it runs.
Querying and modeling pitfalls
Most problems with one-to-many relationships appear in queries and object mappings rather than in the schema itself.
- Row multiplication in joins: joining a parent to two different child tables at once multiplies rows and inflates sums; aggregate each child separately.
- The N+1 query problem: an ORM that loads children lazily runs one query per parent; use joined or batched loading.
- Missing foreign key index: deleting one parent forces a full scan of the child table for the integrity check.
- Wrong cardinality assumption: a relationship modeled as one-to-many often turns out to be many-to-many, such as products and categories.
- Embedding unbounded children: in document databases, an ever-growing array of children inside one parent document eventually exceeds size limits.
Put simply
A one-to-many relationship means that one thing owns or groups many other things, but each of those belongs to only one. A mother can have several children. Each child has one birth mother.
In a database, each child record carries a note saying which parent it belongs to. To find all the children of one parent, the database looks for every record with that note. The parent itself never needs to keep a list.
In practice
A small accounting application stores invoices and invoice lines in two tables. The invoice_lines table has an invoice_id column with a NOT NULL foreign key to invoices, declared with ON DELETE CASCADE, because a line has no meaning without its invoice. A second relationship links invoices to customers with ON DELETE RESTRICT, so a customer with invoices cannot be removed by accident.
A monthly revenue report originally joined customers to both invoice lines and payments in one query, and totals came out inflated for customers with several payments. The developer aggregated lines and payments separately in two CTEs before joining them to customers, and added an index on invoice_lines.invoice_id. The report became correct to the cent, and deleting a draft invoice no longer scanned the 20-million-row lines table.