Foreign Keys and Referential Integrity: Making Relationships Enforceable

Foreign keys: enforcing relationships in the schema

A foreign key (FK) is a constraint that makes a relationship enforceable: values stored in a “child” table must match an existing primary key (or other candidate key) value in a “parent” table (or be NULL if the relationship is optional). Without a foreign key constraint, you can still store “relationship-looking” columns, but the database will not prevent orphan rows, inconsistent references, or accidental deletions that break relationships.

In practice, converting ER relationships into relational tables means deciding where to place foreign key columns (or when to create a junction table) and choosing referential actions that match business policy.

1) Mapping one-to-many (1:N): put the FK on the “many” side

For a one-to-many relationship, store the primary key of the “one” side as a foreign key column in the “many” side table. This is the default mapping because each row on the many side points to exactly one parent (or possibly none, if optional).

Step-by-step mapping

• Identify the parent entity (the “1” side) and the child entity (the “N” side).
• Add a foreign key column to the child table that references the parent’s primary key.
• Set the FK column to NOT NULL if the relationship is mandatory for the child; allow NULL if optional.
• Add an index on the FK column if you will frequently join or filter by it (common in transactional systems).

Example

Relationship: Customer (1) places Order (N). Each Order must belong to exactly one Customer.

Customer(customer_id PK, ...)

Order(order_id PK, customer_id FK -> Customer(customer_id), ...)

If orders can exist before a customer is finalized (rare, but possible in some workflows), then Order.customer_id might be nullable; otherwise make it NOT NULL.

Continue in our app.

• Listen to the audio with the screen off.
• Earn a certificate upon completion.
• Over 5000 courses for you to explore!

Or continue reading below...

Download the app

2) Handling one-to-one (1:1): merge vs. keep separate

One-to-one relationships require more design judgment because there are two valid relational mappings. The key question is whether the two entities should be stored in one table (merge) or two tables linked by a foreign key with a uniqueness rule (keep separate).

Option A: merge tables (single table)

Merge when the relationship is truly one-to-one and the attributes are used together most of the time, and when splitting would add complexity without clear benefits.

• Good when both sides have the same lifecycle (created/updated/deleted together).
• Good when the “optional” side is small and sparsely populated only if NULLs are acceptable.
• Avoid if it would create many NULL columns or mix unrelated concerns (e.g., security vs. profile).

Example: If every employee always has exactly one badge record and badge attributes are small, you might store badge fields directly in Employee.

Option B: keep separate with FK + UNIQUE (two tables)

Keep separate when you want to isolate optional data, sensitive data, large/rarely used columns, or different lifecycles. In the relational model, you enforce 1:1 by making the foreign key unique (or by sharing the primary key).

Pattern 1: FK with UNIQUE (child has its own PK plus a unique FK):

Person(person_id PK, ...)

Passport(passport_id PK, person_id FK -> Person(person_id) UNIQUE, ...)

This ensures at most one Passport per Person.

Pattern 2: shared primary key (child PK is also FK):

Person(person_id PK, ...)

PersonProfile(person_id PK/FK -> Person(person_id), ...)

This enforces exactly one profile row per person profile record and makes the relationship tightly coupled. Whether it is “exactly one” or “zero or one” depends on whether you require a matching row in the child table at all times (often enforced by application logic or triggers; the FK alone enforces that if a child exists, it must match a parent).

Choosing merge vs. separate: quick checklist

• Separate if the secondary data is optional for many rows, large, sensitive, or has different access rules.
• Merge if the data is always present, small, and typically queried together.
• Separate if you need different referential actions or different retention policies.

3) Mapping many-to-many (M:N): use a junction table

Relational tables cannot directly store a many-to-many relationship with a single foreign key column. Instead, create a junction table (also called associative table or bridge table) that contains two foreign keys—one to each side. Each row in the junction table represents one association.

Step-by-step mapping

• Create a new table for the relationship.
• Add foreign key columns referencing each parent table.
• Choose a primary key strategy for the junction table: typically a composite PK of the two FKs, or a surrogate PK plus a UNIQUE constraint on the FK pair.
• If the relationship has attributes (e.g., quantity, role, assigned_date), store them in the junction table.

Example

Relationship: Order (M) contains Product (N), with attribute quantity.

Order(order_id PK, ...)

Product(product_id PK, ...)

OrderItem(order_id FK -> Order(order_id), product_id FK -> Product(product_id), quantity, PRIMARY KEY(order_id, product_id))

The composite primary key prevents duplicate product lines for the same order. If you need multiple lines for the same product (e.g., different discounts or batches), use a line number:

OrderItem(order_id FK -> Order(order_id), line_no, product_id FK -> Product(product_id), quantity, PRIMARY KEY(order_id, line_no))

4) Referential actions: RESTRICT, CASCADE, SET NULL as business-policy choices

A foreign key constraint answers two questions: (1) can a child reference a non-existent parent? (no), and (2) what happens when the parent key is updated or the parent row is deleted? The second part is controlled by referential actions (also called referential integrity actions). These are not “technical defaults”; they encode business policy.

Common actions

• RESTRICT / NO ACTION: prevent deleting/updating the parent if children exist. Use when child rows must not exist without the parent and you want explicit cleanup decisions.
• CASCADE: automatically delete/update child rows when the parent is deleted/updated. Use when child rows are conceptually owned by the parent and have no meaning on their own.
• SET NULL: when the parent is deleted/updated, set the FK in the child to NULL. Use when the relationship is optional and the child can remain without a parent (but you still want to keep the child record).

How to choose (practical guidance)

• Use CASCADE on delete for “composition” relationships: e.g., Order -> OrderItem. If an order is removed, its line items should not remain.
• Use RESTRICT on delete for “reference” relationships: e.g., Product referenced by OrderItem. You typically cannot delete a product that appears on historical orders; you might instead mark it inactive.
• Use SET NULL on delete when the child can survive independently and the link is optional: e.g., Ticket assigned to an Agent; if an agent leaves, tickets can become unassigned.

Be cautious with cascading updates of primary keys. In many designs, primary keys are treated as stable identifiers, so updates are rare; if updates can happen, ensure cascading updates won’t create unexpected write amplification.

Worked example: from ER relationships to enforceable tables

Consider a small order management domain with these entities and relationships (described as an ER diagram in words):

• Customer places Order (1:N). Each Order must have exactly one Customer.
• Order contains Product (M:N) with attribute quantity.
• Customer has LoyaltyCard (1:1). A customer may have zero or one loyalty card; each loyalty card belongs to exactly one customer.

Step 1: Create base tables for entities

Customer(customer_id PK, full_name, email)

Order(order_id PK, order_date)

Product(product_id PK, sku, name, unit_price)

LoyaltyCard(card_no PK, issued_date)

Step 2: Map 1:N (Customer -> Order) by adding FK to the many side

Add customer_id to Order and make it NOT NULL (mandatory relationship).

Order(order_id PK, customer_id FK -> Customer(customer_id) NOT NULL, order_date)

Referential action choice: typically ON DELETE RESTRICT (or NO ACTION) because deleting customers with historical orders is usually not allowed; you might anonymize instead.

Step 3: Map M:N (Order - Product) using a junction table

Create OrderItem with two FKs and relationship attribute quantity.

OrderItem(order_id FK -> Order(order_id) NOT NULL, product_id FK -> Product(product_id) NOT NULL, quantity NOT NULL, PRIMARY KEY(order_id, product_id))

Referential action choices:

• OrderItem.order_id should usually be ON DELETE CASCADE because order items are owned by the order.
• OrderItem.product_id should usually be ON DELETE RESTRICT to protect order history; alternatively, keep products and mark them inactive.

Step 4: Map 1:1 (Customer - LoyaltyCard) with FK + UNIQUE (keep separate)

Because a customer may have no card, and card details may be managed separately, keep a separate table and enforce 1:1 by making the FK unique.

LoyaltyCard(card_no PK, customer_id FK -> Customer(customer_id) UNIQUE, issued_date)

Make LoyaltyCard.customer_id NOT NULL if every loyalty card must belong to a customer (typical). Referential action choice: often ON DELETE CASCADE from Customer to LoyaltyCard if the card should not exist without the customer record; or RESTRICT if deletion is not allowed and you prefer explicit handling.

Resulting table list (PKs, FKs, junction table)

• Customer: customer_id (PK), full_name, email
• Order: order_id (PK), customer_id (FK -> Customer.customer_id, NOT NULL), order_date
• Product: product_id (PK), sku, name, unit_price
• OrderItem (junction): order_id (FK -> Order.order_id), product_id (FK -> Product.product_id), quantity, PK(order_id, product_id)
• LoyaltyCard: card_no (PK), customer_id (FK -> Customer.customer_id, UNIQUE), issued_date