Mastering Foreign Keys and Table Relationships

What is a Foreign Key?

A foreign key (FK) is a constraint that establishes a link between a column in one table and the primary key of another table. Foreign keys define relationships between tables and enforce referential integrity.

For example, if an 'Orders' table has a user_id column referencing 'Users' table's id, you cannot create an order for a non-existent user. This is the core purpose of foreign keys.

Foreign Key Behavior Options

ON DELETE Options

Defines what happens to child rows when a parent row is deleted.

• RESTRICT (default): Reject parent deletion if children exist
  • Use case: Prevent deleting users who have orders
• CASCADE: Delete children when parent is deleted
  • Use case: Delete comments when post is deleted
• SET NULL: Set child FK to NULL when parent is deleted
  • Use case: Set author_id to NULL when user account is deleted
• NO ACTION: Similar to RESTRICT, checked at transaction end

ON UPDATE Options

Defines what happens to children when a parent's primary key changes. Primary keys typically don't change, but use ON UPDATE CASCADE when necessary.

The Three Types of Table Relationships

1:1 (One-to-One) Relationships

One entity corresponds to exactly one other entity.

Example: Users and Profiles

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE user_profiles (
  user_id UUID PRIMARY KEY,  -- Both FK and PK (enforces 1:1)
  full_name VARCHAR(200),
  bio TEXT,
  avatar_url TEXT,
  phone VARCHAR(20),

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

In a 1:1 relationship, use the parent's primary key as the child table's primary key. This guarantees uniqueness of user_id and enforces the 1:1 constraint.

When to use 1:1 relationships:

• Separating rarely accessed supplementary data
• Isolating sensitive data for security (e.g., password hashes)
• Representing optional associated data

1:N (One-to-Many) Relationships

The most common relationship type — one parent record can have multiple child records.

Example: Customers and Orders

CREATE TABLE customers (
  id UUID PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  customer_id UUID NOT NULL,  -- FK is on the "N" side
  total_amount DECIMAL(15, 2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  ordered_at TIMESTAMPTZ DEFAULT NOW(),

  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
);

In 1:N relationships, always add the foreign key to the "N" side (the child table). Orders belong to one customer, so orders table holds customer_id.

Important: Always add an index to the foreign key column. It makes a significant performance difference when querying all orders for a specific customer.

CREATE INDEX idx_orders_customer ON orders(customer_id);

N:M (Many-to-Many) Relationships

Multiple records can relate to multiple other records.

Example: Students and Courses

Direct N:M relationships cannot be expressed in SQL. They must always be implemented through a junction table (also called a bridge table or associative table).

CREATE TABLE students (
  id UUID PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  student_number VARCHAR(20) UNIQUE
);

CREATE TABLE courses (
  id UUID PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,
  title VARCHAR(200) NOT NULL,
  credits INTEGER NOT NULL
);

-- Junction table: implements N:M relationship
CREATE TABLE enrollments (
  student_id UUID NOT NULL,
  course_id UUID NOT NULL,
  enrolled_at TIMESTAMPTZ DEFAULT NOW(),
  grade VARCHAR(2),  -- Attributes of the relationship itself

  PRIMARY KEY (student_id, course_id),  -- Composite primary key
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

The advantage of junction tables is that the relationship itself can have attributes. enrolled_at (enrollment date) and grade belong to the enrollment relationship, not to students or courses individually.

Self-Referencing Relationships

Records within the same table reference each other.

Example: Organizational Hierarchy

CREATE TABLE employees (
  id UUID PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  position VARCHAR(100),
  manager_id UUID,  -- References the same table

  FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL
);

Example: Nested Comments

CREATE TABLE comments (
  id UUID PRIMARY KEY,
  post_id UUID NOT NULL,
  parent_id UUID,  -- NULL means top-level comment
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),

  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
);

Visualizing Relationships in ERDs

In Erdmates, setting a foreign key automatically draws a relationship line between two tables. The cardinality (1:1, 1:N, N:M) is visually represented, giving you a clear overview of your entire data model.

You can also ask the AI: "Connect the orders and order_items tables" and it will automatically set up the foreign key relationship.

Conclusion

Foreign keys and relationship design form the skeleton of a database. While it may seem complex at first, understanding the three basic relationship patterns (1:1, 1:N, N:M) gives you the tools to model data in any domain.