UNIQUE and NOT NULL
A sign-up form has a bug that lets people register twice with the same email. Another form lets customers skip their name entirely. Both bugs live in application code — but two short words in the table definition would have stopped both, no matter which app, script or person wrote the data.
The rule
UNIQUE forbids repeats; NOT NULL forbids blanks. Together they make a column trustworthy.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
phone VARCHAR(15) UNIQUE NOT NULL
);NOT NULL means every row must have a value in that column. Leaving it out of an INSERT (with no default) or setting it to NULL is rejected. It says nothing about repeats — two customers may both be called Asha.
UNIQUE means no two rows may share a value. It says nothing about blanks: in PostgreSQL, MySQL and SQLite, many rows may have NULL in a UNIQUE column, because NULL means "unknown" and two unknowns are not considered equal. SQL Server is the exception: it allows only one NULL in a UNIQUE column.
Combine them and you get "every row has a value, and no value repeats" — the same guarantee as a primary key, which is why phone above could have been the key. The difference: a table has one primary key but may have many UNIQUE columns.
UNIQUE can also cover a combination of columns:
CREATE TABLE product_names (
category VARCHAR(50) NOT NULL,
name VARCHAR(100) NOT NULL,
UNIQUE (category, name)
);Now "Notebook" can exist in both stationery and gifts, but not twice in stationery.
The shortcut
For each column, ask two independent yes/no questions:
- Must every row have it? Yes → NOT NULL.
- Must no two rows share it? Yes → UNIQUE.
Four combinations, four meanings — do not let one answer leak into the other.
name
Must every customer have one? Yes. Must names be different? No. →
NOT NULLonly.
Drills
Answer fast — trust the rule.
email is UNIQUE (nullable) in PostgreSQL. Can two customers both have a NULL email?
Which constraint stops two customers from both being named 'Asha'?
With UNIQUE (category, name), which insert is rejected if ('stationery', 'Notebook') already exists?
Which column definition means "every row has a value and no two rows share it"?
Remember
- UNIQUE forbids repeats; NOT NULL forbids blanks.
- Ask two separate questions: must every row have it? must no two rows share it?
- A UNIQUE column can still hold many NULLs in most databases.