DP-900 Practice Question: Identify considerations for relational data on Azure
A company uses Azure SQL Database for an order processing system. The Orders table has columns: OrderID (PK), CustomerID, OrderDate, TotalAmount. The Customers table has CustomerID (PK), Name, Email. The database administrator wants to ensure that when a customer record is deleted, all orders for that customer are also automatically deleted. Which database constraint should be implemented?
⚠ Common exam trap
Many candidates confuse ON DELETE CASCADE with ON UPDATE CASCADE, mistakenly thinking that updating a primary key is the same as deleting a record, or they incorrectly assume that a trigger is always required for cascading operations when a declarative constraint is available.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
ON DELETE CASCADE on Orders.CustomerID
ON DELETE CASCADE on the foreign key (Orders.CustomerID) automatically deletes all child rows in the Orders table when the parent row in the Customers table is deleted. This ensures referential integrity without requiring additional code or triggers, and is the standard SQL mechanism for cascading deletes in Azure SQL Database.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
ON DELETE SET NULL on Orders.CustomerID
Why it's wrong here
ON DELETE SET NULL would simply set the CustomerID column in the Orders table to NULL when a referenced customer is deleted, leaving the order rows intact. This creates orphaned order records that no longer link to any customer, which violates the business requirement that orders be removed along with the customer. Moreover, this action only works if the Orders.CustomerID column is nullable, and it silently discards the customer association rather than cleanly deleting the dependent data. Therefore, it is not a valid solution for cascading deletes in this order processing system.
- ✓
ON DELETE CASCADE on Orders.CustomerID
Why this is correct
ON DELETE CASCADE is the correct choice because it declaratively tells the database to automatically delete all rows in the Orders table that reference a customer when that customer's row is deleted from the Customers table. This is a built-in referential action on the foreign key constraint, ensuring that no orphaned order rows remain and that the deletion is atomic and enforced by the database engine. Unlike procedural triggers, it cannot be bypassed accidentally and requires no custom T-SQL logic. This exactly meets the requirement for removing a customer and their associated orders.
- ✗
ON UPDATE CASCADE on Customers.CustomerID
Why it's wrong here
ON UPDATE CASCADE applies only to changes in the value of the parent key (Customers.CustomerID), not to deletion of the parent row. If the CustomerID is updated, this action would propagate that new value to all matching Orders.CustomerID rows, but it has no effect whatsoever when a customer is deleted. Therefore, choosing this option would leave the orders untouched and fail to satisfy the business rule. The scenario does not involve updating customer IDs, so this referential action is completely irrelevant to the requirement.
- ✗
A trigger on Customers table
Why it's wrong here
A trigger on the Customers table could execute a DELETE on related Orders rows, but it operates procedurally rather than declaratively, meaning it relies on custom T-SQL logic that can be bypassed or fail silently if the trigger is disabled or an error occurs. It is tempting because triggers are often used to enforce cross-table referential actions when foreign key constraints with CASCADE DELETE are unavailable, making them a fallback for manual cascading deletes in legacy systems.
Go deeper
Related to this question
Learn chapter
Azure SQL Services
Key term
Foreign key
A foreign key is a column or set of columns in a database table that links to the primary key of another table, ensuring relational integrity between the two.
Key term
Row
A row is a horizontal record in a database table that contains all the information about a single entity, like one customer or one product.
About these practice questions
One of 820 original DP-900 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.