Solution:Step 1: Identify the goalYou want to delete an order and automatically delete all its related items safely.Step 2: Evaluate ON DELETE CASCADECASCADE deletes all child rows (order_items) where order_id matches the deleted order, achieving the goal automatically.Step 3: Handle NULL valuesItem

[Solved] You have two tables:CREATE TABLE orders (order_id INT PRIMARY KEY);CREATE TABLE... What is the best ON DELETE behavior to use for the foreign key to avoid errors and keep data consistent? | SQL

SQL - Table Constraints

You have two tables:

CREATE TABLE orders (order_id INT PRIMARY KEY);
CREATE TABLE order_items (item_id INT PRIMARY KEY, order_id INT, FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE);

You want to delete an order and all its items safely. However, sometimes order_items.order_id can be NULL for items not linked to any order. What is the best ON DELETE behavior to use for the foreign key to avoid errors and keep data consistent?

AON DELETE CASCADE, because it deletes all linked items automatically.

BON DELETE NO ACTION, to do nothing on delete.

CON DELETE RESTRICT, to prevent deleting orders with linked items.

DON DELETE SET NULL, so deleting an order sets linked items' order_id to NULL.

Step-by-Step Solution

Solution:

Step 1: Identify the goal
You want to delete an order and automatically delete all its related items safely.
Step 2: Evaluate ON DELETE CASCADE
CASCADE deletes all child rows (order_items) where order_id matches the deleted order, achieving the goal automatically.
Step 3: Handle NULL values
Items with NULL order_id are not referencing any order, so CASCADE leaves them untouched, maintaining consistency without errors.
Final Answer:
ON DELETE CASCADE, because it deletes all linked items automatically. -> Option A
Quick Check:
Delete order + linked items safely = ON DELETE CASCADE [OK]

Quick Trick: CASCADE deletes linked items automatically, ignores NULLs [OK]

Common Mistakes:

MISTAKES

Choosing SET NULL, which orphans items instead of deleting
Using RESTRICT, blocking necessary deletes
Picking NO ACTION, requiring manual item deletes

Master "Table Constraints" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

You have two tables:

Step 1: Identify the goal

Step 2: Evaluate ON DELETE CASCADE

Step 3: Handle NULL values

Final Answer:

Quick Check:

Want More Practice?