Foreign Key Relationship Guide for ER Diagrams

Foreign keys tell you which tables depend on other tables, but a good ER diagram also needs cardinality and optionality. This guide explains how to infer common relationship types from SQL DDL.

Quick Rules

DDL pattern	Likely relationship	Diagram note
Child table has a non-unique foreign key	One parent to many children	Example: users to orders.
Child table has a foreign key with UNIQUE	One parent to zero-or-one child	Example: users to user_profiles.
Join table has two foreign keys and a composite primary key	Many-to-many	Example: posts to tags through post_tags.
Foreign key column is nullable	Optional relationship	Example: employee.manager_id may be null.
Foreign key points to the same table	Self-referencing hierarchy	Example: categories.parent_id.

One-to-Many

The most common pattern is a child table with a foreign key to a parent table. The child can appear many times for the same parent.

CREATE TABLE customers (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

CREATE TABLE invoices (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  issued_at DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

In the ER diagram, read this as one customer can have many invoices, and each invoice belongs to one customer.

One-to-One Or One-to-Zero-One

A foreign key with a unique constraint usually means each parent can have only one matching child row. If the child row is optional, model it as one-to-zero-or-one.

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE user_profiles (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL UNIQUE,
  bio TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Many-to-Many

Relational databases represent many-to-many relationships with a join table. The join table often has two foreign keys and either a composite primary key or a unique constraint over both columns.

CREATE TABLE posts (
  id BIGINT PRIMARY KEY,
  title VARCHAR(200) NOT NULL
);

CREATE TABLE tags (
  id BIGINT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id BIGINT NOT NULL,
  tag_id BIGINT NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id),
  FOREIGN KEY (tag_id) REFERENCES tags(id)
);

Optional Relationships

Nullability affects the diagram. If a foreign key column allows NULL, the child row can exist without a parent relationship in that specific role.

CREATE TABLE employees (
  id BIGINT PRIMARY KEY,
  manager_id BIGINT NULL,
  full_name VARCHAR(120) NOT NULL,
  FOREIGN KEY (manager_id) REFERENCES employees(id)
);

This example is both optional and self-referencing: an employee may have no manager, and a manager may supervise many employees.

Checklist Before Publishing A Diagram

Check whether each foreign key column is nullable or required.
Check whether the foreign key column is unique.
Label join tables when they carry business data such as role, quantity, or timestamps.
Document relationships that exist in application logic but are missing from database constraints.

You can paste the examples into Free ER Diagram to compare how Mermaid and PlantUML represent each relationship.