Constraints Are the Only Validation That Cannot Be Bypassed

A colleague adds a fourth option to a status dropdown and mistypes it. The select element now offers overdue while the code that reads the status only handles pending, and for three weeks the dashboard shows those rows in a column of blanks. The validation in the form was correct. Nothing that wrote to the table through the form was wrong. The row arrived through an admin console instead.

The table accepted it because nothing in the schema had an opinion about which statuses exist. That is the whole argument for constraints. Application validation is a gate in one path to the data. A constraint is a property of the data itself, enforced by the engine no matter which path the write took.

This is also the reason constraints feel like friction. They reject writes that were already validated, which looks redundant until the second client exists. It always does.

Application validation only covers the paths you remembered

Count the ways your table gets written: the HTTP handler, the background job, the import, the migration that backfills a column, the admin console someone installed, the DBA with a psql session, and the test fixtures that keep CI honest. Every one is a client, and every one has to agree about the rules.

Now notice that the rules live in one of those clients. A validation function in the service layer is a copy of the rule, not the rule itself. The other clients have their own copies, or none. When the rule changes, one copy changes. When a new client appears, someone has to remember that a rule exists, which is exactly the kind of memory that fails at two in the morning during a backfill.

A second class of bug is subtler. One request can violate an invariant through two statements that are each valid: withdraw from one account, credit another, crash between them. Only the database sees both statements, so only the database can insist they happen together or not at all. Transactions are the tool for that, and the constraints on the tables are what make the transaction's end state meaningful.

NOT NULL and DEFAULT close the hole a CHECK cannot see

The most common surprise with constraints catches people who have written checks for years. An expression that evaluates to NULL is not false. The SQL standard says a constraint is violated only when the expression is false, so NULL means unknown, and unknown passes.

CREATE TABLE promo (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  code         VARCHAR(16) NOT NULL,
  discount_pct INT NULL,
  CONSTRAINT ck_promo_discount CHECK (discount_pct BETWEEN 1 AND 90)
);

INSERT INTO promo (code, discount_pct) VALUES ('HALF', NULL);
INSERT INTO promo (code, discount_pct) VALUES ('BAD', 150);

ERROR 3819 (HY000): Check constraint 'ck_promo_discount' is violated.

The second insert fails and the first one succeeds. NULL BETWEEN 1 AND 90 is unknown, not false, so the row is accepted. Every engine behaves this way, and both MySQL and SQLite rejected BAD while keeping HALF. If the column is required, say so with NOT NULL and let the check handle only the values that exist:

ALTER TABLE promo MODIFY discount_pct INT NOT NULL;

UPDATE promo SET discount_pct = 10 WHERE discount_pct IS NULL;

INSERT INTO promo (code, discount_pct) VALUES ('FREE', NULL);

ERROR 1048 (23000): Column 'discount_pct' cannot be null

The order of those statements is the whole lesson of a migration: adding NOT NULL to a column with existing NULL values fails, so the backfill has to come first, and the backfill has to be possible. If a NULL discount genuinely means "no discount", keep the column nullable and add the NOT NULL to a different column, or write the check so that the NULL case is explicit:

ALTER TABLE promo
  ADD CONSTRAINT ck_promo_discount
    CHECK (discount_pct IS NULL OR discount_pct BETWEEN 1 AND 90);

The ALTER TABLE ... MODIFY spelling is MySQL and MariaDB. PostgreSQL writes ALTER TABLE promo ALTER COLUMN discount_pct SET NOT NULL, and SQLite refuses it outright, so there you rebuild the table into a new one with the constraint and copy the rows across. What is the same everywhere is the failure mode: the engine checks the existing rows, and the migration aborts if any of them violates the new rule.

DEFAULT is the opposite kind of rule: it supplies a value when the insert does not name the column. It is not a constraint, because it never rejects anything, and it has one behaviour worth memorising. A default applies to an omitted column, not to an explicit NULL. Writing INSERT INTO promo (code, discount_pct) VALUES ('X', NULL) is not the same statement as INSERT INTO promo (code) VALUES ('X'), and only the second one gets the default.

UNIQUE says what a row is, and CHECK says which values are legal

UNIQUE is the constraint that turns a table back into a set. Declare it on the columns that identify a fact and the engine refuses the second copy, which is the only reliable defence against a double import or a retried webhook.

CREATE TABLE plan (
  code          VARCHAR(16) PRIMARY KEY,
  monthly_cents INT NOT NULL,
  CONSTRAINT ck_plan_price CHECK (monthly_cents > 0)
);

INSERT INTO plan (code, monthly_cents) VALUES ('care', 1200);
INSERT INTO plan (code, monthly_cents) VALUES ('care', 999);

ERROR 1062 (23000): Duplicate entry 'care' for key 'plan.PRIMARY'

The duplicate error arrives before the price check would be evaluated, because the unique index probe happens as soon as the row is inserted. That ordering is worth knowing when you read an error log: you are seeing the first rule that failed, not the only one that would have. A multi-column UNIQUE (a, b) means the pair is unique, not each column on its own, and that difference decides whether a many-to-many join table can record the same pair twice.

CHECK is a per-row predicate, evaluated on insert and on every update of the columns it touches. It fits a rule that compares values inside one row: dates in order, a positive amount, a status from a known list.

CREATE TABLE subscription (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  customer_email VARCHAR(255) NOT NULL,
  plan_code     VARCHAR(16) NOT NULL,
  status        VARCHAR(16) NOT NULL DEFAULT 'trialing',
  started_on    DATE NOT NULL,
  cancelled_on  DATE NULL,
  CONSTRAINT fk_sub_plan FOREIGN KEY (plan_code) REFERENCES plan (code)
    ON DELETE RESTRICT,
  CONSTRAINT ck_sub_status
    CHECK (status IN ('trialing', 'active', 'past_due', 'cancelled')),
  CONSTRAINT ck_sub_cancelled CHECK (
    (status = 'cancelled' AND cancelled_on IS NOT NULL) OR
    (status <> 'cancelled' AND cancelled_on IS NULL)
  )
);

INSERT INTO subscription (customer_email, plan_code, status, started_on, cancelled_on)
VALUES ('cy@example.com', 'care', 'active ', '2026-10-01', NULL);

ERROR 3819 (HY000): Check constraint 'ck_sub_status' is violated.

The trailing space failed the check, which is exactly the mistyped dropdown from the opening of this lesson. SQLite names it too, printing CHECK constraint failed: ck_sub_status, while MySQL prints Check constraint 'ck_sub_status' is violated. Name your constraints. An error that says ck_sub_cancelled tells the next person which rule they broke, and an unnamed check reports an expression they have to decode.

One limitation shapes how you write these. A CHECK cannot read another table, so "the plan code must exist" is not a check, it is a foreign key. Where you want a value from a managed list, put the list in a lookup table and point a foreign key at it, which also lets the list change without a schema migration.

FOREIGN KEY decides what happens when the parent disappears

A foreign key is referential integrity, and the ON DELETE option is the part people leave at the default without deciding. The default is usually NO ACTION, which behaves like RESTRICT for the purposes of this discussion: the delete fails.

DELETE FROM plan WHERE code = 'care';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint
fails (`wpsifero_lab`.`subscription`, CONSTRAINT `fk_sub_plan` FOREIGN KEY
(`plan_code`) REFERENCES `plan` (`code`) ON DELETE RESTRICT)

RESTRICT protects history. CASCADE follows the parent down and removes children, which is correct for rows that cannot exist alone. A draft belongs to a subscription, so deleting the subscription should delete the draft:

CREATE TABLE draft (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  subscription_id INT NOT NULL,
  body            VARCHAR(32) NOT NULL,
  CONSTRAINT fk_draft_sub FOREIGN KEY (subscription_id)
    REFERENCES subscription (id) ON DELETE CASCADE
);

INSERT INTO draft (subscription_id, body) VALUES (1, 'hello');
DELETE FROM subscription WHERE id = 1;
SELECT COUNT(*) AS drafts_left FROM draft;

+-------------+
| drafts_left |
+-------------+
|           0 |
+-------------+

SET NULL keeps the child and erases the link, and lesson one showed what that costs when the link carried meaning. The fourth option, SET DEFAULT, is legal in the standard and supported by MySQL and SQLite, and I have never seen a case where it beat the other three.

Every constraint costs something on the write path

Constraints are not free, and the costs differ enough to matter at volume. The table below is a rough ordering, not a benchmark, because the real numbers depend on your engine, your row size and your storage. Run your own before you let it decide anything.

Two of those costs deserve a second look. The foreign key lock on the parent is the one that shows up in production as contention: every insert into a busy child table takes a shared lock on the parent row, so a transaction that updates that parent and holds it blocks all of them. The partial unique index is the opposite story, a constraint that is cheaper than the alternatives because it covers fewer rows.

What constraints cannot do is equally worth stating. They cannot enforce a rule that needs two queries, such as "a customer may have at most one active subscription" across tables, without a trigger or a carefully designed unique index. They cannot validate that an email address is deliverable. They cannot stop an application from deleting the right row. And a constraint added to a table with existing bad data fails to apply until you clean the data, which is a deployment problem rather than a design one. All of that is module five.

Put the rule where no new client can miss it

The decision procedure I use is short. If a value is required, make the column NOT NULL. If a fact must be unique, add the constraint, not a check in the service. If a rule compares columns in one row, it is a CHECK. If it references another table, it is a foreign key. If it needs two statements or another table's aggregate, it is a trigger or a transaction, and you should write down which one you chose and why.

Then treat the application validation as a courtesy rather than the guarantee. The service layer should still check, because a clear message at the right layer is better for the user, but the database is the thing that is actually right. When you see a constraint error in production, you have found a code path that skipped a rule.

The subscription table is where the rules get written down

CREATE TABLE subscription (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  customer_email VARCHAR(255) NOT NULL,
  plan_code      VARCHAR(16)  NOT NULL,
  status         VARCHAR(16)  NOT NULL DEFAULT 'trialing',
  monthly_cents  INT NOT NULL,
  started_on     DATE NOT NULL,
  cancelled_on   DATE NULL,
  CONSTRAINT uq_subscription_email UNIQUE (customer_email),
  CONSTRAINT ck_subscription_price CHECK (monthly_cents >= 0),
  CONSTRAINT ck_subscription_status
    CHECK (status IN ('trialing', 'active', 'past_due', 'cancelled')),
  CONSTRAINT ck_subscription_cancelled CHECK (
    (status = 'cancelled' AND cancelled_on IS NOT NULL) OR
    (status <> 'cancelled' AND cancelled_on IS NULL)
  ),
  CONSTRAINT ck_subscription_dates
    CHECK (cancelled_on IS NULL OR cancelled_on >= started_on),
  CONSTRAINT fk_subscription_plan FOREIGN KEY (plan_code)
    REFERENCES plan (code) ON DELETE RESTRICT
);

-- duplicate email
ERROR 1062 (23000): Duplicate entry 'ana@example.com' for key 'subscription.uq_subscription_email'

-- negative price
ERROR 3819 (HY000): Check constraint 'ck_subscription_price' is violated.

-- status with a trailing space
ERROR 3819 (HY000): Check constraint 'ck_subscription_status' is violated.

-- cancelled with no date
ERROR 3819 (HY000): Check constraint 'ck_subscription_cancelled' is violated.

-- plan that does not exist
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`wpsifero_lab`.`subscription`, CONSTRAINT `fk_subscription_plan` FOREIGN KEY
(`plan_code`) REFERENCES `plan` (`code`) ON DELETE RESTRICT)