The NOT NULL, the UNIQUE and the DEFAULT constraints do most of the quiet work in a schema. Each one turns a rule that lives in somebody's head, or in application code that somebody will eventually bypass, into a rule the database enforces on every single write.
Three keywords, and most of the classic data-quality bugs can no longer happen.
This is the first lesson in the Constraints and Integrity module, and the next lesson in this module is about "How Do CHECK Constraints Catch Bad Data Early?".
What do NOT NULL, UNIQUE and DEFAULT enforce?
The insurer in this module has two tables that matter here. The first is policyholders, the people who hold the cover. The second is policies, the cover itself.
The policies table carries all three constraints at once, and the metadata view shows exactly what was declared.
CREATE TABLE policyholders (
holder_id INTEGER PRIMARY KEY,
full_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
city TEXT,
joined_on TEXT
);
CREATE TABLE policies (
policy_id INTEGER PRIMARY KEY,
holder_id INTEGER NOT NULL REFERENCES policyholders(holder_id),
policy_number TEXT NOT NULL UNIQUE,
product TEXT NOT NULL,
premium REAL NOT NULL,
start_date TEXT NOT NULL,
end_date TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'active'
);Five holders and eight policies load the module.
INSERT INTO policyholders (holder_id, full_name, email, city, joined_on) VALUES
(1, 'Marta Lindqvist', '[email protected]', 'Bristol', '2021-03-14'),
(2, 'Dev Raghunathan', '[email protected]', 'Cardiff', '2022-07-08'),
(3, 'Salome Adeyinka', '[email protected]', 'Leeds', '2023-01-19'),
(4, 'Tomas Bergland', '[email protected]', 'Manchester', '2023-09-26'),
(5, 'Elif Dogan', '[email protected]', 'Glasgow', '2024-05-07');
INSERT INTO policies (policy_id, holder_id, policy_number, product, premium, start_date, end_date, status) VALUES
(1, 1, 'POL-2020-0001', 'travel', 412.50, '2020-03-01', '2021-02-28', 'active'),
(2, 2, 'POL-2020-0002', 'home', 96.00, '2020-07-15', '2021-07-14', 'active'),
(3, 3, 'POL-2021-0003', 'travel', 88.75, '2021-01-20', '2022-01-19', 'active'),
(4, 4, 'POL-2021-0004', 'motor', 355.00, '2021-06-05', '2022-06-04', 'active'),
(5, 1, 'POL-2022-0005', 'home', 132.40, '2022-02-11', '2023-02-10', 'active'),
(6, 5, 'POL-2022-0006', 'travel', 210.00, '2022-09-30', '2023-09-29', 'active'),
(7, 2, 'POL-2023-0007', 'motor', 540.00, '2023-04-18', '2024-04-17', 'active'),
(8, 3, 'POL-2024-0007', 'home', 64.20, '2024-08-01', '2025-07-31', 'lapsed');Now ask the metadata view what the policies table actually declared.
PRAGMA table_info(policies);cid name type notnull dflt_value pk
--- ------------- ------- ------- ---------- --
0 policy_id INTEGER 0 NULL 1
1 holder_id INTEGER 1 NULL 0
2 policy_number TEXT 1 NULL 0
3 product TEXT 1 NULL 0
4 premium REAL 1 NULL 0
5 start_date TEXT 1 NULL 0
6 end_date TEXT 1 NULL 0
7 status TEXT 1 'active' 0There are three things to read here. The notnull column shows 1 for every column except the primary key, and the 0 on the primary key is nothing to worry about, because a primary key is implicitly NOT NULL anyway. The dflt_value column shows the 'active' on the status column and nothing anywhere else. The pk column flags the key.
What does the NOT NULL actually guarantee?
It guarantees that every row in the table holds a value in that column. It says nothing about whether the value makes sense. The NOT NULL only promises that a value is there.
In a table with no constraints, the next row would store perfectly well.
INSERT INTO policies (policy_id, holder_id, policy_number, product, premium, start_date, end_date)
VALUES (9, NULL, 'POL-2026-0009', 'travel', 82.00, '2026-02-01', '2027-01-31');NOT NULL constraint failed: policies.holder_idAn insurance policy with no holder is not a policy at all. Every report that joins policies to policyholders would show a blank name next to it.
The NOT NULL on holder_id, on policy_number, on product, on premium and on both dates makes the database refuse that row outright.
The cost is a single decision at design time. The benefit is that the question never comes up in production.
How does the DEFAULT fill the gap?
A default supplies a value when the caller does not. Insert a policy and say nothing about its status.
INSERT INTO policies (policy_id, holder_id, policy_number, product, premium, start_date, end_date)
VALUES (9, 4, 'POL-2026-0009', 'travel', 82.00, '2026-02-01', '2027-01-31');
SELECT policy_id, policy_number, status FROM policies WHERE policy_id = 9;policy_id policy_number status
--------- ------------- ------
9 POL-2026-0009 activeThe row comes out active and nobody asked for it. That is what makes a default worth declaring. The common case needs no value at all, so the code that creates the row has fewer branches to get wrong, and the rare case passes an explicit value.
Checking the result is easy.
SELECT status, COUNT(*) AS policies FROM policies GROUP BY status ORDER BY status;status policies
-------- --------
active 8
lapsed 1There are nine policies: eight are active and one is lapsed.
Notice how the two constraints work together. A NOT NULL column that also has a DEFAULT can never end up as NULL, because the caller supplied a value or the default did. A column with a default and no NOT NULL can still be set to NULL on purpose. Sometimes that is exactly what you want, and sometimes it is a hole in your data.
What does the UNIQUE stop?
It stops two rows from holding the same value in that column.
The policies table declares policy_number as UNIQUE, so the insurer cannot issue the same reference twice. The policyholders table declares email as UNIQUE, so one person cannot hold two accounts.
Trying to issue a reference that already exists is refused.
INSERT INTO policies (policy_id, holder_id, policy_number, product, premium, start_date, end_date)
VALUES (9, 4, 'POL-2024-0007', 'travel', 82.00, '2026-02-01', '2027-01-31');UNIQUE constraint failed: policies.policy_numberThe statement fails, the rows that were already there stay exactly as they were, and the caller hears about it straight away.
Two refinements are worth knowing.
A UNIQUE constraint on a single column allows any number of NULL values, because in SQL a NULL never equals a NULL. Two rows with a NULL in the same UNIQUE column do not collide. If your rule is "at most one row may leave this value out", you need an extra check or a filtered unique index.
A compound UNIQUE constraint covers a combination of columns. That is the usual way to write rules like "one row per customer per day", and you write it as UNIQUE (customer_id, order_date) on the table that holds both columns.
What does each constraint buy you?
| Constraint | Rule enforced | Typical mistake it prevents |
|---|---|---|
| NOT NULL | a value must be present | rows with a missing owner or date |
| UNIQUE | no two rows share the value | duplicate accounts, duplicate references |
| DEFAULT | a value is supplied when omitted | blank statuses and forgotten flags |
| PRIMARY KEY | unique and never null | rows that cannot be referenced |
| CHECK | the value satisfies an expression | negative premiums, impossible dates |
| FOREIGN KEY | the reference exists | orphan rows and broken relationships |
Every constraint moves a rule out of the application code and into the database, where nobody can bypass it.
The three you have just seen are the ones you can add to an existing table with almost no risk. The CHECK constraint and the foreign keys are the subject of lessons 45 and 48.
Quick reference
- The
NOT NULLguarantees that a value is present, not that it is sensible. You use theCHECKfor a sensible value. - The
DEFAULTsupplies the common case. Pair it with theNOT NULLso the column can never be blank. - The
UNIQUEblocks duplicates, but it still allows severalNULLvalues in the same column. - A compound
UNIQUEconstrains a combination of columns. That is how you express one row per entity per period. - Every constraint moves a rule from the application code into the engine, where it cannot be bypassed.
Constraint drill: write the CREATE TABLE for a table of policy endorsements. Give it an identifier, an endorsement reference that must exist and must be unique, a type that defaults to 'general', and a description that may be left out. Then say which of your four columns could still be NULL, and why.