SQL and MySQL course Β· Module 9: Indexes and normalization

Foreign keys and data integrity

2 min read Β·
In this lesson3

Our Library is a web of cross-references. A book scroll does not repeat the author's whole biography - instead it points to the catalog number of the scroll describing the creator. In MySQL such a cross-reference is a foreign key.

What a foreign key is

The autor_id column in the ksiazki table stores the id of an author from the autorzy table. A foreign key formalizes this relationship and makes the Library ensure that every autor_id points to an existing author.

ALTER TABLE ksiazki ADD FOREIGN KEY (autor_id) REFERENCES autorzy(id);

We read it: "The autor_id column in ksiazki refers to the id column in autorzy".

Referential integrity

Thanks to the foreign key the database will not allow you to:

  • add a book whose autor_id does not exist in autorzy,
  • delete an author still referenced by books (unless you decide otherwise).

This is referential integrity - a guarantee that the cross-references in the catalog never lead nowhere.

What to do on deletion?

You can specify what happens to the books when their author disappears:

ALTER TABLE ksiazki
ADD FOREIGN KEY (autor_id) REFERENCES autorzy(id)
ON DELETE CASCADE;
  • ON DELETE CASCADE - deleting an author automatically deletes their books (a cascade).
  • ON DELETE SET NULL - deleting an author sets autor_id to NULL (the book stays, but with no assigned author).

The choice depends on how you want to keep order in the Library. Foreign keys are the guardians of consistency - without them the catalog would soon fill with dead cross-references.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What is a foreign key (FOREIGN KEY) used for?

  2. 2. What does the referential integrity enforced by a foreign key guarantee?

These are 2 of 4 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Code editor

    Add to the ksiazki table a foreign key on the autor_id column that points to the id column in the autorzy table. Use ALTER TABLE.

  • Horizontal ordering

    Arrange on a single line the correct fragment that adds a foreign key (the REFERENCES part):

  • Code editor

    Add to the wypozyczenia table a foreign key on the ksiazka_id column that points to the id column in the ksiazki table. Use ALTER TABLE.

  • Vertical ordering

    Arrange the lines of the command that adds a foreign key with a cascading delete rule (as we write them in the editor):

  • Code editor

    Add to the wypozyczenia table a foreign key on the czytelnik_id column that points to the id column in the czytelnicy table. Use ALTER TABLE.

  1. QuestionWhy a foreign key
  2. QuestionReferential integrity
  3. Coding taskAdd a foreign key
  4. Put in orderThe autor_id foreign key
  5. QuestionON DELETE CASCADE
  6. QuestionON DELETE SET NULL
  7. Coding taskForeign key for loans
  8. Put in orderA foreign key with ON DELETE CASCADE
  9. Coding taskLoan to a reader

Useful articles