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');

That rejection 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.

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.

Your action is to insert one valid and one invalid post. Then attempt to delete the referenced author and record the database’s configured behavior.