NOT NULL, UNIQUE and DEFAULT are the three constraints that do most of the quiet work in a schema. Each one converts a rule that lives in somebody's head — or in application code that will eventually be bypassed — into a rule the database enforces on every write. Three keywords, and most of the classic data-quality bugs become impossible.
What do NOT NULL, UNIQUE and DEFAULT enforce?
The insurer in this module has two tables that matter here: policyholders (the people) and policies (the cover they hold). 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');
And the metadata view shows 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' 0
Three things to read. The notnull column is 1 for every column except the primary key — and the primary key is implicitly NOT NULL anyway, which is why the engine records 0 there. The dflt_value column shows 'active' for the status column and nothing elsewhere. And the pk column flags the key.
What does NOT NULL actually guarantee?
That the column always holds a value for every row that exists. It does not guarantee the value is sensible — only that it is present. In a system with no constraints, the following row would be perfectly storable:
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_id
An insurance policy with no holder is not a policy, and every report that joins policies to policyholders would show a blank name for it. NOT NULL on holder_id, policy_number, product, premium and the two dates means the database refuses that row outright. The cost is one decision at design time; the benefit is that the question never arises in production.
How does 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 active
The row came out active without anybody saying so. That is what makes a default worth declaring: the common case needs no value, so the code that creates the row has fewer branches to get wrong, and the exceptional case passes an explicit value. The resulting distribution is easy to check:
SELECT status, COUNT(*) AS policies FROM policies GROUP BY status ORDER BY status;
status policies
-------- --------
active 8
lapsed 1
Nine policies, eight active and one lapsed. Note the pairing of the two constraints: a NOT NULL column with a DEFAULT can never be NULL, because either the caller supplied a value or the default did. A column with a default and no NOT NULL can still be set to NULL explicitly — which is occasionally exactly what you want, and occasionally a hole.
What does UNIQUE stop?
Two rows with the same value in the column. The policies table declares the policy_number UNIQUE, so the insurer cannot issue the same reference twice, and the policyholders table declares the email UNIQUE so that one person cannot hold two accounts. Issuing 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_number
The statement fails, the existing rows are untouched, and the caller learns about it immediately.
Two refinements are worth knowing. A UNIQUE constraint on a single column permits any number of NULLs, because in SQL NULL is not equal to NULL — two rows with a NULL in the same UNIQUE column do not collide. If your rule is "at most one row may omit this value", you need an additional check or a filtered unique index. And a compound UNIQUE constraint covers a combination of columns, which is the usual rule for things like "one row per customer per day" — written UNIQUE (customer_id, order_date) on the table that holds those two 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 |
Each constraint moves a rule from application code into the database, where it cannot be bypassed. The three in this lesson are the ones you can add to an existing table with almost no risk; CHECK and foreign keys are the subject of lessons 45 and 48.
Quick reference
- NOT NULL guarantees presence, not sense — use CHECK for a sensible value.
- DEFAULT supplies the common case; pair it with NOT NULL so the column can never be blank.
- UNIQUE blocks duplicates but still allows several NULLs in the same column.
- A compound UNIQUE constrains a combination of columns, which is how you express one-row-per-entity-per-period.
- Every constraint is a rule moved from application code into the engine, where it cannot be bypassed.
Constraint drill: write the CREATE TABLE for a table of policy endorsements with an identifier, an endorsement reference that must exist and must be unique, a type that defaults to 'general', and a description that may be omitted — then state which of your four columns could still be NULL and why.