Section 3 of 3 · 7 minutes
Use constraints to prevent the problem returning
Your goal
What you will be able to do
Choose and verify a database constraint that enforces an approved data rule.
Why this matters at work
The practical reason
A repaired row can become invalid again unless the database rejects future bad values.
Learn
The idea in plain English
Constraints make data rules part of the database. `NOT NULL`, `CHECK`, `UNIQUE`, primary keys, and foreign keys reject writes that break their rules.
Before adding a constraint, find existing violations and understand the lock the change may take. After adding it, test one valid write and one invalid write inside a transaction you can roll back.
Remember these points
- Repair existing violations before validating a new rule.
- Choose the narrowest constraint that expresses the requirement.
- Test both an allowed value and a rejected value.
See it in SQL
Add and test an account-status rule
Prevent future values outside the approved status list.
ALTER TABLE training_accounts
ADD CONSTRAINT training_accounts_status_check
CHECK (account_status IN ('active', 'paused', 'closed'));
SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'training_accounts'::regclass;The CHECK constraint is evaluated for new or changed rows. Existing invalid data makes the ALTER TABLE fail unless handled first.
Reading `pg_constraint` confirms the rule PostgreSQL stored rather than assuming the statement completed as intended.
The constraint list includes a CHECK rule that permits only active, paused, or closed.
Useful words
Important terms
- Constraint
- A database rule that rejects data that does not meet a requirement.
Quick check · Not graded
Check your understanding
How do you prove a new constraint works?
Your progress is saved to your signed-in account.