SQL / DEFINING TABLES AND CONSTRAINTS
NOT NULL and deciding what must always exist
Decide which columns can never be missing, declare NOT NULL on them, predict the errors it raises, and add it to a populated table safely.
What you will learn
- Choose NOT NULL by asking whether a row still means anything with the value missing
- Predict which INSERT fails: an omitted column stores NULL unless it has a DEFAULT
- Count existing NULLs and backfill before ALTER TABLE ... SET NOT NULL
- Spot placeholders like 'unknown' or -1 that satisfy NOT NULL and corrupt averages
Understanding NOT NULL and deciding what must always exist
NULL is not a value; it is a marker meaning no value is recorded here. NOT NULL turns that marker into an error: any INSERT or UPDATE that would leave the column empty is rejected before the row version is written. The decision is about meaning rather than tidiness, so ask whether a row can still be a true statement about the world while this one fact is unknown. A shipment with no tracking number is not a shipment record at all, so tracking_no is NOT NULL; a shipment that has not arrived yet genuinely has no delivery time, so delivered_at must stay nullable.
The cost of a nullable column is paid in every query that reads it. Because NULL means unknown, comparisons against it produce unknown instead of true, so WHERE delivered_at > '2026-01-01' silently drops undelivered rows, count(delivered_at) returns less than count(*), and sums and joins skip the gaps without saying so. NOT NULL is the promise that lets you stop writing IS NULL branches and COALESCE wrappers for that column forever. Columns are nullable unless you say otherwise, so omitting NOT NULL is also a decision, usually an unintended one.
The reliable sign of a wrong NOT NULL is that you have to invent something to satisfy it: 'unknown', -1, 1900-01-01, an empty string. A placeholder is an ordinary value, so it gets compared, averaged and joined like any other, and every future reader must learn the sentinel or be misled by it. When that happens the fact is either genuinely optional, or it is learned after the row is created, in which case it belongs in a nullable column or in a separate row that exists only once the fact does. That timing question, is this known at INSERT time, settles more NOT NULL arguments than any style rule.
-- PostgreSQL, run as separate statements (autocommit)
CREATE TABLE shipment (
id integer PRIMARY KEY,
tracking_no text NOT NULL,
carrier text NOT NULL,
delivered_at timestamp -- unknown until the parcel arrives
);
INSERT INTO shipment (id, tracking_no, carrier)
VALUES (1, 'ZX-9931', 'Ampere Post');
INSERT INTO shipment (id, tracking_no, carrier, delivered_at)
VALUES (2, 'ZX-9932', 'Ampere Post', '2026-02-11 08:15');
-- carrier is left out of the column list, so NULL is what gets offered
INSERT INTO shipment (id, tracking_no)
VALUES (3, 'ZX-9933');
SELECT count(*) AS rows_stored, count(delivered_at) AS delivery_known
FROM shipment;NOT NULL is a per-row promise that a fact is always known, so it belongs only on columns whose absence would make the row meaningless, and never on columns you would have to fill with an invented value.
Worked examples
Adding NOT NULL to a column that already holds NULLs
Shows why SET NOT NULL fails on a populated table and what backfilling really costs.
CREATE TABLE contact (
id integer PRIMARY KEY,
email text NOT NULL,
phone text
);
INSERT INTO contact VALUES (1, 'ada@example.com', '555-0100'),
(2, 'grace@example.com', NULL);
ALTER TABLE contact ALTER COLUMN phone SET NOT NULL;
UPDATE contact SET phone = 'unknown' WHERE phone IS NULL;
ALTER TABLE contact ALTER COLUMN phone SET NOT NULL;Example explained
Line 1email text NOT NULL applies from the moment the table exists, so no row can ever be stored without an address.
Line 2The two-row INSERT succeeds because phone is nullable, and row 2 stores a real NULL, not an empty string.
Line 3SET NOT NULL scans the existing rows first and refuses while row 2 is NULL; the error names the column, so you know where to look.
Line 4The UPDATE makes the ALTER succeed, but 'unknown' is now indistinguishable from a real number, which is the signal that phone should have stayed nullable.
An empty string is not NULL
Demonstrates that NOT NULL blocks absence, not blankness.
CREATE TABLE note (
id integer PRIMARY KEY,
body text NOT NULL
);
INSERT INTO note VALUES (1, ''), (2, 'real text');
SELECT id, body = '' AS is_blank, length(body) AS chars
FROM note
ORDER BY id;Example explained
Line 1VALUES (1, '') is accepted because '' is a zero-length string, which is a value; NULL is the absence of one.
Line 2length(body) returns 0 for row 1, proving something really was stored in a NOT NULL column.
Line 3body = '' returns t, whereas body = NULL would return NULL and match nothing, which is why absence needs IS NULL.
Line 4Rejecting blank-but-present text is a CHECK constraint's job, not something NOT NULL can do.
UPDATE is checked too
Shows that the constraint guards every write to the row, not just the insert.
CREATE TABLE invoice (
id integer PRIMARY KEY,
status text NOT NULL
);
INSERT INTO invoice VALUES (1, 'open');
UPDATE invoice SET status = NULL WHERE id = 1;
UPDATE invoice SET status = 'paid' WHERE id = 1;
SELECT * FROM invoice;Example explained
Line 1The first UPDATE is rejected exactly like a bad INSERT: NOT NULL is verified on every row version written.
Line 2Only that one statement rolled back, so the row still held 'open' and the next UPDATE reports UPDATE 1.
Line 3The final SELECT shows 'paid', confirming the rejected statement left no half-applied change behind.
Important notes
A PRIMARY KEY column is already NOT NULL, so restating it is harmless but redundant, and you cannot DROP NOT NULL from it while it remains the key.
MySQL only rejects NULL in a NOT NULL column reliably under strict mode; without it, multi-row inserts coerce the value to '' or 0 and raise a warning instead of an error.
Common mistakes
Reading NOT NULL as required input: '' and 0 satisfy it, so a required name column fills up with empty strings and reports look complete while being blank.
Backfilling 'unknown' or 1900-01-01 to make a new NOT NULL pass: MIN, AVG and equality now include an event that never happened, and every query must know the sentinel.
Running ALTER TABLE ... SET NOT NULL without checking for existing NULLs: the statement aborts, and inside a migration transaction it discards the steps that already ran.
Try it yourself
Change, predict, then run
Create a booking table where id, guest_name and nights are NOT NULL and cancelled_at is nullable. Then run one INSERT that omits nights and another that sets guest_name to '', and note which one the database rejects and why the other slips through.
Open the SQL workspaceCheck your understanding
A patients table has a nullable discharged_at, and a report computes AVG(discharged_at - admitted_at). Someone proposes making discharged_at NOT NULL and filling the gaps with the admission timestamp so the data is complete. What happens to the report?
- Nothing changes, because AVG already treats NULL as zero.
- AVG raises an error, because subtracting two equal timestamps yields NULL.
- Patients still in hospital become zero-length stays and pull the average down, whereas as NULL they were skipped entirely.
- The placeholder rows are ignored by AVG because their difference is zero.
Show answer
AVG divides by the count of non-NULL values, so a NULL discharge time keeps an unfinished stay out of the average completely; the placeholder is an ordinary value and is averaged in as a zero-length stay. The first option is tempting because NULL looks like nothing, but SQL aggregates skip NULL rows rather than scoring them as 0, which is exactly why forcing NOT NULL with an invented value is worse here than leaving the column nullable.