Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 42 of 49

Constraints: keeping bad data out with NOT NULL, UNIQUE, CHECK and DEFAULT

Use NOT NULL, UNIQUE, CHECK and DEFAULT to make the database reject bad rows, with real error messages from SQLite and notes on other databases.

15 minDesigning databasesUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonAdd NOT NULL, UNIQUE, CHECK and DEFAULT rules to a table and predict which inserts will be accepted or rejected.

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.

sql
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:

sql
INSERT INTO accounts (username) VALUES ('sam');
result
Error: NOT NULL constraint failed: accounts.email

The 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:

sql
INSERT INTO accounts (username, email) VALUES ('ria2', 'ria@example.com');
result
Error: UNIQUE constraint failed: accounts.email

A 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:

sql
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:

sql
INSERT INTO bookings VALUES (101, '2026-12-01', 'Chetan');
result
Error: UNIQUE constraint failed: bookings.room, bookings.day

There 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:

sql
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;
result
+----+--------+-------+------+-------+----------+
| 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:

sql
INSERT INTO subscribers (name, seats) VALUES ('Gita', NULL);
result
Error: NOT NULL constraint failed: subscribers.seats

Defaults 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:

sql
INSERT INTO subscribers (name, plan) VALUES ('Esha', 'gold');
result
Error: CHECK constraint failed: ck_subscribers_plan
sql
INSERT INTO subscribers (name, seats) VALUES ('Farah', 0);
result
Error: CHECK constraint failed: ck_subscribers_seats

Because 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:

sql
INSERT INTO accounts (username, email, age) VALUES ('kid', 'kid@example.com', 12);
result
Error: CHECK constraint failed: age >= 18

There 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:

sql
INSERT INTO accounts (username, email) VALUES ('noage', 'noage@example.com');

SELECT username, age FROM accounts;
result
+----------+------+
| 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 NULL in a unique constraint, unlike the others.
  • Adding rules later. ALTER TABLE ... ADD CONSTRAINT works 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 NULL unless you say otherwise, so required columns need an explicit NOT NULL.
  • Expecting DEFAULT to repair a NULL. It only fills in a column that is missing from the insert.
  • Forgetting that CHECK lets NULL through. Pair it with NOT NULL when 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
sql
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;
result
+----+-----------+-------------+------+
| id | code      | percent_off | used |
+----+-----------+-------------+------+
|  1 | WELCOME10 |          10 |    0 |
+----+-----------+-------------+------+
sql
INSERT INTO coupons (code, percent_off) VALUES ('BIGSALE', 95);
result
Error: CHECK constraint failed: ck_coupons_percent

Key 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.

Check yourself

1. A column is declared UNIQUE and allows NULL. In SQLite, PostgreSQL and MySQL, how many rows may hold NULL in it?
2. A column has DEFAULT 1 and NOT NULL. What does INSERT ... VALUES (NULL) for that column do?
3. A table has CHECK (age >= 18). You insert a row whose age is NULL. What happens?
4. Why give a constraint a name?