course content

Course Content

Relational Database and Normalization

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

ON DELETE

In relational databases, a foreign key establishes a relationship between two tables. This relationship is strict and comes with certain rules or constraints, known as ON DELETE options.

The ON DELETE option specifies the action taken when a record, linked by a foreign key, is removed from another table.

For illustration, consider a scenario involving a primary key and its associated foreign key. When you delete a record holding the primary key, the specified ON DELETE action for the corresponding foreign key is triggered.

Note

When creating a foreign key through a Database Management System (DBMS) interface, you'll often be prompted to select an ON DELETE option. However, if you're defining the relationship directly through SQL, you'll need to specify this option manually.

A foreign key requires an ON DELETE action to be defined.

Here are the main ON DELETE actions:

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

CASCADE

When the primary record is deleted, all related records (those referencing it via a foreign key) are also removed.

Image 1
Image 2
Image 3
Image 4
Image 5

For instance, if you have tables named Group and Student (where the student table has a foreign key pointing to the group table):

Student and Group relations

And if the foreign key relation from student.group_id to group.id is defined with the ON DELETE CASCADE option, then deleting a group will result in the removal of all students associated with that group.

RESTRICT or NO ACTION

You can't delete a primary record if there are related records in other tables. If you try, the action will be blocked, ensuring the integrity of the database.

SET NULL

Deleting the primary record will change the foreign key in the dependent records to NULL, rather than deleting those records.

Note

To utilize the ON DELETE SET NULL action, the foreign key column should allow null values.

SET DEFAULT

The SET DEFAULT action is similar to SET NULL. Deleting the primary record changes the foreign key in related records to a default value that you've specified, rather than to NULL.

1. When you drop a cup, and it feels like the world comes crashing down around you, which ON DELETE option mirrors that feeling?
2. Parents always have their children's backs, but if the tables were turned, which ON DELETE option would represent children protecting their parents?
3. You've stealthily removed a record, leaving no trace behind. Which ON DELETE option did you employ for such a covert operation?
4. If every time something breaks, you wish it would magically transform into gold, which ON DELETE option would make that dream a reality?

question-icon

When you drop a cup, and it feels like the world comes crashing down around you, which ON DELETE option mirrors that feeling?

Select the correct answer

question-icon

Parents always have their children's backs, but if the tables were turned, which ON DELETE option would represent children protecting their parents?

Select the correct answer

question-icon

You've stealthily removed a record, leaving no trace behind. Which ON DELETE option did you employ for such a covert operation?

Select the correct answer

question-icon

If every time something breaks, you wish it would magically transform into gold, which ON DELETE option would make that dream a reality?

Select the correct answer

Everything was clear?

Section 2. Chapter 2