Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A data architect is designing a relational database for an e-commerce platform in Azure. The database includes a 'Categories' table and a 'Products' table, where each product belongs to a category. When a category is deleted, all associated products should also be automatically deleted to maintain data consistency. Which referential integrity action should be configured on the foreign key constraint?
- ASET NULL
- BRESTRICT
- CNO ACTION
- DCASCADE
Show answer & explanationAnswer & explanation
Correct answer: D. 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 automatically deleted, maintaining consistency.
Why the other options are wrong
- A. SET NULL sets the foreign key column(s) in the child table to NULL when the corresponding parent row is deleted, which is not suitable if products must be deleted.
- B. RESTRICT is similar to NO ACTION; it prevents the deletion of a parent row if there are any referencing child rows.
- C. NO ACTION means that if a deletion in the parent table would result in orphaned rows in the child table, the deletion in the parent table is disallowed.
CASCADE Referential Integrity
The CASCADE referential action automatically deletes or updates dependent rows in the child table when the corresponding parent row is deleted or updated.
- Maintains data consistency.
- Automates deletion/update of related records.
- Can be powerful but requires careful design to avoid unintended data loss.
Memory trick: CASCADE for Chain Deletes, SET NULL for Orphans, RESTRICT for Blocks.