Introduction
Foreign keys
Multicolumn foreign keys
Updates and deletes
20. REFERENCES explained
Revision

Instruction

Just as we expected - we failed. Why is that so? Because when we write REFERENCES... after FOREIGN KEY (...), we actually mean REFERENCES ... ON UPDATE RESTRICT ON DELETE RESTRICT. In other words, the database won't allow to update or delete a primary key which is linked to another table with a foreign key.

There are, however, other options too. Instead of RESTRICT, we can use:

  1. CASCADE - when the referenced row is deleted or updated, the respective rows of the referencing table will be deleted or updated.
  2. NO ACTION - do nothing to the the referenced row (in our database this is the same as RESTRICT, in some databases NO ACTION is slightly different from RESTRICT).
  3. SET NULL - the values of the affected rows are set to NULLs.
  4. SET DEFAULT - the values of the affected rows are set to their default values.

Exercise

Run the template code to create a new version of the table project - this time with CASCADE. Take a close look at how it's done.

Console

Code editor

Result

TableConsole