How do you ensure data integrity in a database?
Instruction: Discuss methods and mechanisms to maintain and enforce data integrity within a database.
Updated
Official answer
Data integrity means data conforms to defined structural and business rules throughout its lifecycle. Start with an explicit rule and place enforcement where competing writers cannot bypass it.
Use primary or unique keys for identity, foreign keys for relationships, and NOT NULL and CHECK constraints for required values and allowed ranges. In PostgreSQL, a CHECK that evaluates to NULL passes, so a required positive amount needs both NOT NULL and a positive-value check.
Use transactions for related changes. Choose isolation and concurrency control for the invariant: an application that first checks for duplicates and then inserts can race, whereas a suitable unique constraint arbitrates concurrent writes. Multi-row business rules may require stronger coordination than individual row checks.
Validate source-to-target reconciliation, authorized changes and monitoring. Backups support recovery, but test restoration and reconcile recovered data; merely having a backup does not prove current correctness.
Constraints cannot establish every real-world fact. A valid customer ID does not prove a person supplied a truthful address, and a plausible amount may still be assigned to the wrong sale. Define ownership and exception handling for these semantic checks.