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

ON DELETE CASCADE and ON DELETE SET NULL are two important options in SQL foreign key constraints that define how the database handles related records in a child table when a record in the parent table is deleted. These options are crucial for maintaining referential integrity and ensuring a consistent database structure. This article will explain these concepts in detail with examples, syntax, advantages, disadvantages, and key differences.

Difference Between ON DELETE CASCADE and ON DELETE SET NULL

The following table lists all the major differences between ON DELETE CASCADE and ON DELETE SET NULL:

What is ON DELETE CASCADE?

The "ON DELETE CASCADE" for a foreign key constraint 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 of ON DELETE CASCADE

Step 1: Create the Parent Table

CREATE TABLE parent_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

Step 2: Create the Child Table with ON DELETE CASCADE

CREATE TABLE child_table (
    id INT PRIMARY KEY,
    parent_id INT,
    detail VARCHAR(100),
    FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE
);

Step 3: Insert Sample Data

-- Insert data into parent table
INSERT INTO parent_table (id, name) VALUES (1, 'Parent 1'), (2, 'Parent 2');

-- Insert data into child table
INSERT INTO child_table (id, parent_id, detail) 
VALUES (1, 1, 'Child of Parent 1'), (2, 1, 'Another Child of Parent 1'), (3, 2, 'Child of Parent 2');

Step 4: View the Data

SELECT * FROM parent_table;
SELECT * FROM child_table;

Output for child_table:

Step 5: Delete a Record from Parent Table

DELETE FROM parent_table WHERE id = 1;

Step 6: Check the Child Table

SELECT * FROM child_table;

Output

Explanation:

The rows in child_table with parent_id = 1 were automatically deleted because of the ON DELETE CASCADE constraint.

What is ON DELETE SET NULL?

The ON DELETE SET NULL option updates the foreign key column in the child table to NULL when the corresponding parent record is deleted. This approach preserves the child record while removing its reference to the parent. 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 of ON DELETE SET NULL

Step 1: Create the Parent Table

CREATE TABLE parent_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

Step 2: Create the Child Table with ON DELETE SET NULL

CREATE TABLE child_table (
    id INT PRIMARY KEY,
    parent_id INT,
    detail VARCHAR(100),
    FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE SET NULL
);

Step 3: Insert Sample Data

-- Insert data into parent table
INSERT INTO parent_table (id, name) VALUES (1, 'Parent 1'), (2, 'Parent 2');

-- Insert data into child table
INSERT INTO child_table (id, parent_id, detail) 
VALUES (1, 1, 'Child of Parent 1'), (2, 1, 'Another Child of Parent 1'), (3, 2, 'Child of Parent 2');

Step 4: Delete a Record from Parent Table

DELETE FROM parent_table WHERE id = 1;

Step 5: Check the Child Table

SELECT * FROM child_table;

Output:

Explanation:

The parent_id for the affected rows in child_table is set to NULL, preserving the child records.

Conclusion

In conclusion, the choice between 'ON DELETE CASCADE' and 'ON DELETE SET NULL' in database design depends on the specific requirements of the application and the desired behavior when dealing with parent and child records. ON DELETE CASCADE is ideal for maintaining strict referential integrity, while ON DELETE SET NULL is suitable for preserving child records without parent references