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.

Practice

1. What does CASCADE DELETE do in a database?

easy

A. Creates a backup of rows before deletion

B. Prevents deletion of parent rows if child rows exist

C. Deletes only the parent row without affecting child rows

D. Automatically deletes related child rows when a parent row is deleted

Solution

1. Step 1: Understand CASCADE DELETE behavior

   CASCADE DELETE means when you delete a parent row, all related child rows linked by foreign keys are also deleted automatically.

2. Step 2: Compare options with this behavior

   Automatically deletes related child rows when a parent row is deleted matches this behavior exactly. Options A, B, and D describe different or incorrect behaviors.

3. Final Answer:

   Automatically deletes related child rows when a parent row is deleted -> Option D

4. Quick Check:

   CASCADE DELETE = auto-remove child rows [OK]

Hint: CASCADE means child rows go away with parent [OK]

Common Mistakes:

• Thinking CASCADE prevents deletion
• Confusing CASCADE with backup
• Assuming child rows stay after parent deletion

2. Which SQL syntax correctly previews rows that will be deleted by CASCADE when deleting from orders table with customer_id = 5?

easy

A. SELECT * FROM customers WHERE id = 5;

B. DELETE FROM order_items WHERE customer_id = 5;

C. SELECT * FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE customer_id = 5);

D. SELECT * FROM orders WHERE customer_id = 5;

Solution

1. Step 1: Identify child rows linked to parent rows

   To preview cascade deletes, select child rows where foreign key matches parent rows to be deleted. Here, order_items link to orders by order_id.

2. Step 2: Use subquery to find parent rows with customer_id = 5

   The subquery selects orders with customer_id = 5. The outer query selects order_items with those order_ids.

3. Final Answer:

   SELECT * FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE customer_id = 5); -> Option C

4. Quick Check:

   Preview child rows with IN + subquery [OK]

Hint: Use IN with subquery to find child rows [OK]

Common Mistakes:

• Trying to delete instead of preview
• Selecting wrong table without join
• Using wrong column in WHERE clause

3. Given tables departments and employees with employees.department_id referencing departments.id with CASCADE DELETE, what rows will this query return?

SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

medium

A. All employees who work in the Sales department

B. All departments named Sales

C. All employees regardless of department

D. No rows, syntax error

Solution

1. Step 1: Understand the subquery

   The subquery selects department ids where the name is 'Sales'.

2. Step 2: Outer query filters employees by those department ids

   Employees with department_id matching those ids are returned.

3. Final Answer:

   All employees who work in the Sales department -> Option A

4. Quick Check:

   IN subquery filters child rows correctly [OK]

Hint: IN with subquery filters child rows by parent condition [OK]

Common Mistakes:

• Confusing employees with departments
• Assuming syntax error
• Ignoring the subquery effect

4. You wrote this query to preview cascade deletes:

SELECT * FROM order_items WHERE order_id = (SELECT id FROM orders WHERE customer_id = 10);

Why might this query cause an error?

medium

A. Subquery returns multiple rows but '=' expects one value

B. Table order_items does not exist

C. Missing WHERE clause in subquery

D. Incorrect column name order_id

Solution

1. Step 1: Analyze subquery result

   The subquery selects all order ids for customer_id 10, which can be multiple rows.

2. Step 2: Check operator usage

   The outer query uses '=' which expects a single value, causing an error if multiple rows returned.

3. Final Answer:

   Subquery returns multiple rows but '=' expects one value -> Option A

4. Quick Check:

   Use IN for multiple subquery results [OK]

Hint: Use IN, not =, when subquery returns many rows [OK]

Common Mistakes:

• Using '=' with multi-row subquery
• Assuming table or column names are wrong
• Ignoring subquery result count

5. You want to preview all child rows that will be deleted when deleting customers with country = 'USA'. The schema has customers and orders (orders.customer_id references customers.id with CASCADE). Which query correctly previews all orders to be deleted?

hard

A. SELECT * FROM customers WHERE country = 'USA';

B. SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'USA');

C. DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'USA');

D. SELECT * FROM orders WHERE country = 'USA';

Solution

1. Step 1: Identify parent rows to delete

   Customers with country = 'USA' are the parent rows to be deleted.

2. Step 2: Find child rows linked to those parents

   Orders with customer_id matching those customers will be deleted by CASCADE.

3. Step 3: Write query to preview child rows

   Use IN with subquery selecting customer ids from customers where country = 'USA'.

4. Final Answer:

   SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'USA'); -> Option B

5. Quick Check:

   Preview child rows with IN + subquery [OK]

Hint: Preview child rows with IN and parent filter subquery [OK]

Common Mistakes:

• Selecting from wrong table
• Using DELETE instead of SELECT
• Filtering child table by parent's column directly