SQL Constraints and Keys for Guaranteed Data Integrity
I enforced data integrity at the database layer with primary, foreign, unique, and check constraints rather than trusting application code. Pushing validation into the schema guaranteed correctness even when multiple clients write concurrently.
Objective & Context
Constraints are the last line of defense for data correctness. This lab applies entity integrity (PK), referential integrity (FK with cascade rules), uniqueness, and domain validation (CHECK), so invalid data is rejected regardless of which client writes it.
Environment & Prerequisites
- PostgreSQL 16 with a normalized schema.
- Test data including intentionally invalid rows.
- Understanding of cascade vs restrict behaviour.
flowchart LR
I[INSERT] --> PK{PK unique?}
PK -->|no| R[Reject]
PK -->|yes| FK{FK exists?}
FK -->|no| R
FK -->|yes| CK{CHECK passes?}
CK -->|no| R
CK -->|yes| OK[Commit]
Step-by-Step Execution
1. Define keys and a check constraint
CREATE TABLE invoice (id SERIAL PRIMARY KEY, amount NUMERIC CHECK (amount > 0), customer_id INT REFERENCES customer(id) ON DELETE RESTRICT);2. Add a uniqueness rule
ALTER TABLE customer ADD CONSTRAINT uq_email UNIQUE (email);3. Test rejection of invalid data
INSERT INTO invoice (amount, customer_id) VALUES (-5, 1);ERROR: new row violates check constraint "invoice_amount_check"Validation & Testing
Attempt inserts that violate each constraint type and confirm the database rejects them. Pass criteria: duplicate PK/UNIQUE rejected, orphan FK rejected, negative amount rejected by CHECK, and valid rows commit.
Advanced: Troubleshooting
- FK insert fails: the referenced parent row must exist first.
- Cascade surprises: choose ON DELETE RESTRICT vs CASCADE deliberately.
- Slow constraint checks: index the FK columns to speed validation.
Key Results
- Pushed 100% of integrity rules into the database layer.
- Rejected invalid rows across PK, FK, UNIQUE, and CHECK in testing.
- Guaranteed referential integrity with explicit cascade rules.
- Removed reliance on application code for core validation.