Foreign key ON DELETE behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of ON DELETE CASCADE on child table rows

Consider two tables: authors and books. The books table has a foreign key author_id referencing authors(id) with ON DELETE CASCADE. If an author with id = 5 is deleted, what happens to the books written by that author?

SQL

CREATE TABLE authors (id INT PRIMARY KEY, name VARCHAR(100));
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 (5, 'Jane Doe');
INSERT INTO books VALUES (101, 'Book A', 5), (102, 'Book B', 5);

DELETE FROM authors WHERE id = 5;

SELECT * FROM books WHERE author_id = 5;

A Books with author_id = 5 remain unchanged.

B No rows in books with author_id = 5 remain; they are deleted.

C Books with author_id = 5 have author_id set to NULL.

D Deletion of author with id 5 fails due to foreign key constraint.

💡 Hint

Think about what ON DELETE CASCADE means for dependent rows.

The ON DELETE CASCADE option means that when a referenced row in the parent table (authors) is deleted, all rows in the child table (books) that reference it are automatically deleted as well.

❓ query_result

intermediate

Behavior of ON DELETE SET NULL on foreign key

Given a orders table with a foreign key customer_id referencing customers(id) with ON DELETE SET NULL, what is the result of deleting a customer with id = 10?

SQL

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE orders (id INT PRIMARY KEY, order_date DATE, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL);

INSERT INTO customers VALUES (10, 'Alice');
INSERT INTO orders VALUES (201, '2024-01-01', 10), (202, '2024-01-02', 10);

DELETE FROM customers WHERE id = 10;

SELECT * FROM orders WHERE id IN (201, 202);

A The orders with customer_id 10 remain, but their customer_id is set to NULL.

B The orders with customer_id 10 are deleted.

C The deletion of customer with id 10 fails due to foreign key constraint.

D The orders with customer_id 10 remain unchanged.

💡 Hint

Consider what ON DELETE SET NULL does to foreign key columns.

With ON DELETE SET NULL, when the referenced parent row is deleted, the foreign key column in the child rows is set to NULL instead of deleting the child rows.

🧠 Conceptual

advanced

Understanding ON DELETE RESTRICT behavior

What happens if you try to delete a row from a parent table that has child rows referencing it, and the foreign key is defined with ON DELETE RESTRICT?

A The parent row is deleted and child rows are deleted automatically.

B The parent row is deleted but child rows remain with invalid foreign keys.

C The parent row is deleted and child rows have their foreign key set to NULL.

D The deletion of the parent row is blocked and raises an error.

💡 Hint

Think about what RESTRICT means in terms of deletion.

ON DELETE RESTRICT prevents deletion of a parent row if any child rows reference it. The database raises an error to maintain referential integrity.

📝 Syntax

advanced

Correct syntax for foreign key with ON DELETE SET DEFAULT

Which of the following SQL statements correctly creates a foreign key with ON DELETE SET DEFAULT behavior?

A ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET DEFAULT;

B ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL;

C ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET DEFAULT;

D ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;

💡 Hint

Check the full syntax for adding a named foreign key constraint with ON DELETE behavior.

The correct syntax requires naming the constraint and specifying the foreign key columns and referenced table, followed by the ON DELETE SET DEFAULT clause.

🔧 Debug

expert

Diagnosing foreign key deletion error

You have two tables: departments and employees. The employees table has a foreign key department_id referencing departments(id) with ON DELETE NO ACTION. When you try to delete a department that has employees, you get an error. What is the cause?

SQL

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100), department_id INT, FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE NO ACTION);

INSERT INTO departments VALUES (1, 'HR');
INSERT INTO employees VALUES (1001, 'John', 1);

DELETE FROM departments WHERE id = 1;

A The foreign key constraint with ON DELETE NO ACTION prevents deleting a department if employees reference it.

B The foreign key constraint is missing, so deletion fails.

C The employees table has no rows, so deletion should succeed.

D The ON DELETE NO ACTION allows deletion but sets department_id to NULL.

💡 Hint

Consider what ON DELETE NO ACTION means for deletion of referenced rows.

ON DELETE NO ACTION means the database will prevent deletion of a parent row if child rows exist referencing it, raising an error to maintain integrity.