SQL Constraints: DEFAULT, CHECK and UNIQUE

Q: Does CHECK imply NOT NULL?

No. CHECK (amount >= 0) is unknown, not false, when amount is NULL, and unknown passes the check. Add a presence constraint, NOT NULL, when a value is required.

Q: Can a CHECK constraint compare two columns?

Yes, as long as both are in the same row: CHECK (end_date >= start_date) or CHECK (discount <= price). A CHECK can't look at other rows or tables, so rules such as "total stock across warehouses" need a trigger or application logic.

Q: How do I give a constraint a name?

Write CONSTRAINT name before it: CONSTRAINT amount_not_negative CHECK (amount >= 0). Named constraints produce clearer error messages and are easier to drop or change later, because you don't have to look up a generated name.

DEFAULT supplies an omitted value, CHECK restricts allowed row values, and UNIQUE prevents duplicate key values according to the engine’s rules.

These rules protect a table even when data arrives from a script rather than a form. They serve different purposes: a default is a convenience value, a check expresses validity, and uniqueness establishes an identity or business restriction.

This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.

Set up the practice database

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

SQL

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

How DEFAULT, CHECK and UNIQUE constraints works

A DEFAULT is used when an INSERT omits the column or explicitly requests the default through supported syntax. It does not generally replace an explicit NULL. A status DEFAULT 'pending' gives new records an initial status without every caller spelling it out, but a caller can still submit another permitted value. Pair a default with NOT NULL if the field must always be present, and with a CHECK if only particular labels are allowed.

A CHECK accepts values whose condition is not false; in these common engines an unknown result can pass. CHECK(amount >= 0) therefore does not by itself prohibit a NULL amount. Add NOT NULL if a known amount is required. A condition such as CHECK(status IN ('paid','pending')) restricts status labels, but its text comparison still follows the engine’s collation. Constraints should match a documented business rule rather than an assumption based on the sample records.

UNIQUE on one column or a combination

UNIQUE prevents duplicate values for a column or combination of columns. UNIQUE(customer_id,created_on) enforces one row for each exact pair, not uniqueness of either component alone. This can be wrong for sales because a customer may legitimately purchase twice on one date. Choose a key from actual requirements. A primary key combines identity with non-NULL requirements, while a UNIQUE rule can allow missing components according to dialect-specific treatment.

NULL handling for UNIQUE differs. PostgreSQL, MySQL and SQLite normally permit multiple NULLs in a single-column unique field; PostgreSQL also has NULLS NOT DISTINCT options. SQL Server’s ordinary single-column unique constraint generally permits only one NULL, and filtered indexes can implement a different desired policy. Existing data must satisfy a new constraint before it can be enforced in normal validated form. Find and resolve violations first, then install the protection. Give important constraints names in real schemas so errors and migrations identify the exact business rule.

MySQL, PostgreSQL, SQL Server and SQLite

The displayed constraints work in current PostgreSQL, MySQL, SQL Server and SQLite. Older MySQL versions before enforced CHECK support could parse but ignore CHECK clauses, so verify supported versions. NULL treatment in UNIQUE is not uniform. SQLite foreign-key enablement is a separate issue and does not disable ordinary CHECK or UNIQUE enforcement.

Check the engine-specific syntax and version requirements in PostgreSQL constraints.

Practice task and worked solution

Create a small label table whose code is both required and unique, whose quantity defaults to one, and whose quantity must be positive. Insert a code while omitting quantity.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SQL

CREATE TABLE label_rules (
 id INTEGER PRIMARY KEY,code VARCHAR(20) NOT NULL UNIQUE,
 quantity INTEGER NOT NULL DEFAULT 1 CHECK(quantity>0)
);
INSERT INTO label_rules (id,code) VALUES (1,'LABEL-1');
SELECT id,code,quantity FROM label_rules;

Output

id | code | quantity
1 | LABEL-1 | 1

Omitting quantity invokes the default one, which also satisfies the positive check. NOT NULL prevents missing quantity, while UNIQUE and NOT NULL together protect the code from duplicate or absent labels. Each constraint contributes a separate rule rather than duplicating the others.

In checked_sales, attempt a new id with the same code and expect a uniqueness error. Attempt a negative amount with a new code and expect the check to reject it. Omit status to observe the default, then explicitly supply NULL to observe NOT NULL rejection. Use a separate nullable CHECK-only field to see why unknown is not the same as false.

SQL DEFAULT, CHECK and UNIQUE Constraints with Examples Syntax

DEFAULT, CHECK and UNIQUE constraints

CREATE TABLE checked_sales (
 id INTEGER PRIMARY KEY,
 code VARCHAR(20) NOT NULL UNIQUE,
 amount DECIMAL(10,2) NOT NULL CHECK(amount>=0),
 status VARCHAR(20) NOT NULL DEFAULT 'pending'
);
INSERT INTO checked_sales (id,code,amount) VALUES (1,'A-1',12);
SELECT id,code,amount,status FROM checked_sales;

SQL DEFAULT, CHECK and UNIQUE Constraints with Examples Summary

DEFAULT fills a column when an INSERT leaves it out; an explicit NULL usually stays NULL.
CHECK (condition) rejects rows where the condition is false, but an unknown result caused by NULL passes.
UNIQUE prevents duplicate values; UNIQUE (a, b) prevents duplicate pairs, not duplicates of each column alone.
Add NOT NULL whenever presence is part of the rule.
Constraints protect data from every writer, so validate in the application for friendly messages as well.

SQL DEFAULT, CHECK and UNIQUE Constraints with Examples Examples

Each example shows the code, the output it produces and what each part does.

Example 1

A default status and a checked amount

The insert omits status, so its default supplies pending. The amount is known and nonnegative, satisfying both rules. The code has a unique business label, and the readback shows the actual stored default rather than only a client success message.

SQL

CREATE TABLE checked_sales (
 id INTEGER PRIMARY KEY,
 code VARCHAR(20) NOT NULL UNIQUE,
 amount DECIMAL(10,2) NOT NULL CHECK(amount>=0),
 status VARCHAR(20) NOT NULL DEFAULT 'pending'
);
INSERT INTO checked_sales (id,code,amount) VALUES (1,'A-1',12);
SELECT id,code,amount,status FROM checked_sales;

Output

id | code | amount | status
1 | A-1 | 12 | pending

Common SQL DEFAULT, CHECK and UNIQUE Constraints with Examples Mistakes

Expecting CHECK or DEFAULT to block NULL

CHECK(amount >= 0) alone still admits NULL because its condition becomes unknown rather than false. Another mistake is assuming a default replaces explicit NULL: it usually applies to omitted values instead. Use NOT NULL when presence is part of the requirement, and test both omitted and explicitly missing input.

SQL DEFAULT, CHECK and UNIQUE Constraints with Examples in Real Projects

Protecting an order intake table

An order intake table can require a unique external reference, a nonnegative known amount and a default pending state. The application should still validate for helpful messages, while database constraints defend the same requirements against alternate writers and concurrent requests.

Keep constraint errors identifiable by the business rule they enforce. A unique external reference prevents duplicate requests, a nonnegative amount check limits the domain, and NOT NULL requires presence. Test each separately before depending on their combination. Application validation can improve messages, while the database rules protect writes from every entry point.

SQL DEFAULT, CHECK and UNIQUE Constraints with Examples FAQ

Does CHECK imply NOT NULL?

No. CHECK (amount >= 0) is unknown, not false, when amount is NULL, and unknown passes the check. Add a presence constraint, NOT NULL, when a value is required.

Is UNIQUE the same as a primary key?

They both enforce uniqueness, but primary keys also express row identity, require non-NULL components and are the usual target of foreign keys. A table has one primary key and may have several unique constraints for other candidate keys, such as email addresses.

Do defaults repair old records?

Adding a default primarily defines insertion behaviour for new rows. Whether existing rows receive the value depends on the migration and engine: adding a new column with a default usually fills it, but changing the default of an existing column leaves stored values unchanged. Backfill old rows explicitly with UPDATE if needed.

Can a CHECK constraint compare two columns?

Yes, as long as both are in the same row: CHECK (end_date >= start_date) or CHECK (discount <= price). A CHECK can't look at other rows or tables, so rules such as "total stock across warehouses" need a trigger or application logic.

How do I give a constraint a name?

Write CONSTRAINT name before it: CONSTRAINT amount_not_negative CHECK (amount >= 0). Named constraints produce clearer error messages and are easier to drop or change later, because you don't have to look up a generated name.

SQL DEFAULT, CHECK and UNIQUE Constraints with Examples Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Multiple choicemedium

What status is saved when checked_sales omits the status column during insertion?

SQL DEFAULT, CHECK and UNIQUE Constraints with Examples Quiz

2 questions to check what you learned.

When is the default normally used in this example?
- Whenever an explicit NULL is supplied, even with NOT NULL
- When the INSERT omits status
- Whenever a SELECT reads an existing row
Why add NOT NULL beside CHECK(amount>=0)?
- CHECK requires every amount to equal zero unless NOT NULL overrides it
- NOT NULL makes the check condition unnecessary because all known numbers are nonnegative
- The CHECK alone can accept an unknown amount