Rules for your data

Why put rules in the database at all?

Your app probably checks its forms. But apps are not the only thing that writes data. There is the admin script someone ran last spring, the CSV import, the second app built by another team, the AI-written migration, and you at a SQL prompt on a Friday afternoon. Each of them skips the form.

A constraint is a rule stored with the table. The database checks it on every INSERT and UPDATE, from every source, and refuses any change that breaks it. Bad data never gets in, so you never have to clean it out later or wonder which report it has already spoiled.

Keep the checks in your app as well. They give friendly messages before anything is sent. The database rule is the last line that can't be skipped.

What does NOT NULL do?

NOT NULL means the column must have a value. Leave it out of an INSERT, or set it to NULL, and the statement fails with NOT NULL constraint failed: orders.qty. The message names the table and column, which makes it the easiest error to fix.

Use it on every column that a row makes no sense without: a product's name, an order's customer, a loan's date. Leave it off columns where 'unknown' is a real, expected state, like a customer's email when they ordered at the counter. Sam Patel in this database has no email, and that is fine.

SELECT name, "notnull"
FROM pragma_table_info('orders');

What does UNIQUE allow and refuse?

UNIQUE refuses a value that already exists in that column. Two customers can't share ada@example.com, and two products can't both be called Sourdough. The error reads UNIQUE constraint failed: customers.email.

NULL is the exception. Each NULL counts as unknown, and two unknowns are not equal, so several customers may have no email at all. That matches what you usually want. If it isn't, add NOT NULL too.

A rule can cover a pair of columns: UNIQUE (customer_id, product_id, ordered_on) at the end of the column list allows the same customer and product on different days but not twice on one day. Text comparison is case-sensitive, so Tom@example.com and tom@example.com are different values to SQLite. Store emails in lowercase, or add COLLATE NOCASE to the column, if case shouldn't matter.

SELECT name, email
FROM customers
WHERE email IS NULL OR email LIKE '%@example.com';

How do CHECK rules work?

CHECK (expression) runs the expression for each new or changed row. If it comes out false, the change is refused. If it comes out NULL, the row is allowed, because a CHECK only rejects a definite false. That is why CHECK (price_pence > 0) lets a NULL price through unless the column is also NOT NULL.

The expression can use any columns of the same row: CHECK (returned_on IS NULL OR returned_on >= loaned_on) keeps loan dates in order. It can't look at other rows or other tables. Give a rule a name with CONSTRAINT price_positive CHECK (…) and that name appears in the error, which helps when a table has several checks.

MySQL ignored CHECK rules entirely before version 8.0.16, so older MySQL code may contain checks that never ran.

SELECT name, price_pence
FROM products
WHERE price_pence > 0
ORDER BY price_pence;

What does DEFAULT fill in?

DEFAULT value supplies a value when an INSERT leaves the column out. Here a product with no category becomes 'bread'. CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP give the date or time (in UTC) at the moment of the insert. An expression in brackets also works, for example DEFAULT (date('now', '+14 days')) for a library due date.

A default only applies when the column is missing from the INSERT. Writing NULL explicitly stores NULL (or fails, if the column is also NOT NULL). Run the example and open Changes: the new row has bread filled in.

INSERT INTO products (name, price_pence)
VALUES ('Spelt loaf', 480);
SELECT name, category, price_pence
FROM products
WHERE name = 'Spelt loaf';

How do foreign keys protect links between tables?

REFERENCES products(id) says every product_id must match an existing product. Insert an order for product 50 and SQLite stops with FOREIGN KEY constraint failed. Unlike the other errors, this one doesn't say which key failed, so check each REFERENCES column in turn.

SQLite enforces foreign keys only when PRAGMA foreign_keys = ON has been run on the connection. It is off by default for backward compatibility. PlainFinch switches it on before every run. In your own app, run that pragma each time you open the database, or the rule is silently ignored. PostgreSQL and MySQL (with InnoDB tables) always enforce them.

SELECT o.id, c.name, p.name AS product, o.qty
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products AS p ON p.id = o.product_id;

What happens to orders when a customer is deleted?

The ON DELETE part of a foreign key decides. Without one, deleting a customer who has orders fails, which protects the orders. The options you will meet most:

DELETE FROM customers
WHERE id = 2;
SELECT COUNT(*) AS orders_left
FROM orders;

• ON DELETE CASCADE: delete the child rows too. Here, deleting Tom deletes his orders. Good for rows that mean nothing without their parent.
• ON DELETE RESTRICT (or no action): refuse to delete a parent that still has children. Here, a product that has been ordered can't be deleted, which keeps sales history intact.
• ON DELETE SET NULL: keep the child and blank its link. The column must allow NULL.
• ON UPDATE CASCADE: if a parent's key changes, update the children to match. Rarely needed when ids never change.

How do I add rules to a table that already exists?

SQLite's ALTER TABLE can't add a CHECK, UNIQUE or foreign key to an existing column. A UNIQUE rule can be added later as a unique index (CREATE UNIQUE INDEX … ON customers (email);). For the others, build a new table with the rules, copy the rows across with INSERT … SELECT, drop the old one and rename the new one, inside a transaction. Expect the copy to fail if old rows break the new rules. That failure is useful: it tells you exactly which data needs cleaning first.

When an AI tool writes a schema, read each column and ask what values should never appear in it. Generated schemas often have no NOT NULL at all, use VARCHAR lengths as if they were limits, and declare foreign keys without anything that turns enforcement on in SQLite.

Field notes

• Rules in the database protect your data from every writer, including scripts and imports.
• NOT NULL means a column must always have a value.
• A CHECK rule refuses a row when its test is false, but lets a NULL result through.
• REFERENCES makes sure a linked id points at a row that really exists.

Easy to get wrong

A CHECK passes when the expression is NULL, so a missing price gets through. Pair CHECK with NOT NULL when a value is required.

SQLite checks foreign keys only after this pragma on each connection. Without it, an order for a customer who doesn't exist is saved without complaint.

CASCADE deletes children silently. On records you must keep, like invoices or sales, one DELETE on a customer wipes their history. Choose CASCADE only for rows that are worthless without the parent.

A DEFAULT applies only when the column is left out. Writing NULL asks for NULL, and here NOT NULL refuses it.

Questions people ask

Do constraints slow the database down?

Slightly. NOT NULL and CHECK cost almost nothing. UNIQUE builds an index that also speeds up lookups. Foreign key checks look up the parent, which is fast because parents are found by primary key. Index the child column too, or deleting a parent has to scan every child row.

Can a constraint use a value from another table?

A CHECK can't. Foreign keys are the built-in way to compare against another table. Anything more complicated needs a trigger, which is a statement the database runs automatically on insert, update or delete.

Why didn't my rule apply to rows already in the table?

Rules are checked when rows are written. SQLite doesn't let you add a CHECK to an existing table, so old rows were never tested. Rebuilding the table with INSERT … SELECT tests every old row against the new rule.

What does INSERT OR IGNORE do with constraint errors?

It skips the row that broke a NOT NULL, UNIQUE, CHECK or primary key rule instead of stopping, and carries on. It doesn't cover foreign keys. Use it carefully: skipped rows leave no trace, so you may lose data without noticing.

Is PRIMARY KEY a constraint too?

Yes. It combines UNIQUE with identifying the row. In SQLite, an INTEGER PRIMARY KEY also can't be NULL. Other primary keys can be, because of an old quirk, so add NOT NULL to them.