SQL · Theory

On Delete Cascade

Theory

283/439

On Delete Cascade

FOREIGN KEY (roll) REFERENCES students(roll) ON DELETE CASCADE means deleting Asha deletes her marks rows too. ON DELETE SET NULL sets marks.roll to NULL (column must allow NULL). Default is reject the delete.

Use CASCADE only when the child has no meaning without the parent (marks without a student). Don’t CASCADE a student to a payments table you must keep for audit.

Trap — CASCADE on every FK ‘so DELETE works’.

On the example next to this theory: On Delete Cascade — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Exam tip

ON DELETE CASCADE vs SET NULL vs RESTRICT.

On Delete Cascade — sample query

CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (1, 'old'), (2, 'keep');
DELETE FROM tags WHERE label = 'old';
SELECT * FROM tags;

On Delete Cascade — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Short notes

DefON DELETE CASCADE removes children when the parent goes.
RuleOnly when children must not outlive the parent.
TrapCASCADE on audit/payment rows.

SQL · Theory

On Delete Cascade

← All stacks

Theory

283/439

On Delete Cascade

Use CASCADE only when the child has no meaning without the parent (marks without a student). Don’t CASCADE a student to a payments table you must keep for audit.

Trap — CASCADE on every FK ‘so DELETE works’.

On the example next to this theory: On Delete Cascade — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Exam tip

ON DELETE CASCADE vs SET NULL vs RESTRICT.

On Delete Cascade — sample query

CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (1, 'old'), (2, 'keep');
DELETE FROM tags WHERE label = 'old';
SELECT * FROM tags;

On Delete Cascade — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Short notes

DefON DELETE CASCADE removes children when the parent goes.
RuleOnly when children must not outlive the parent.
TrapCASCADE on audit/payment rows.

On Delete Cascade

On Delete Cascade — sample query

Short notes

Questions

On Delete Cascade

On Delete Cascade — sample query

Short notes

Questions