{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"create-table-and-constraints","title":"CREATE TABLE: a constraint is a test that runs on every write","url":"https://addaly.com/learn/data-and-sql/create-table-and-constraints","position":56,"of":66,"minutes":9,"answer":"A constraint is a test the database runs on every write, refusing the row rather than flagging it later; name each one, and know that CHECK sees one row at a time.","keyIdea":"A constraint is a test the database runs on every write, refusing the row rather than flagging it later; name each one, and know that CHECK sees one row at a time, so multi-row rules need UNIQUE, EXCLUDE or a trigger."},"body":{"format":"markdown","text":"## The table is the first line of defence\n\nEvery check in the previous module ran after the data was in. A constraint runs before, on every insert and every update, and a row that fails it never exists. That is the difference between finding a negative payment in next month's audit and never having one.\n\n```sql\nCREATE TABLE payments (\n  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  reference    text        NOT NULL,\n  student_id   bigint      NOT NULL REFERENCES students (id),\n  amount       numeric(12, 2) NOT NULL,\n  paid_on      date        NOT NULL,\n  method       text        NOT NULL,\n  note         text,\n  created_at   timestamptz NOT NULL DEFAULT now(),\n  CONSTRAINT payments_reference_unique  UNIQUE (reference),\n  CONSTRAINT payments_amount_positive   CHECK (amount > 0),\n  CONSTRAINT payments_method_known      CHECK (method IN ('cash', 'upi', 'card', 'transfer')),\n  CONSTRAINT payments_not_in_future     CHECK (paid_on <= current_date)\n);\n```\n\nRead it as a list of promises. Every payment has a reference and it is unique. Every payment belongs to a student who exists. The amount is money, to the paisa, and positive. The method is one of four. The date is not in the future. `note` is the only thing allowed to be missing, and that is a decision, not an omission.\n\n## What each kind refuses\n\n`NOT NULL` refuses an unknown. Pair it with `DEFAULT` for columns that should be filled in automatically, as `created_at` is; a `NOT NULL` column with no default and no value in the INSERT is an error, which is what you want for `amount` and not for `created_at`.\n\n`UNIQUE` refuses a repeat. It builds an index to do it, which the performance module returns to, and it treats NULLs as distinct from each other, so a nullable unique column can hold many NULLs. `PRIMARY KEY` is `UNIQUE` plus `NOT NULL` plus the declaration that this is the identity, as the keys lesson described.\n\n`CHECK` refuses any row for which its expression is not true. It can reference several columns of the same row: `CHECK (valid_to IS NULL OR valid_to > valid_from)` is the guard for every effective-dated table in the time module. It cannot look at other rows or other tables, and that limit matters below.\n\n`REFERENCES` refuses a row that points at nothing, and refuses (or cascades) the deletion of a row that is pointed at.\n\n## Name them\n\nAn unnamed constraint produces an error like `new row violates check constraint \"payments_check1\"`. A named one says `payments_amount_positive`, and the person reading the error at two in the morning knows what happened without opening the schema. Names cost nothing.\n\n## The rule a CHECK cannot express\n\n\"A student may not have more than one payment per day\" is a rule about two rows. \"A booking may not overlap another booking of the same room\" is a rule about two rows. `CHECK` sees one row at a time and cannot enforce either. The options, in order of preference:\n\nA `UNIQUE` constraint expresses \"at most one row per combination\": `UNIQUE (student_id, paid_on)` enforces the first rule exactly. Postgres's `EXCLUDE` constraint expresses \"no two rows may overlap\", which is the second: `EXCLUDE USING gist (room_id WITH =, during WITH &&)` on a range column, and two bookings for the same room whose ranges intersect cannot both exist. Beyond those, the rule needs a trigger, which is code that runs on every write and can query anything, or it lives in the application, which means every application. A rule that lives in the database is enforced for the admin fixing data by hand at midnight; a rule in the application is not.\n\n> Figure: Six refusals, from the shape of one value to a rule about two rows. Layers, top to bottom: The type (refuses a value of the wrong shape); NOT NULL (refuses an unknown); CHECK (refuses a value this row may not hold); UNIQUE (refuses a repeat, and states the grain); REFERENCES (refuses a row that points at nothing); EXCLUDE, or a trigger (the only way to state a rule about two rows). A constraint costs a comparison per write. A missing one costs the query that finds the bad rows a year later, the decision about what to do with them, and every report that ran in between. Note where the ladder stops: CHECK sees one row at a time and cannot express a rule about two.\n\n## The engines differ in what they enforce\n\nSQLite accepts a `REFERENCES` clause and, by default, ignores it. Foreign keys are enforced only when the connection has run `PRAGMA foreign_keys = ON`, and every connection has to do it. A SQLite table with foreign keys declared and never enforced is the norm, not the exception, and orphan rows are the usual discovery. MySQL's older MyISAM engine has the same property; InnoDB enforces. DuckDB enforces primary and unique keys and checks, and is built for analysis rather than for a thousand concurrent writers, which is the right trade for what it is.\n\n## Constraints are cheap to add and expensive to lack\n\nA constraint costs a comparison per write. A missing constraint costs the query that finds the bad rows a year later, the decision about what to do with them, and every report that ran in between. When in doubt, add it. When the data already violates it, Postgres refuses to add it, which is itself the list of rows to fix, and `NOT VALID` lets you add it for future rows while you work through the old ones."},"check":{"question":"A team wants to guarantee that no student has two payments recorded on the same date. They write `CHECK (count(*) = 1)` on the payments table and get an error. What is the right tool and why does the CHECK fail?","options":[{"text":"A trigger is the only option, because constraints can never involve more than one column and this rule involves two.","correct":false,"tempting":"Constraints can span several columns of a row; the limit is on rows, not columns, and a simpler tool exists here."},{"text":"`UNIQUE (student_id, paid_on)`, because a CHECK evaluates one row at a time and cannot count other rows.","correct":true},{"text":"`CHECK (student_id IS DISTINCT FROM paid_on)`, since the rule is that the two columns must not coincide within the table.","correct":false,"tempting":"Compares two unrelated columns of the same row, which is meaningless here; the rule is about two different rows sharing both values."},{"text":"An `EXCLUDE` constraint on the `paid_on` range, because two payments on the same date have overlapping day-long intervals and EXCLUDE is built for overlaps.","correct":false,"tempting":"EXCLUDE would work but is heavier than needed; an equality on two columns is exactly what UNIQUE expresses, and the CHECK fails for a reason this option does not state."}]},"prev":{"slug":"indexes-and-slow-queries","title":"Why a query is slow, and what an index does","url":"https://addaly.com/learn/data-and-sql/indexes-and-slow-queries"},"next":{"slug":"insert-and-upsert","title":"INSERT, and the INSERT you can run twice","url":"https://addaly.com/learn/data-and-sql/insert-and-upsert"},"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) 'CREATE TABLE: a constraint is a test that runs on every write', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/create-table-and-constraints (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":"CREATE TABLE: a constraint is a test that runs on every write","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":6,"slug":"changing-data-safely","title":"Changing data safely"},"answer":null,"keyIdea":"A constraint is a test the database runs on every write, refusing the row rather than flagging it later; name each one, and know that CHECK sees one row at a time, so multi-row rules need UNIQUE, EXCLUDE or a trigger.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure 6.1","figure":{"type":"stack","title":"Six refusals, from the shape of one value to a rule about two rows","layers":[{"label":"The type","detail":"refuses a value of the wrong shape"},{"label":"NOT NULL","detail":"refuses an unknown"},{"label":"CHECK","detail":"refuses a value this row may not hold"},{"label":"UNIQUE","detail":"refuses a repeat, and states the grain"},{"label":"REFERENCES","detail":"refuses a row that points at nothing"},{"label":"EXCLUDE, or a trigger","detail":"the only way to state a rule about two rows"}],"caption":"A constraint costs a comparison per write. A missing one costs the query that finds the bad rows a year later, the decision about what to do with them, and every report that ran in between. Note where the ladder stops: CHECK sees one row at a time and cannot express a rule about two."},"alt":"Figure: Six refusals, from the shape of one value to a rule about two rows. Layers, top to bottom: The type (refuses a value of the wrong shape); NOT NULL (refuses an unknown); CHECK (refuses a value this row may not hold); UNIQUE (refuses a repeat, and states the grain); REFERENCES (refuses a row that points at nothing); EXCLUDE, or a trigger (the only way to state a rule about two rows). A constraint costs a comparison per write. A missing one costs the query that finds the bad rows a year later, the decision about what to do with them, and every report that ran in between. Note where the ladder stops: CHECK sees one row at a time and cannot express a rule about two."}},"blocks":[{"t":"h2","id":"the-table-is-the-first-line-of-defence","runs":[{"text":"The table is the first line of defence"}]},{"t":"p","runs":[{"text":"Every check in the previous module ran after the data was in. A constraint runs before, on every insert and every update, and a row that fails it never exists. That is the difference between finding a negative payment in next month’s audit and never having one."}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE payments (\n  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  reference    text        NOT NULL,\n  student_id   bigint      NOT NULL REFERENCES students (id),\n  amount       numeric(12, 2) NOT NULL,\n  paid_on      date        NOT NULL,\n  method       text        NOT NULL,\n  note         text,\n  created_at   timestamptz NOT NULL DEFAULT now(),\n  CONSTRAINT payments_reference_unique  UNIQUE (reference),\n  CONSTRAINT payments_amount_positive   CHECK (amount > 0),\n  CONSTRAINT payments_method_known      CHECK (method IN ('cash', 'upi', 'card', 'transfer')),\n  CONSTRAINT payments_not_in_future     CHECK (paid_on <= current_date)\n);"},{"t":"p","runs":[{"text":"Read it as a list of promises. Every payment has a reference and it is unique. Every payment belongs to a student who exists. The amount is money, to the paisa, and positive. The method is one of four. The date is not in the future. "},{"text":"note","code":true},{"text":" is the only thing allowed to be missing, and that is a decision, not an omission."}]},{"t":"h2","id":"what-each-kind-refuses","runs":[{"text":"What each kind refuses"}]},{"t":"p","runs":[{"text":"NOT NULL","code":true},{"text":" refuses an unknown. Pair it with "},{"text":"DEFAULT","code":true},{"text":" for columns that should be filled in automatically, as "},{"text":"created_at","code":true},{"text":" is; a "},{"text":"NOT NULL","code":true},{"text":" column with no default and no value in the INSERT is an error, which is what you want for "},{"text":"amount","code":true},{"text":" and not for "},{"text":"created_at","code":true},{"text":"."}]},{"t":"p","runs":[{"text":"UNIQUE","code":true},{"text":" refuses a repeat. It builds an index to do it, which the performance module returns to, and it treats NULLs as distinct from each other, so a nullable unique column can hold many NULLs. "},{"text":"PRIMARY KEY","code":true},{"text":" is "},{"text":"UNIQUE","code":true},{"text":" plus "},{"text":"NOT NULL","code":true},{"text":" plus the declaration that this is the identity, as the keys lesson described."}]},{"t":"p","runs":[{"text":"CHECK","code":true},{"text":" refuses any row for which its expression is not true. It can reference several columns of the same row: "},{"text":"CHECK (valid_to IS NULL OR valid_to > valid_from)","code":true},{"text":" is the guard for every effective-dated table in the time module. It cannot look at other rows or other tables, and that limit matters below."}]},{"t":"p","runs":[{"text":"REFERENCES","code":true},{"text":" refuses a row that points at nothing, and refuses (or cascades) the deletion of a row that is pointed at."}]},{"t":"h2","id":"name-them","runs":[{"text":"Name them"}]},{"t":"p","runs":[{"text":"An unnamed constraint produces an error like "},{"text":"new row violates check constraint \"payments_check1\"","code":true},{"text":". A named one says "},{"text":"payments_amount_positive","code":true},{"text":", and the person reading the error at two in the morning knows what happened without opening the schema. Names cost nothing."}]},{"t":"h2","id":"the-rule-a-check-cannot-express","runs":[{"text":"The rule a CHECK cannot express"}]},{"t":"p","runs":[{"text":"“A student may not have more than one payment per day” is a rule about two rows. “A booking may not overlap another booking of the same room” is a rule about two rows. "},{"text":"CHECK","code":true},{"text":" sees one row at a time and cannot enforce either. The options, in order of preference:"}]},{"t":"p","runs":[{"text":"A "},{"text":"UNIQUE","code":true},{"text":" constraint expresses “at most one row per combination”: "},{"text":"UNIQUE (student_id, paid_on)","code":true},{"text":" enforces the first rule exactly. Postgres’s "},{"text":"EXCLUDE","code":true},{"text":" constraint expresses “no two rows may overlap”, which is the second: "},{"text":"EXCLUDE USING gist (room_id WITH =, during WITH &&)","code":true},{"text":" on a range column, and two bookings for the same room whose ranges intersect cannot both exist. Beyond those, the rule needs a trigger, which is code that runs on every write and can query anything, or it lives in the application, which means every application. A rule that lives in the database is enforced for the admin fixing data by hand at midnight; a rule in the application is not."}]},{"t":"h2","id":"the-engines-differ-in-what-they-enforce","runs":[{"text":"The engines differ in what they enforce"}]},{"t":"p","runs":[{"text":"SQLite accepts a "},{"text":"REFERENCES","code":true},{"text":" clause and, by default, ignores it. Foreign keys are enforced only when the connection has run "},{"text":"PRAGMA foreign_keys = ON","code":true},{"text":", and every connection has to do it. A SQLite table with foreign keys declared and never enforced is the norm, not the exception, and orphan rows are the usual discovery. MySQL’s older MyISAM engine has the same property; InnoDB enforces. DuckDB enforces primary and unique keys and checks, and is built for analysis rather than for a thousand concurrent writers, which is the right trade for what it is."}]},{"t":"h2","id":"constraints-are-cheap-to-add-and-expensive-to-lack","runs":[{"text":"Constraints are cheap to add and expensive to lack"}]},{"t":"p","runs":[{"text":"A constraint costs a comparison per write. A missing constraint costs the query that finds the bad rows a year later, the decision about what to do with them, and every report that ran in between. When in doubt, add it. When the data already violates it, Postgres refuses to add it, which is itself the list of rows to fix, and "},{"text":"NOT VALID","code":true},{"text":" lets you add it for future rows while you work through the old ones."}]}],"check":{"question":[{"text":"A team wants to guarantee that no student has two payments recorded on the same date. They write "},{"text":"CHECK (count(*) = 1)","code":true},{"text":" on the payments table and get an error. What is the right tool and why does the CHECK fail?"}],"options":[{"text":[{"text":"A trigger is the only option, because constraints can never involve more than one column and this rule involves two."}],"correct":false,"tempting":[{"text":"Constraints can span several columns of a row; the limit is on rows, not columns, and a simpler tool exists here."}]},{"text":[{"text":"UNIQUE (student_id, paid_on)","code":true},{"text":", because a CHECK evaluates one row at a time and cannot count other rows."}],"correct":true,"tempting":null},{"text":[{"text":"CHECK (student_id IS DISTINCT FROM paid_on)","code":true},{"text":", since the rule is that the two columns must not coincide within the table."}],"correct":false,"tempting":[{"text":"Compares two unrelated columns of the same row, which is meaningless here; the rule is about two different rows sharing both values."}]},{"text":[{"text":"An "},{"text":"EXCLUDE","code":true},{"text":" constraint on the "},{"text":"paid_on","code":true},{"text":" range, because two payments on the same date have overlapping day-long intervals and EXCLUDE is built for overlaps."}],"correct":false,"tempting":[{"text":"EXCLUDE would work but is heavier than needed; an equality on two columns is exactly what UNIQUE expresses, and the CHECK fails for a reason this option does not state."}]}],"order":[3,2,0,1],"why":null},"prev":{"slug":"indexes-and-slow-queries","title":"Why a query is slow, and what an index does","minutes":8},"next":{"slug":"insert-and-upsert","title":"INSERT, and the INSERT you can run twice","minutes":8}}}