Let the database enforce the rules
Your application can validate input, but it is not the only thing that writes to a database. Import scripts, other services and people at a SQL prompt all do too. A constraint is a rule attached to the table itself. The database checks it on every insert and update, so the data stays correct whoever sends it.
After this lesson you will be able to add four kinds of rule, and to predict which statements a table will accept. As in the previous lesson, the tables are new ones created in a scratch SQLite 3.54 database. SQLite's error wording is its own, and other databases word the same failures differently, but the idea of each rule is the same everywhere.
NOT NULL: a value is required
A column marked NOT NULL must hold a value in every row. Leaving it out of an insert, or setting it to NULL, is an error unless the column has a default. Use it for everything that is genuinely required, and make it your habit rather than the exception.
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
username VARCHAR(30) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
age INTEGER CHECK (age >= 18),
status VARCHAR(10) NOT NULL DEFAULT 'active'
);
INSERT INTO accounts (username, email, age) VALUES ('ria', 'ria@example.com', 30);This one table uses all four rules, and the sections below take them apart. The first row is valid, so it is accepted. Now try to insert a row with no email:
INSERT INTO accounts (username) VALUES ('sam');Error: NOT NULL constraint failed: accounts.emailThe shell adds its own prefix, such as Error near line 13:, and what follows is the message from SQLite. MySQL says that the field has no default value, PostgreSQL says that a null value violates a not-null constraint, and SQL Server reports that it cannot insert NULL into the column. The meaning is identical.
UNIQUE: no duplicates
UNIQUE forbids two rows from having the same value in a column, or the same combination across several columns. It suits natural identifiers such as email addresses and phone numbers. The email column above was declared NOT NULL UNIQUE:
INSERT INTO accounts (username, email) VALUES ('ria2', 'ria@example.com');Error: UNIQUE constraint failed: accounts.emailA unique constraint over several columns means the combination must be unique, not each column. Here is a table of hotel bookings where a room can be booked once per day:
CREATE TABLE bookings (
room INTEGER NOT NULL,
day DATE NOT NULL,
guest VARCHAR(30) NOT NULL,
UNIQUE (room, day)
);
INSERT INTO bookings VALUES (101, '2026-12-01', 'Anil');
INSERT INTO bookings VALUES (101, '2026-12-02', 'Anil');
INSERT INTO bookings VALUES (102, '2026-12-01', 'Bina');Room 101 appears twice, and the day appears twice, but never the same pair. All three inserts succeed. A fourth that repeats room 101 on the first of December does not:
INSERT INTO bookings VALUES (101, '2026-12-01', 'Chetan');Error: UNIQUE constraint failed: bookings.room, bookings.dayThere is a subtle point about NULL. A unique column that also allows NULL can hold many rows with NULL, because unknown values are not equal to each other. That is how SQLite, PostgreSQL and MySQL behave. SQL Server is the well-known exception, since it allows only a single NULL in a unique constraint.
DEFAULT: a value when none is given
DEFAULT supplies a value when an insert leaves the column out. The status column above defaults to 'active'. Let me create one more table, with a nullable phone number and a few defaults, to show the behaviour clearly:
CREATE TABLE subscribers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
phone VARCHAR(20) UNIQUE,
plan VARCHAR(10) NOT NULL DEFAULT 'free',
seats INTEGER NOT NULL DEFAULT 1,
discount DECIMAL(4,2) NOT NULL DEFAULT 0,
CONSTRAINT ck_subscribers_plan CHECK (plan IN ('free', 'pro', 'team')),
CONSTRAINT ck_subscribers_seats CHECK (seats >= 1 AND seats <= 50)
);
INSERT INTO subscribers (name, phone, plan) VALUES ('Asha', '98200', 'pro');
INSERT INTO subscribers (name) VALUES ('Bilal');
INSERT INTO subscribers (name) VALUES ('Chitra');
SELECT id, name, phone, plan, seats, discount FROM subscribers;+----+--------+-------+------+-------+----------+
| id | name | phone | plan | seats | discount |
+----+--------+-------+------+-------+----------+
| 1 | Asha | 98200 | pro | 1 | 0 |
| 2 | Bilal | NULL | free | 1 | 0 |
| 3 | Chitra | NULL | free | 1 | 0 |
+----+--------+-------+------+-------+----------+Bilal and Chitra got the plan, seats and discount defaults. Both also have NULL phones, and both rows were accepted even though phone is UNIQUE, which confirms the rule about multiple NULL values.
A default applies only when the column is left out. If you name the column and give it NULL, you have supplied a value, and NOT NULL rejects it:
INSERT INTO subscribers (name, seats) VALUES ('Gita', NULL);Error: NOT NULL constraint failed: subscribers.seatsDefaults can also be functions, such as the current date. The spelling varies: CURRENT_DATE works in SQLite, PostgreSQL and MySQL, but SQL Server uses GETDATE() instead.
CHECK: any condition you can write
CHECK (condition) rejects any row for which the condition is false. It is the most flexible constraint, and the table above uses two named ones. One limits plan to a list, and the other limits seats to a range:
INSERT INTO subscribers (name, plan) VALUES ('Esha', 'gold');Error: CHECK constraint failed: ck_subscribers_planINSERT INTO subscribers (name, seats) VALUES ('Farah', 0);Error: CHECK constraint failed: ck_subscribers_seatsBecause the constraints were named, the messages say exactly which rule was broken. Look at the accounts table, where CHECK (age >= 18) was not named. The error for an underage row shows the condition instead, which is readable here but would be much worse for a long one:
INSERT INTO accounts (username, email, age) VALUES ('kid', 'kid@example.com', 12);Error: CHECK constraint failed: age >= 18There is a trap. A CHECK only fails when its condition is false. If age is NULL, the condition age >= 18 is unknown, and unknown passes. So this insert is accepted:
INSERT INTO accounts (username, email) VALUES ('noage', 'noage@example.com');
SELECT username, age FROM accounts;+----------+------+
| username | age |
+----------+------+
| ria | 30 |
| noage | NULL |
+----------+------+If an age must be present, combine the rules: age INTEGER NOT NULL CHECK (age >= 18).
Naming constraints
Write CONSTRAINT name in front of a rule to name it, as the subscribers table did. Names appear in error messages in most databases, and they are how you refer to a rule when you drop or replace it. A common style is a short prefix, the table and the purpose: uq_accounts_email, ck_subscribers_plan.
Not every database reports names the same way. In the output above SQLite named the CHECK constraints but reported unique failures by column. Other databases show the constraint name for both.
Differences between databases
- CHECK in MySQL. Older MySQL versions accepted CHECK and ignored it. It is enforced from MySQL 8.0.16.
- NULL in unique columns. SQL Server allows only one
NULLin a unique constraint, unlike the others. - Adding rules later.
ALTER TABLE ... ADD CONSTRAINTworks in PostgreSQL, MySQL and SQL Server. SQLite cannot add most constraints to an existing table, which the ALTER lesson explains.
The MySQL constraints lesson goes further into MySQL's behaviour.
Common mistakes
- Leaving columns nullable by accident. Columns allow
NULLunless you say otherwise, so required columns need an explicitNOT NULL. - Expecting
DEFAULTto repair aNULL. It only fills in a column that is missing from the insert. - Forgetting that CHECK lets
NULLthrough. Pair it withNOT NULLwhen the value is required. - Relying only on the application. A second program, or an import script, will skip your validation, and the table then holds bad data.
- Unnamed constraints. They work, but the generated names and messages are harder to read and to drop.
Exercise
Create a table coupons with an integer id primary key, a required unique code, a percent_off integer that must be between 1 and 90, and a used column that defaults to 0. Insert one valid coupon, then try one with a percent_off of 95.
Show one possible answer
CREATE TABLE coupons (
id INTEGER PRIMARY KEY,
code VARCHAR(20) NOT NULL UNIQUE,
percent_off INTEGER NOT NULL,
used INTEGER NOT NULL DEFAULT 0,
CONSTRAINT ck_coupons_percent CHECK (percent_off BETWEEN 1 AND 90)
);
INSERT INTO coupons (code, percent_off) VALUES ('WELCOME10', 10);
SELECT * FROM coupons;+----+-----------+-------------+------+
| id | code | percent_off | used |
+----+-----------+-------------+------+
| 1 | WELCOME10 | 10 | 0 |
+----+-----------+-------------+------+INSERT INTO coupons (code, percent_off) VALUES ('BIGSALE', 95);Error: CHECK constraint failed: ck_coupons_percentKey points
- A constraint is a rule stored with the table. The database checks it on every insert and update, whichever program sends the statement.
- NOT NULL demands a value, UNIQUE forbids duplicates, CHECK rejects rows where a condition is false, and DEFAULT fills in a value when the column is left out.
- A CHECK that evaluates to NULL passes, and an explicit NULL does not trigger a DEFAULT. Both surprise beginners.
- Name your constraints, so that error messages and later changes point at something readable.