Add a Foreign Key in MySQL

Add a foreign key in MySQL to tie a row in one table to a row in another, and to
stop the two from drifting apart. A FOREIGN KEY makes the
database reject any child row that points at a parent that does not exist. First, you create
the parent table. Then a REFERENCES clause links the child to
it. Finally, ON DELETE CASCADE keeps the two in step when a
parent is removed. This needs the InnoDB engine, which is MySQL’s default.

Requirements to add a foreign key in MySQL:

• MySQL 8.4 (tested against 8.4.10). Foreign keys work on MySQL 5.6+ and MariaDB, as long
  as both tables use InnoDB.
• Terminal access to the mysql client, logged in to a database where you can create
  tables.

How To Add a Foreign Key in MySQL.

The objective is to link a books table to an authors table, so every book
belongs to a real author. We enforce that link on insert, and then let a deleted author take
its books with it.

Step 1.

First, create the parent table. A foreign key can only point at a column that is a key on
the other side, so authors gives id a
PRIMARY KEY. The ENGINE = InnoDB
clause is explicit here, though it is already the default.

CREATE TABLE authors (
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(80) NOT NULL
) ENGINE = InnoDB;

The referenced column and the foreign-key column must share a type. Because
authors.id is INT UNSIGNED, the child column has to be
INT UNSIGNED as well, or MySQL refuses the constraint.

Step 2.

Next, create the child table with the foreign key built in. The
CONSTRAINT line names the key, states which column carries it,
and points REFERENCES authors (id) at the parent. Then two
valid rows go in, each with an author_id that really exists.

CREATE TABLE books (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(120) NOT NULL,
    author_id  INT UNSIGNED NOT NULL,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (id)
        ON DELETE CASCADE
) ENGINE = InnoDB;

INSERT INTO authors (name) VALUES ('Ada Lovelace'), ('Alan Turing');
INSERT INTO books (title, author_id) VALUES
    ('Notes on the Analytical Engine', 1),
    ('Computing Machinery',            2);

Naming the constraint fk_books_author is optional but wise.
As a result, error messages name it, and you can drop it later by that name.

Step 3.

Then, watch the key do its job. Try to insert a book for author
99, who does not exist. Instead of storing a broken row,
MySQL stops the insert with error 1452.

INSERT INTO books (title, author_id) VALUES ('Orphan Book', 99);

This is the whole point of a foreign key. Because no author has id
99, the database refuses the row rather than let an orphan
slip in. Your data stays consistent without any application-side check.

Step 4.

Finally, delete an author and see the cascade. The constraint carries
ON DELETE CASCADE, so removing a parent row also removes the
child rows that point at it. Here, deleting Ada also deletes her book.

DELETE FROM authors WHERE id = 1;

SELECT id, title, author_id FROM books;

Only Alan’s book remains. To keep the child rows but blank the link instead, swap the rule
for ON DELETE SET NULL and allow the column to be
NULL. You can also add a key to a table that already exists
with ALTER TABLE books ADD CONSTRAINT ... FOREIGN KEY ....

Result of adding a foreign key in MySQL.

The valid rows load and join cleanly to their authors. The bad insert is rejected with a
1452 error that names the constraint, and after the cascade
delete only the surviving author’s book is left:

-- valid books joined to their authors
+----+--------------------------------+--------------+
| id | title                          | author       |
+----+--------------------------------+--------------+
|  1 | Notes on the Analytical Engine | Ada Lovelace |
|  2 | Computing Machinery            | Alan Turing  |
+----+--------------------------------+--------------+

-- inserting a book for a missing author is refused
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`library`.`books`, CONSTRAINT `fk_books_author`
FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE)

-- after DELETE FROM authors WHERE id = 1, the cascade removes Ada's book
+----+---------------------+-----------+
| id | title               | author_id |
+----+---------------------+-----------+
|  2 | Computing Machinery |         2 |
+----+---------------------+-----------+

Notes on the foreign key in MySQL:

• Both tables must use InnoDB. The older MyISAM engine parses a foreign key but silently
  ignores it, so nothing is enforced.
• The parent and child columns need the same type and sign. A signed
  INT referencing an INT UNSIGNED
  is the most common reason MySQL rejects the constraint.
• A foreign key indexes the child column for you. That index also speeds up joins, so you
  rarely need to add one by hand.
• Choose the delete rule deliberately. CASCADE deletes
  children, SET NULL keeps them but clears the link, and the
  default RESTRICT blocks the parent delete outright.
• A foreign key is the key that joins tables in the first place. Once it is in place, you can
  access the MySQL database from the terminal
  and query across both tables, or
  select MySQL data with PHP PDO
  by joining on it.

References:

• MySQL 8.4 Reference Manual — FOREIGN KEY Constraints
• MySQL — Using FOREIGN KEY Constraints
• MySQL — InnoDB and FOREIGN KEY Constraints