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 the error each one produces on the engine running in your browser, and ends with which to reach for.

All six, in one table

CREATE TABLE Invoices (
  invoice_id  INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      NUMBER CHECK (amount > 0),
  status      STRING 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 INT PRIMARY KEY,
  full_name  STRING NOT NULL,
  phone      STRING
);

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 the engine answers Wrong NULL value in NOT NULL column full_name — 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     STRING PRIMARY KEY,
  joined_on STRING NOT NULL
);

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

SELECT * FROM Members;

Insert that same email twice and the engine refuses with Cannot insert record, because it already exists in primary key index. That refusal is the point: it is what stops duplicate rows from ever existing, rather than leaving you to find and delete them later.

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 usually hold NULL, because NULL is not equal to anything including another NULL. Most databases therefore allow one NULL (SQL Server) or several (PostgreSQL, MySQL) in a unique column, which is a genuine portability difference worth checking if it matters.

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  INT PRIMARY KEY,
  product_id INT NOT NULL,
  stars      INT 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 the engine answers Foreign key "999" not found in table "Products". 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.

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, RESTRICT, refuses the delete while children exist. 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 INT PRIMARY KEY,
  quantity    INT CHECK (quantity > 0),
  ship_status STRING CHECK (ship_status IN ('packed', 'sent', 'delivered'))
);

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

SELECT * FROM Shipments;

A negative quantity is now impossible, and so is a status nobody planned for. Try (2, -5, 'packed') and the engine answers Violation of CHECK constraint.

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 INT PRIMARY KEY,
  subject   STRING NOT NULL,
  priority  STRING DEFAULT 'normal',
  is_open   INT 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:

ALTER TABLE Products ADD CONSTRAINT chk_price_positive CHECK (unit_price > 0);

An unnamed constraint gets a generated name like products_check1, which 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