SQL Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT and Keys

A constraint is a rule the database refuses to break. Not a validation you hope everybody remembers to run — a rule enforced on every insert and update, from every application, every script and every person with a query window.

That distinction is the whole argument for using them. Application code can be bypassed by the next application; a constraint cannot. This page covers all six, shows what SQLite — the database engine running in your browser — says when each one is broken, and ends with which to reach for.

All six, in one table

CREATE TABLE Invoices (
  invoice_id  INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount      DECIMAL(10,2) CHECK (amount > 0),
  status      VARCHAR(10) DEFAULT 'draft',
  FOREIGN KEY (customer_id) REFERENCES Customers (customer_id)
);

INSERT INTO Invoices (invoice_id, customer_id, amount)
VALUES (1, 101, 250);

SELECT * FROM Invoices;

One insert, five rules checked, and note what happened to status: it was not supplied, so the DEFAULT filled it in. Every rule in that table is now enforced for the lifetime of the table.

NOT NULL: this column must have a value

The simplest and most under-used constraint. Leave it off and every column silently accepts “unknown”, which is how a NULL ends up in an amount column and an arithmetic expression returns nothing.

CREATE TABLE Contacts (
  contact_id INTEGER PRIMARY KEY,
  full_name  VARCHAR(100) NOT NULL,
  phone      VARCHAR(20)
);

INSERT INTO Contacts VALUES (1, 'Asha Patel', NULL);

SELECT * FROM Contacts;

The phone number is allowed to be missing; the name is not. Try inserting a row with no full_name and SQLite answers NOT NULL constraint failed: Contacts.full_name (the playground shows the same refusal reworded in plain English) — the insert is rejected, not silently patched.

PRIMARY KEY: the identity of a row

A primary key is UNIQUE and NOT NULL together, and it is how every other table refers to this one:

CREATE TABLE Members (
  email     VARCHAR(255) NOT NULL PRIMARY KEY,
  joined_on DATE NOT NULL
);

INSERT INTO Members VALUES ('asha@example.com', '2026-01-04');

SELECT * FROM Members;

Insert that same email twice and SQLite refuses with UNIQUE constraint failed: Members.email — it reports a broken primary key as a broken uniqueness rule, which is what it is. That refusal is the point: it is what stops duplicate rows from ever existing, rather than leaving you to find and delete them later.

The NOT NULL in that definition is there for SQLite’s sake. The SQL standard says a primary key can never be NULL, and PostgreSQL, MySQL, SQL Server and Oracle enforce that by themselves. SQLite keeps an old bug for backwards compatibility: it lets NULL into a PRIMARY KEY column unless the column is also declared NOT NULL. The exceptions are an INTEGER PRIMARY KEY, where SQLite fills in a number instead of storing NULL, and tables declared STRICT or WITHOUT ROWID, which refuse the NULL. So on SQLite, write NOT NULL next to any other kind of primary key.

One design note that comes up in every interview: a key made of data that can change — an email address, a phone number — means every table referencing it has to change when the data does. A meaningless id avoids that, which is why most schemas use one. The primary key vs foreign key page goes into the trade-off.

UNIQUE: no two rows the same

UNIQUE is a primary key without the identity role, and you can have as many as you like. A users table might have an id as its primary key and a unique constraint on the email as well — both are enforced, but only one identifies the row.

The difference from a primary key that catches people out: a unique column can hold NULL, and because NULL is not equal to anything, including another NULL, two NULLs do not count as a duplicate. PostgreSQL, MySQL, Oracle and SQLite therefore accept any number of NULLs in a unique column:

CREATE TABLE Accounts (
  account_id INTEGER PRIMARY KEY,
  email      VARCHAR(255) UNIQUE
);

INSERT INTO Accounts VALUES (1, 'asha@example.com');
INSERT INTO Accounts VALUES (2, NULL);
INSERT INTO Accounts VALUES (3, NULL);

SELECT * FROM Accounts;

Both accounts without an email were accepted. Insert 'asha@example.com' a second time, though, and SQLite refuses with UNIQUE constraint failed: Accounts.email. SQL Server is the exception: it treats NULLs as equal for this purpose, so its unique constraint allows only one. That is a genuine portability difference worth checking if your data has optional values that must be unique when present.

FOREIGN KEY: the value must exist over there

A foreign key says this column’s values must appear in another table — the rule that keeps a database internally consistent:

CREATE TABLE Reviews (
  review_id  INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  stars      INTEGER CHECK (stars BETWEEN 1 AND 5),
  FOREIGN KEY (product_id) REFERENCES Products (product_id)
);

INSERT INTO Reviews VALUES (1, 201, 5);

SELECT * FROM Reviews;

Product 201 exists, so the review is accepted. Change it to 999 and SQLite answers FOREIGN KEY constraint failed. Without that line the column would accept 999 happily and you would have a review of nothing — a row that no join will ever find, which is how orphaned data accumulates.

One SQLite detail matters here. Plain SQLite does not enforce foreign keys unless each connection first runs PRAGMA foreign_keys = ON; the setting is off by default, and with it off the 999 review would be stored without complaint. The playground switches it on for you, which is why the refusal happens. If you use SQLite anywhere else, turn it on yourself.

Foreign keys also control what happens when the referenced row is deleted. ON DELETE CASCADE deletes the children with the parent; ON DELETE SET NULL orphans them deliberately; the default, NO ACTION, refuses the delete while children exist, as does RESTRICT if you ask for it by name. Choosing CASCADE without thinking is how people lose more data than they meant to.

CHECK: any condition you can write

CHECK takes an arbitrary boolean expression and refuses rows that fail it:

CREATE TABLE Shipments (
  shipment_id INTEGER PRIMARY KEY,
  quantity    INTEGER CHECK (quantity > 0),
  ship_status VARCHAR(10) CHECK (ship_status IN ('packed', 'sent', 'delivered'))
);

INSERT INTO Shipments VALUES (1, 12, 'packed');
INSERT INTO Shipments VALUES (2, NULL, 'sent');

SELECT * FROM Shipments;

A negative quantity is now impossible, and so is a status nobody planned for. Try (3, -5, 'packed') and SQLite answers CHECK constraint failed: quantity > 0. Shipment 2 got in with no quantity at all, though, and the next box explains why.

One caveat for MySQL specifically: CHECK was parsed but silently ignored before version 8.0.16. On an older MySQL the constraint is documentation, not enforcement.

DEFAULT: fill it in when nobody says

Not a rule so much as a convenience, and it removes a whole category of NULL:

CREATE TABLE Tickets (
  ticket_id INTEGER PRIMARY KEY,
  subject   VARCHAR(200) NOT NULL,
  priority  VARCHAR(10) DEFAULT 'normal',
  is_open   INTEGER DEFAULT 1
);

INSERT INTO Tickets (ticket_id, subject) VALUES (1, 'Cannot log in');

SELECT * FROM Tickets;

Two columns were never mentioned in the insert and both came back filled. Note that DEFAULT applies when a column is omitted, not when NULL is passed explicitly — INSERT ... VALUES (2, 'x', NULL, NULL) stores NULLs, which is a distinction that surprises people debugging an ORM.

Adding and naming constraints

Constraints can be added to an existing table with ALTER TABLE, and giving them names is worth the extra words:

-- PostgreSQL, MySQL, SQL Server and Oracle
ALTER TABLE Products ADD CONSTRAINT chk_price_positive CHECK (unit_price > 0);

SQLite is the exception: its ALTER TABLE can add a column but not a constraint, so there the route is to create a new table with the constraint and copy the rows across.

An unnamed constraint gets a name the database makes up — in MySQL the table name followed by _chk_1, in Oracle SYS_C followed by a number — and that is what appears in the error message your users see and what you have to reference to drop it later. A name like chk_price_positive turns a violation from a puzzle into a sentence.

Why in the database and not in the application?

• There is never only one writer. The web app validates; the nightly import does not. The admin script does not. A constraint covers all of them at once.
• Race conditions. Two requests that both check “is this email taken?” and then both insert will both succeed. Only a unique constraint actually prevents it.
• They document the rules. The schema states what is true about the data, in a form that cannot drift from reality.
• The planner uses them. Knowing a column is unique or not null lets the optimiser choose better plans.
• Bad data is expensive. Rejecting one row at insert time costs nothing. Finding and repairing a million rows later costs a week.

Application-level validation is still worth having — it gives better error messages and catches mistakes before a round trip. It is a nicety on top of the guarantee, not a replacement for it.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• Who reports to nobody?Medium
• Every department, even the quiet onesHard
• Customers still waitingMedium

See all 42 exercises →

Frequently asked questions

Related reading

• Primary key vs foreign key
• Database normalization: 1NF, 2NF and 3NF
• SQL commands: DDL, DML, DCL and TCL
• Find and delete duplicate rows
• The WHERE clause and the NULL traps