CASCADE delete preview in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What rows will be deleted with CASCADE?

Consider two tables: Authors and Books.
The Books table has a foreign key author_id referencing Authors(id) with ON DELETE CASCADE.

If you delete the author with id = 3, which rows in Books will be deleted?

SQL

CREATE TABLE Authors (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE Books (id INT PRIMARY KEY, title VARCHAR(100), author_id INT, FOREIGN KEY (author_id) REFERENCES Authors(id) ON DELETE CASCADE);

INSERT INTO Authors VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO Books VALUES (10, 'Book A', 1), (11, 'Book B', 3), (12, 'Book C', 3), (13, 'Book D', 2);

A Books with id 11 and 12 will be deleted.

B Books with id 10 and 13 will be deleted.

C No books will be deleted.

D All books will be deleted.

💡 Hint

Think about which books belong to the author being deleted.

Deleting author with id 3 triggers cascade delete on books with author_id = 3, which are books 11 and 12.

🧠 Conceptual

intermediate

Effect of CASCADE delete on child table

What is the main effect of ON DELETE CASCADE on a foreign key constraint?

A Deleting a parent row sets child foreign keys to NULL.

B Deleting a child row automatically deletes the parent row.

C Deleting a parent row automatically deletes all related child rows.

D Deleting a child row sets parent keys to NULL.

💡 Hint

Think about what happens to dependent rows when the main row is removed.

ON DELETE CASCADE means deleting a parent row causes automatic deletion of all child rows referencing it.

📝 Syntax

advanced

Identify the correct CASCADE delete syntax

Which SQL statement correctly creates a foreign key with ON DELETE CASCADE?

A ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES Customers(id) DELETE CASCADE;

B ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES Customers(id) ON DELETE CASCADE;

C ALTER TABLE Orders ADD FOREIGN KEY customer_id REFERENCES Customers(id) CASCADE DELETE;

D ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES Customers(id) ON DELETE SET CASCADE;

💡 Hint

Look for the exact phrase used to specify cascade delete.

The correct syntax uses ON DELETE CASCADE after the foreign key reference.

🔧 Debug

advanced

Why does this CASCADE delete not work?

Given these tables, deleting a parent row does not delete child rows. Why?

CREATE TABLE Parent (id INT PRIMARY KEY);
CREATE TABLE Child (id INT PRIMARY KEY, parent_id INT, FOREIGN KEY (parent_id) REFERENCES Parent(id));

Delete statement: DELETE FROM Parent WHERE id = 1;

A The parent table has no primary key.

B The delete statement syntax is incorrect.

C The child table's foreign key references a non-existent column.

D The foreign key lacks <code>ON DELETE CASCADE</code>, so child rows are not deleted.

💡 Hint

Check the foreign key definition for cascade behavior.

Without ON DELETE CASCADE, deleting a parent row does not affect child rows.

❓ optimization

expert

Optimizing CASCADE delete performance

You have a large parent table and a child table with millions of rows. Deleting a parent row cascades deletes to many child rows, but the operation is slow. Which approach can improve performance?

A Add an index on the child table's foreign key column to speed up cascade deletes.

B Remove the foreign key constraint to avoid cascade overhead.

C Use triggers instead of foreign keys for cascading deletes.

D Disable transactions during delete to speed up operations.

💡 Hint

Think about how the database finds child rows to delete.

Indexing the foreign key column helps the database quickly locate child rows to delete, improving cascade delete speed.