Connect rows with foreign keys

A foreign key says that a value must identify an existing row in another table.

Suppose every post belongs to one author:

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

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL,
  title VARCHAR(300) NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

Now this insert fails when author 99 does not exist:

INSERT INTO posts (id, author_id, title)
VALUES (1, 99, 'Learning SQL');

The database rejects the row. That protects the relationship even when data arrives from a script or migration instead of the main application.

You must also choose what happens when a referenced author is deleted. Common actions are:

• reject the delete while posts still exist
• CASCADE and delete the posts too
• SET NULL when an authorless post is valid and the column permits NULL

Choose from the meaning of the data. Cascade is convenient when a child cannot exist alone, but one parent delete can become a large destructive operation. I review cascade rules carefully on production tables.

The foreign key checks integrity. It does not automatically make every join fast in every database. Index foreign-key columns when the workload frequently joins or checks rows through them, then verify the query plan with EXPLAIN.

You declare the delete action when you create the table:

FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE

Without ON DELETE, the database uses its default, which is often to reject the delete. Read your database docs before you assume.

Insert the parent row first, then the child. The foreign key check runs at insert time, not at table creation time. Both rows must exist in the right order.

Try this on your own: insert one valid and one invalid post. Then attempt to delete the referenced author and record the database’s configured behavior.