SQL and MySQL course Β· Module 9: Indexes and normalization
Foreign keys and data integrity
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_iddoes not exist inautorzy, - 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_idtoNULL(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. What is a foreign key (FOREIGN KEY) used for?
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.