course content

Course Content

Relational Database and Normalization

Foreign Key Constraints. ON DELETE OptionsForeign Key Constraints. ON DELETE Options

ON DELETE

A foreign key creates a dependency between two tables. This dependency is not conditional (like in non-relational databases), so it has certain constraints that must be set. These constraints are named ON DELETE options.

The ON DELETE option is an action that will complete when a dependent element (data from another table to which the foreign key is directed) is deleted.

Here's a short example of a primary key and a foreign key. Suppose you are deleting the record with the primary key. The ON DELETE action associated with the foreign key will be executed at this moment.

Note

DBMS offers to choose the ON DELETE option when you create a foreign key. But if you do it through SQL, you must do it yourself.

A foreign key cannot exist without an ON DELETE setting.

Main actions that you can set:

  • CASCADE
  • RESTRICT (or NO ACTION)
  • SET NULL
  • SET DEFAULT

CASCADE

If the primary element is deleted, all dependent elements (elements with foreign keys) will be deleted too.

carousel-imgcarousel-imgcarousel-imgcarousel-imgcarousel-img

If you have tables Group and Student (with foreign key relation to table Group) like this:

... and the foreign key relationship student.group_id -> group.id has the ON DELETE CASCADE option, which means that when a group is deleted, all students belonging to that group will also be deleted.

RESTRICT or NO ACTION

If a primary element has dependent elements, you cannot delete it unless you delete the dependent elements first. If you attempt to delete the primary element without deleting the dependent elements, the action will be prevented, and no changes will be made to the database.


SET NULL

If you delete the primary element, the dependent elements' foreign key will be set to a NULL value instead of being deleted.

Note

The foreign key should have a null=True parameter to choose ON DELETE SET NULL option!

SET DEFAULT

SET DEFAULT works similarly to SET NULL. If you delete the primary element, the dependent elements' foreign key will be set to a new value, but it's not null. You should specify the default value.

1. You want to destroy the whole world just by breaking a cup. Which ON DELETE option will you use?
2. Of course, parents protect their children, but what ON DELETE option is used so that children protect their parents?
3. You deleted the element and covered the tracks so no one would find out. Which ON DELETE option did you use to do this?
4. You want that when you destroy something, it replaces something new, for example gold. Which ON DELETE option will you use?

question-icon

You want to destroy the whole world just by breaking a cup. Which ON DELETE option will you use?

Select the correct answer

question-icon

Of course, parents protect their children, but what ON DELETE option is used so that children protect their parents?

Select the correct answer

question-icon

You deleted the element and covered the tracks so no one would find out. Which ON DELETE option did you use to do this?

Select the correct answer

question-icon

You want that when you destroy something, it replaces something new, for example gold. Which ON DELETE option will you use?

Select the correct answer

Section 2.

Chapter 2