CompTIA DataSys+ (DS0-001)Database Management and MaintenanceEasy
A junior database administrator is tasked with ensuring the referential integrity of a customer database. They are specifically concerned about preventing a situation where a customer record is deleted, but associated order records remain, leading to 'orphan' data. Which of the following mechanisms should the administrator implement to enforce referential integrity and prevent this issue?
- ACheck constraints
- BNOT NULL constraints
- CForeign key constraints with ON DELETE CASCADE
- DUnique constraints
Show answer & explanationAnswer & explanation
Correct answer: C. Foreign key constraints with ON DELETE CASCADE
Foreign key constraints with the ON DELETE CASCADE action automatically delete child records (orders) when the parent record (customer) is deleted, maintaining referential integrity.
Why the other options are wrong
- A. Check constraints enforce domain integrity by limiting the range of values that can be placed in a column, not for managing relationships between tables.
- B. NOT NULL constraints ensure that a column cannot have a NULL value, which is important for data integrity but doesn't handle cascading deletions.
- D. Unique constraints ensure that all values in a column or set of columns are unique, not directly related to preventing orphan records upon deletion.
Referential Integrity
A database concept that ensures relationships between tables remain consistent. It dictates that foreign key values must either match a primary key value in a related table or be NULL.
- Prevents orphan records.
- Enforced using foreign key constraints.
- Actions like CASCADE, SET NULL, or RESTRICT can be defined for ON DELETE/ON UPDATE.
Memory trick: Referential integrity is about linked records, like a chain.