Basic16 min read

Relationships & Constraints

Model one-to-many and many-to-many relationships with foreign keys and constraints.

Foreign Keys

Foreign keys enforce referential integrity between parent and child tables. ON DELETE and ON UPDATE actions (CASCADE, RESTRICT, SET NULL) define behavior when parent rows change.

Use RESTRICT or NO ACTION when deletes should fail if children exist, protecting accidental data loss. CASCADE deletes children automatically—use only when business rules require it.

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT
);

One-to-Many

The classic pattern stores the parent primary key on the child table (orders.user_id → users.id). Queries join child to parent; indexes on foreign key columns are essential for join performance.

Embed rarely queried parent attributes on child rows only when read patterns justify denormalization and you accept update anomalies.

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

Many-to-Many

Implement many-to-many with a junction table containing foreign keys to both entities. The junction table primary key is often the composite of both foreign keys or a surrogate id plus unique constraint on the pair.

Name junction tables clearly (user_roles, product_tags) and index both foreign key columns for efficient lookups from either direction.

CREATE TABLE user_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

Constraints

PRIMARY KEY uniquely identifies rows. UNIQUE prevents duplicate values. NOT NULL rejects nulls. CHECK constraints (MySQL 8.0.16+) validate expressions on insert/update.

Combine constraints at the database layer with application validation for user-friendly error messages. Database constraints are the last line of defense.

Use UNSIGNED for IDs that never go negative
Document cascade behavior in ER diagrams for the team
Defer foreign key checks only during controlled bulk imports

CREATE TABLE products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(50) NOT NULL UNIQUE,
  price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

Schema Design Tips

Normalize to third normal form first, then denormalize selectively for proven bottlenecks. Avoid nullable foreign keys unless the relationship is truly optional.

Use soft deletes (deleted_at column) with unique indexes that include deleted_at or partial unique indexes where supported to preserve email uniqueness among active users only.