SQL Relationships

Overview

▾

Relationships define how tables interact in a relational database. Properly designed relationships enforce data integrity and simplify queries.

  • One‑to‑One: Each row in Table A relates to a single row in Table B.
  • One‑to‑Many: A row in Table A can relate to many rows in Table B.
  • Many‑to‑Many: Rows in Table A can relate to multiple rows in Table B and vice‑versa, typically implemented via a junction table.

Use FOREIGN KEY constraints to enforce relationships.

Foreign Key Constraints

▾

A foreign key links a column (or set of columns) in one table to the primary key of another table.

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE;

ON DELETE / ON UPDATE Options

▾

Control how changes propagate:

  • CASCADE – Propagate the delete/update.
  • SET NULL – Set the foreign key to NULL.
  • NO ACTION – Prevent the operation if related rows exist.
  • RESTRICT – Same as NO ACTION in most systems.

Best Practices

▾
  1. Always name foreign keys meaningfully (e.g., FK_Child_Parent).
  2. Use appropriate data types and lengths.
  3. Index foreign key columns for better join performance.
  4. Prefer ON DELETE CASCADE only when logical.
  5. Document relationship cardinality in ER diagrams.