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.

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

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.