Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureHard
A data architect is designing a relational database for an e-commerce platform in Azure. They need to enforce a rule that if a category is deleted from the `Categories` table, all products belonging to that category in the `Products` table must also be automatically deleted. This ensures data consistency and prevents orphaned records. Which referential integrity action should be configured on the foreign key constraint between `Products` and `Categories`?
- ARESTRICT
- BNO ACTION
- CCASCADE
- DSET NULL
Show answer & explanationAnswer & explanation
Correct answer: C. CASCADE
The CASCADE referential integrity action ensures that when a row in the parent table (Categories) is deleted, all corresponding rows in the child table (Products) are also deleted. This perfectly matches the requirement to automatically delete products when their category is deleted.
Why the other options are wrong
- A. RESTRICT is similar to NO ACTION; it prevents the deletion of a parent row if there are dependent child rows, which would prevent category deletion if products exist.
- B. NO ACTION means that if a delete operation on the parent table would create orphaned rows in the child table, the delete operation on the parent table is rolled back with an error.
- D. SET NULL means that if a row in the parent table is deleted, the foreign key values in the child table are set to NULL. This would leave products without a category, which is not the desired outcome.
CASCADE Referential Integrity
A referential integrity action that automatically propagates changes (deletes or updates) from the parent table to related rows in the child table, ensuring data consistency.
- When a parent row is deleted, child rows are also deleted.
- When a parent key is updated, child foreign keys are also updated.
- Ensures data consistency and prevents orphaned records.
- Used carefully due to its cascading effect on data.
Memory trick: CASCADE cleans up, SET NULL orphans, NO ACTION/RESTRICT prevent.