What approaches ensure referential integrity between Recipient and Address records?

What approaches ensure referential integrity between Recipient and Address records?

• A. No constraints on relations
• B. Manually reconcile recipients and addresses
• C. Allow orphaned addresses
• D. Enforce foreign keys, cascading updates/deletes where appropriate, and implement business rules to prevent orphaned addresses through validation and cleanup jobs

Answer: (D)

Maintaining referential integrity means ensuring every address is tied to an existing recipient and that changes to recipients don’t leave addresses pointing nowhere. The best approach is to use a foreign key on the Address table that references the Recipient, so an address cannot exist without a valid recipient. Adding cascading updates and deletes ensures that if a recipient's key changes or the recipient is removed, the related addresses are updated or removed accordingly, preventing orphaned addresses and keeping data consistent. Implementing business rules and validation, along with periodic cleanup jobs, covers edge cases from imports or manual edits where data might slip through constraints, further safeguarding integrity. Without these constraints, you could end up with addresses that reference non-existent recipients, and manual reconciliation is slow and error-prone, making this approach far more reliable and scalable.

Maintaining referential integrity means ensuring every address is tied to an existing recipient and that changes to recipients don’t leave addresses pointing nowhere. The best approach is to use a foreign key on the Address table that references the Recipient, so an address cannot exist without a valid recipient. Adding cascading updates and deletes ensures that if a recipient's key changes or the recipient is removed, the related addresses are updated or removed accordingly, preventing orphaned addresses and keeping data consistent. Implementing business rules and validation, along with periodic cleanup jobs, covers edge cases from imports or manual edits where data might slip through constraints, further safeguarding integrity. Without these constraints, you could end up with addresses that reference non-existent recipients, and manual reconciliation is slow and error-prone, making this approach far more reliable and scalable.