{"course":{"slug":"how-software-changed","title":"How Software Development Changed","url":"https://addaly.com/learn/how-software-changed","level":"intermediate","track":"work"},"lesson":{"slug":"constraints-in-the-database","title":"The database is the last honest gate","url":"https://addaly.com/learn/how-software-changed/constraints-in-the-database","position":24,"of":87,"minutes":9,"answer":"A database constraint enforces a rule against every writer that will ever exist.","keyIdea":"A database constraint enforces a rule against every writer that will ever exist, so it converts a class of bug from unlikely to impossible for the price of one line of DDL and a validation scan."},"body":{"format":"markdown","text":"## Application code is a suggestion; the schema is a rule\n\nA validation in your service protects the rows written by that service, in that version, on that code path. The database protects every row written by anything: the old version still running during a deploy, the migration script, the admin console, the analyst with psql, the batch job someone wrote in 2021, and whatever was generated last week and reviewed quickly.\n\nThat has always been the argument for constraints. It got stronger in exactly the way everything else in this module did: more write paths, less scrutiny per path, and a single guarantee that covers all of them for one line of DDL.\n\n## The constraints that earn their place\n\n**NOT NULL**, on everything that cannot be absent. Free, checked always, and the single most common source of `NoneType has no attribute` at 3am.\n\n**CHECK**, for the domain rules that are expressible.\n\n```sql\nALTER TABLE charges\n  ADD CONSTRAINT amount_non_negative CHECK (amount_minor >= 0),\n  ADD CONSTRAINT currency_is_iru CHECK (currency ~ '^[A-Z]{3}$');\n```\n\nA `CHECK (amount_minor >= 0)` catches a whole class of sign bugs for ever, regardless of what generated the code above it, and it will find one within a month in most systems that add it.\n\n**UNIQUE**, including partial and functional forms, which people underuse:\n\n```sql\n-- one active subscription per customer; cancelled ones may repeat\nCREATE UNIQUE INDEX one_active_sub\n  ON subscriptions (customer_id)\n  WHERE status = 'active';\n\n-- emails unique case-insensitively\nCREATE UNIQUE INDEX users_email_ci ON users (lower(email));\n```\n\nThat first one replaces an application-level check that has a race condition in it. There is no way to do it correctly in application code without a lock, and there is no way to do it incorrectly with that index.\n\n**Foreign keys**, with the deletion behaviour stated: `ON DELETE RESTRICT` for anything you must not lose, `ON DELETE CASCADE` only where you mean it. Choosing cascade by accident is how a support agent deletes a customer and takes four years of invoices with them.\n\n**Exclusion constraints**, in PostgreSQL, for the class of rule nothing else expresses:\n\n```sql\nCREATE EXTENSION IF NOT EXISTS btree_gist;\nALTER TABLE bookings ADD CONSTRAINT no_double_booking\n  EXCLUDE USING gist (room_id WITH =, during WITH &&);\n```\n\nNo two bookings for the same room may overlap in time. Try writing that correctly in application code under concurrency. This is one line and it cannot be violated.\n\n**Generated columns**, so a derived value cannot drift from its source:\n\n```sql\nALTER TABLE invoices\n  ADD COLUMN total_minor bigint\n  GENERATED ALWAYS AS (subtotal_minor + tax_minor) STORED;\n```\n\n## Adding a constraint to data that already violates it\n\nThis is the part that stops people, and it has a standard answer.\n\n1. **Find the violations first.** `SELECT count(*) FROM charges WHERE amount_minor < 0;` If it is zero, you are adding a guarantee. If it is 4,812, you have just discovered a bug that has been running for two years, and that discovery is the main value of the exercise.\n2. **Decide what those rows mean.** Fix them, delete them, or move them somewhere quarantined. This is a data decision and it needs a human.\n3. **Add the constraint without a long lock.** In PostgreSQL:\n\n```sql\nALTER TABLE charges ADD CONSTRAINT amount_non_negative\n  CHECK (amount_minor >= 0) NOT VALID;   -- instant, applies to new rows\nALTER TABLE charges VALIDATE CONSTRAINT amount_non_negative;  -- scans, weak lock\n```\n\nThe `NOT VALID` step takes microseconds and starts protecting immediately. The validation scan takes as long as it takes and does not block writes. Doing it in one step on a large table takes an exclusive lock and is how people cause an outage while improving safety.\n\n## Isolation levels, briefly, because it is the classic silent bug\n\nRead-modify-write in application code under the default isolation level is a race, always. `SELECT balance; if balance >= amount; UPDATE balance = balance - amount` runs correctly a thousand times and double-spends under load.\n\nThe fixes, cheapest first: do the arithmetic in SQL (`UPDATE ... SET balance = balance - $1 WHERE balance >= $1`, then check the affected row count); take a row lock (`SELECT ... FOR UPDATE`); or raise the isolation level and retry on serialisation failure.\n\nThis is worth knowing precisely because it is invisible to review. The code reads correctly. It is wrong about concurrency, and concurrency is exactly the category where plausible-looking code is most dangerous.\n\n## The one-line summary\n\nEvery constraint you add is a class of bug that becomes impossible rather than merely unlikely, enforced against every writer for ever, for the price of one line and one migration. In a period when the number of writers went up and the attention per writer went down, that is the best trade available in the whole of this course."},"check":{"question":"A service enforces \"one active subscription per customer\" with a SELECT followed by an INSERT inside application code. Under load, duplicates appear. Why does adding retry logic not fix this?","options":[{"text":"Retries make it worse by amplifying load, so the correct fix is a queue that serialises subscription creation per customer.","correct":false,"tempting":"A workable architecture in some systems, but it introduces a component to enforce what one partial unique index enforces for free."},{"text":"The duplicates come from a stale read replica, so the fix is to route the SELECT to the primary.","correct":false,"tempting":"Replica lag causes similar symptoms, but the race exists even when both statements hit the primary."},{"text":"The two statements are not atomic, so two requests can both read no-active-subscription before either inserts.","correct":true},{"text":"Retrying only helps for transient errors, and a duplicate insert is a permanent logical error that will fail identically each time.","correct":false,"tempting":"Correct about retries being the wrong tool, but misidentifies why: the insert succeeds rather than failing, which is the whole problem."}]},"prev":{"slug":"types-as-always-on-oracles","title":"The type checker got more valuable","url":"https://addaly.com/learn/how-software-changed/types-as-always-on-oracles"},"next":{"slug":"mutation-testing","title":"Proving the suite bites","url":"https://addaly.com/learn/how-software-changed/mutation-testing"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'The database is the last honest gate', in How Software Development Changed. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/how-software-changed/constraints-in-the-database (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"The database is the last honest gate","courseTitle":"How Software Development Changed","module":{"index":3,"slug":"oracles-and-verification","title":"Building oracles that stay ahead"},"answer":null,"keyIdea":"A database constraint enforces a rule against every writer that will ever exist, so it converts a class of bug from unlikely to impossible for the price of one line of DDL and a validation scan.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"application-code-is-a-suggestion-the-schema-is-a-rule","runs":[{"text":"Application code is a suggestion; the schema is a rule"}]},{"t":"p","runs":[{"text":"A validation in your service protects the rows written by that service, in that version, on that code path. The database protects every row written by anything: the old version still running during a deploy, the migration script, the admin console, the analyst with psql, the batch job someone wrote in 2021, and whatever was generated last week and reviewed quickly."}]},{"t":"p","runs":[{"text":"That has always been the argument for constraints. It got stronger in exactly the way everything else in this module did: more write paths, less scrutiny per path, and a single guarantee that covers all of them for one line of DDL."}]},{"t":"h2","id":"the-constraints-that-earn-their-place","runs":[{"text":"The constraints that earn their place"}]},{"t":"p","runs":[{"text":"NOT NULL","b":true},{"text":", on everything that cannot be absent. Free, checked always, and the single most common source of "},{"text":"NoneType has no attribute","code":true},{"text":" at 3am."}]},{"t":"p","runs":[{"text":"CHECK","b":true},{"text":", for the domain rules that are expressible."}]},{"t":"code","lang":"sql","label":"SQL","text":"ALTER TABLE charges\n  ADD CONSTRAINT amount_non_negative CHECK (amount_minor >= 0),\n  ADD CONSTRAINT currency_is_iru CHECK (currency ~ '^[A-Z]{3}$');"},{"t":"p","runs":[{"text":"A "},{"text":"CHECK (amount_minor >= 0)","code":true},{"text":" catches a whole class of sign bugs for ever, regardless of what generated the code above it, and it will find one within a month in most systems that add it."}]},{"t":"p","runs":[{"text":"UNIQUE","b":true},{"text":", including partial and functional forms, which people underuse:"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- one active subscription per customer; cancelled ones may repeat\nCREATE UNIQUE INDEX one_active_sub\n  ON subscriptions (customer_id)\n  WHERE status = 'active';\n\n-- emails unique case-insensitively\nCREATE UNIQUE INDEX users_email_ci ON users (lower(email));"},{"t":"p","runs":[{"text":"That first one replaces an application-level check that has a race condition in it. There is no way to do it correctly in application code without a lock, and there is no way to do it incorrectly with that index."}]},{"t":"p","runs":[{"text":"Foreign keys","b":true},{"text":", with the deletion behaviour stated: "},{"text":"ON DELETE RESTRICT","code":true},{"text":" for anything you must not lose, "},{"text":"ON DELETE CASCADE","code":true},{"text":" only where you mean it. Choosing cascade by accident is how a support agent deletes a customer and takes four years of invoices with them."}]},{"t":"p","runs":[{"text":"Exclusion constraints","b":true},{"text":", in PostgreSQL, for the class of rule nothing else expresses:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE EXTENSION IF NOT EXISTS btree_gist;\nALTER TABLE bookings ADD CONSTRAINT no_double_booking\n  EXCLUDE USING gist (room_id WITH =, during WITH &&);"},{"t":"p","runs":[{"text":"No two bookings for the same room may overlap in time. Try writing that correctly in application code under concurrency. This is one line and it cannot be violated."}]},{"t":"p","runs":[{"text":"Generated columns","b":true},{"text":", so a derived value cannot drift from its source:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ALTER TABLE invoices\n  ADD COLUMN total_minor bigint\n  GENERATED ALWAYS AS (subtotal_minor + tax_minor) STORED;"},{"t":"h2","id":"adding-a-constraint-to-data-that-already-violates-it","runs":[{"text":"Adding a constraint to data that already violates it"}]},{"t":"p","runs":[{"text":"This is the part that stops people, and it has a standard answer."}]},{"t":"ol","start":1,"items":[[{"text":"Find the violations first.","b":true},{"text":" "},{"text":"SELECT count(*) FROM charges WHERE amount_minor < 0;","code":true},{"text":" If it is zero, you are adding a guarantee. If it is 4,812, you have just discovered a bug that has been running for two years, and that discovery is the main value of the exercise."}],[{"text":"Decide what those rows mean.","b":true},{"text":" Fix them, delete them, or move them somewhere quarantined. This is a data decision and it needs a human."}],[{"text":"Add the constraint without a long lock.","b":true},{"text":" In PostgreSQL:"}]]},{"t":"code","lang":"sql","label":"SQL","text":"ALTER TABLE charges ADD CONSTRAINT amount_non_negative\n  CHECK (amount_minor >= 0) NOT VALID;   -- instant, applies to new rows\nALTER TABLE charges VALIDATE CONSTRAINT amount_non_negative;  -- scans, weak lock"},{"t":"p","runs":[{"text":"The "},{"text":"NOT VALID","code":true},{"text":" step takes microseconds and starts protecting immediately. The validation scan takes as long as it takes and does not block writes. Doing it in one step on a large table takes an exclusive lock and is how people cause an outage while improving safety."}]},{"t":"h2","id":"isolation-levels-briefly-because-it-is-the-classic-silent-bu","runs":[{"text":"Isolation levels, briefly, because it is the classic silent bug"}]},{"t":"p","runs":[{"text":"Read-modify-write in application code under the default isolation level is a race, always. "},{"text":"SELECT balance; if balance >= amount; UPDATE balance = balance - amount","code":true},{"text":" runs correctly a thousand times and double-spends under load."}]},{"t":"p","runs":[{"text":"The fixes, cheapest first: do the arithmetic in SQL ("},{"text":"UPDATE ... SET balance = balance - $1 WHERE balance >= $1","code":true},{"text":", then check the affected row count); take a row lock ("},{"text":"SELECT ... FOR UPDATE","code":true},{"text":"); or raise the isolation level and retry on serialisation failure."}]},{"t":"p","runs":[{"text":"This is worth knowing precisely because it is invisible to review. The code reads correctly. It is wrong about concurrency, and concurrency is exactly the category where plausible-looking code is most dangerous."}]},{"t":"h2","id":"the-one-line-summary","runs":[{"text":"The one-line summary"}]},{"t":"p","runs":[{"text":"Every constraint you add is a class of bug that becomes impossible rather than merely unlikely, enforced against every writer for ever, for the price of one line and one migration. In a period when the number of writers went up and the attention per writer went down, that is the best trade available in the whole of this course."}]}],"check":{"question":[{"text":"A service enforces “one active subscription per customer” with a SELECT followed by an INSERT inside application code. Under load, duplicates appear. Why does adding retry logic not fix this?"}],"options":[{"text":[{"text":"Retries make it worse by amplifying load, so the correct fix is a queue that serialises subscription creation per customer."}],"correct":false,"tempting":[{"text":"A workable architecture in some systems, but it introduces a component to enforce what one partial unique index enforces for free."}]},{"text":[{"text":"The duplicates come from a stale read replica, so the fix is to route the SELECT to the primary."}],"correct":false,"tempting":[{"text":"Replica lag causes similar symptoms, but the race exists even when both statements hit the primary."}]},{"text":[{"text":"The two statements are not atomic, so two requests can both read no-active-subscription before either inserts."}],"correct":true,"tempting":null},{"text":[{"text":"Retrying only helps for transient errors, and a duplicate insert is a permanent logical error that will fail identically each time."}],"correct":false,"tempting":[{"text":"Correct about retries being the wrong tool, but misidentifies why: the insert succeeds rather than failing, which is the whole problem."}]}],"order":[0,1,2,3],"why":null},"prev":{"slug":"types-as-always-on-oracles","title":"The type checker got more valuable","minutes":9},"next":{"slug":"mutation-testing","title":"Proving the suite bites","minutes":9}}}