Referential integrity ensures that relationships between tables contain only references to existing key values. A foreign key in Employees can point to an existing OfficeID in Offices, preventing invalid office references. section 4.1.1
Mistake #13: trusting application logic instead of enforcing relationships in the database.
- Changing a detail row’s PurchaseOrderNumber to 6828 may seem reasonable when replacing order 6827, but it creates orphaned details if header 6828 does not exist. section 4.3
- A later inner join then returns neither purchase order, hiding the affected details from ordinary queries. section 4.3
- Application safeguards cannot catch every mistake made outside the application, so related tables should have primary and foreign key constraints. section 4.3
MagicChoc purchase orders
Correct existing values before adding the constraint; otherwise constraint creation fails. Then enforce the relationship with: ALTER TABLE dbo.PurchaseOrderDetails ADD CONSTRAINT FK_PurchaseOrderDetails_PurchaseOrderHeaders FOREIGN KEY (PurchaseOrderNumber) REFERENCES dbo.PurchaseOrderHeaders(PurchaseOrderNumber);
