Foreign keys and data integrity

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.

1ALTER 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:

1ALTER TABLE ksiazki
2ADD FOREIGN KEY (autor_id) REFERENCES autorzy(id)
3ON 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.