DiagramDB / Primary vs Foreign Key

Primary Key vs Foreign Key

A primary key says “this is row 42”. A foreign key in another table says “I belong to row 42”, and the database refuses anything that would break that promise. Try to break it below.

Referential integrity sandbox

authors (id is the primary key)
id PK	name	Action
1	Mo
2	Iris
3	Tomás

posts (**author_id** is a foreign key)
id PK	author_id FK	title
100	1	Indexing 101
101	1	Why NULL ≠ NULL
102	2	Picking keys

Try inserting a post for author 9, or deleting Mo while RESTRICT is on.

The DDL behind this sandbox

CREATE TABLE authors (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE posts (
  id        INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES authors(id)
            ON DELETE RESTRICT,
  title     TEXT NOT NULL
);

Side by side

	Primary key	Foreign key
Purpose	Identifies each row of its own table	Points to a row in another (or the same) table
Uniqueness	Always unique	Duplicates allowed (many posts, one author)
NULL	Never	Allowed unless NOT NULL
How many per table	At most one (may span columns)	Any number
Index	Created automatically	Automatic in MySQL/InnoDB only; add one elsewhere
Enforces	Entity integrity	Referential integrity
ER diagram	Marked PK, usually first column	Marked FK, the line starts here

Choosing ON DELETE behaviour

RESTRICT / NO ACTION (the default): refuse to delete a parent that still has children. Safest; forces you to decide what happens to the children.
CASCADE: delete the children too. Right for true ownership, like order lines of an order, and dangerous for anything else.
SET NULL: keep the children but unlink them. The foreign key column must be nullable.

Composite and self-referencing keys

A junction table for a many-to-many relationship, such as post_tags(post_id, tag_id), usually uses both foreign keys together as its primary key, which also prevents tagging a post twice. A foreign key can also point at its own table: employees.manager_id REFERENCES employees(id) models a hierarchy.

Keys are what the lines in an ER diagram represent; paste the DDL above into the ER diagram generator to see it drawn, and learn to read the line ends on the crow’s foot notation page.

Questions

What is the difference between a primary key and a foreign key?

A primary key uniquely identifies each row in its own table and can never be NULL. A foreign key is a column in another table that stores a primary key value to point at a row there, and the database rejects values that don’t exist in the referenced table.

Can a table have more than one primary key?

No, a table has at most one primary key, but that key can span several columns (a composite key), such as (order_id, product_id) in an order_items table. A table can have many UNIQUE constraints and many foreign keys.

Can a foreign key be NULL?

Yes, unless you declare it NOT NULL. A NULL foreign key means “no related row”, for example a comment whose author deleted their account. Use NOT NULL when the relationship is mandatory.

Does a foreign key have to reference a primary key?

It must reference a column set that is unique: the primary key or a UNIQUE constraint. Referencing the primary key is by far the most common.

Should I use natural or surrogate primary keys?

A surrogate key (an auto-increment integer or a UUID) never changes and has no business meaning, which makes it the safer default. Natural keys such as email addresses or ISBNs can change or turn out not to be unique; keep them as UNIQUE constraints instead.

Are foreign keys indexed automatically?

It depends. MySQL’s InnoDB creates an index on a foreign key column if none exists. PostgreSQL and SQL Server do not, so add an index yourself on foreign keys you join or delete by, or cascades and joins will scan the whole child table.