The phrases “ON DELETE CASCADE” and “ON DELETE SET NULL” refer to options that can be specified when defining foreign key constraints in relational databases. These options determine the behavior of the database system when a record in the parent table (referenced table) is deleted. Let’s explore each option:

ON DELETE CASCADE

When you specify an “ON DELETE CASCADE” for a foreign key constraint, it means that if a record in the parent table (referenced table) is deleted then all related records in the child table (referencing table) will be automatically deleted. This ensures the referential integrity by removing dependent records when the referenced record is removed.

Example SQL Syntax

CREATE TBALE parent_table_p (
           id INT PRIMARY KEY
)
CREATE_TABLE child_table_c(
           id INT PRIMARY KEY,
           parent_id INT,
           FOREIGN KEY (parent_id) REFERENCES parent_table_p(id) ON DELETE CASCADE
)

ON DELETE SET NULL

The phrase “ON DELETE SET NULL” is not a standard option in most relational databases. If you made the child table (referencing table) “ON DELETE SET NULL” this means if a record in the parent table (referenced table) is deleted then the corresponding entry or values in the child table (referencing table) will be set to NULL. This is another way to maintain reference integrity, allowing the child records to exist but with the NULL reference if the parent record is deleted.

Example SQL Syntax

CREATE TBALE parent_table_p (
           id INT PRIMARY KEY
)
CREATE_TABLE child_table_c(
           id INT PRIMARY KEY,
           parent_id INT,
           FOREIGN KEY (parent_id) REFERENCES parent_table_p(id) ON DELETE SET NULL
)

Difference Between ON DELETE CASCADE and ON DELETE SET NULL in DBMS