Referential integrity enforcement in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What happens when deleting a parent row with ON DELETE CASCADE?

Given two tables departments and employees where employees.department_id references departments.id with ON DELETE CASCADE, what will be the result of deleting a department?

SQL

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE);

INSERT INTO departments VALUES (1, 'HR'), (2, 'IT');
INSERT INTO employees VALUES (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', 1);

DELETE FROM departments WHERE id = 1;

SELECT * FROM employees ORDER BY id;

A Only the department row is deleted; employees remain

B Delete fails with foreign key constraint error

C Rows with department_id = 1 are deleted; remaining employees: (2, 'Bob', 2)

D All employees remain unchanged

💡 Hint

Think about what ON DELETE CASCADE means for child rows.

The ON DELETE CASCADE option means that when a referenced row in the parent table is deleted, all child rows referencing it are automatically deleted. So deleting department 1 removes employees with department_id = 1.

📝 Syntax

intermediate

Which option correctly defines a foreign key with ON UPDATE SET NULL?

Choose the correct SQL statement to create a foreign key constraint that sets the child column to NULL when the parent key is updated.

A FOREIGN KEY (child_id) REFERENCES parent(id) ON UPDATE NO ACTION

B FOREIGN KEY (child_id) REFERENCES parent(id) ON UPDATE CASCADE

C FOREIGN KEY (child_id) REFERENCES parent(id) ON DELETE SET NULL

D FOREIGN KEY (child_id) REFERENCES parent(id) ON UPDATE SET NULL

💡 Hint

Focus on the ON UPDATE clause and the action SET NULL.

The ON UPDATE SET NULL clause means that if the parent key changes, the foreign key in the child row is set to NULL. Option D correctly uses this syntax.

🔧 Debug

advanced

Why does this foreign key constraint fail to create?

Given the following SQL, why does the foreign key constraint creation fail?

SQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
);

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

A Primary key must be declared on <code>customer_id</code> in <code>orders</code>

B The referenced table <code>customers</code> must be created before <code>orders</code>

C The <code>ON DELETE RESTRICT</code> clause is invalid syntax

D The foreign key column <code>customer_id</code> must be declared NOT NULL

💡 Hint

Think about the order of table creation and foreign key references.

Foreign key constraints require the referenced table to exist before the referencing table is created. Here, orders references customers which is created later, causing failure.

❓ optimization

advanced

How to optimize foreign key checks for bulk inserts?

You need to insert thousands of rows into a child table with foreign keys. Which approach optimizes performance while maintaining referential integrity?

A Disable foreign key checks before insert, then re-enable after all inserts

B Insert rows one by one with foreign key checks enabled

C Drop foreign key constraints permanently before insert

D Insert rows in any order without foreign key constraints

💡 Hint

Consider temporarily disabling checks to speed up bulk operations.

Disabling foreign key checks temporarily allows faster bulk inserts. After inserts, re-enabling checks ensures integrity. Dropping constraints permanently risks data integrity.

🧠 Conceptual

expert

What is the effect of ON DELETE SET DEFAULT in referential integrity?

Consider a foreign key with ON DELETE SET DEFAULT. What happens when the referenced parent row is deleted?

A The child foreign key column is set to its default value

B The child row is deleted automatically

C The delete operation is rejected with an error

D The child foreign key column is set to NULL

💡 Hint

Think about what SET DEFAULT means for a foreign key column.

ON DELETE SET DEFAULT sets the foreign key column in the child row to its predefined default value when the referenced parent row is deleted.