SQL Constraints Explained Simply: NOT NULL, UNIQUE, CHECK and DEFAULT

Your application probably checks the data people type in. The email field must not be empty, the age must be a positive number, the username must not already be taken. That’s good. But what happens when someone imports a spreadsheet directly, a second app writes to the same table, or a developer runs a quick fix by hand? Application checks don’t help then.

Constraints are rules you put on the table itself. The database checks them on every insert and update, no matter where the data comes from. They’re your last line of defence for clean data, and they cost almost nothing to add.

Quick answer: what are constraints in SQL?
Constraints are rules on a table’s columns that the database enforces on every insert and update. There are six main types: NOT NULL (a value is required), UNIQUE (no duplicates), PRIMARY KEY (identifies each row), FOREIGN KEY (the value must exist in another table), CHECK (a condition must be true) and DEFAULT (a value is filled in automatically).

Diagram showing SQL constraints accepting a valid row and rejecting invalid rows

The Main Types of Constraint

ConstraintWhat it guaranteesExample
NOT NULLThe column always has a valueEvery customer must have an email
UNIQUENo two rows share the same valueTwo customers can’t use the same email
CHECKA value meets a condition you defineAge must be 0 or more
DEFAULTA value is filled in when none is givenNew accounts start as ‘active’
PRIMARY KEYUnique and not null, identifies each rowcustomer_id
FOREIGN KEYA value must exist in another tableAn order’s customer_id must be a real customer

Primary and foreign keys have their own article (Primary Keys vs. Foreign Keys), so here I’ll focus on the other four.

A Table With Constraints

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) NOT NULL UNIQUE,
    age         INT CHECK (age >= 0 AND age <= 120),
    status      VARCHAR(10) NOT NULL DEFAULT 'active',
    created_at  DATE NOT NULL DEFAULT CURRENT_DATE
);

This works as written in PostgreSQL. In MySQL (8.0.13 and later) wrap the default in brackets: DEFAULT (CURRENT_DATE). In SQL Server use DEFAULT CAST(GETDATE() AS DATE).

Every rule about this table is now written right next to the column it protects. Let’s look at each one.

NOT NULL: This Value Is Required

By default, columns accept NULL (no value). NOT NULL turns that off:

INSERT INTO customers (customer_id, name, email)
VALUES (1, 'Lina', NULL);
-- Error: column "email" cannot be NULL

My advice: make columns NOT NULL unless you have a real reason to allow missing values. Fewer nulls means fewer surprises in your queries. (Nulls behave strangely, which I explain in NULL in SQL Explained.)

UNIQUE: No Duplicates Allowed

UNIQUE stops two rows from having the same value in a column:

INSERT INTO customers (customer_id, name, email) VALUES (1, 'Sara', 'sara@mail.com');
INSERT INTO customers (customer_id, name, email) VALUES (2, 'Omar', 'sara@mail.com');
-- Error: duplicate value violates unique constraint

UNIQUE vs. PRIMARY KEY

PRIMARY KEYUNIQUE
How many per tableOneAs many as you need
Allows NULL?NeverYes (how many nulls depends on the database)
PurposeIdentify the rowPrevent duplicate business values like emails or codes

On nulls: PostgreSQL and MySQL allow many NULL values in a unique column, while SQL Server allows only one. If you need “unique when filled in” in SQL Server, use a filtered unique index.

Unique across several columns

Sometimes the combination must be unique, not each column on its own. A student can enrol in many courses, but not the same course twice:

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    CONSTRAINT uq_student_course UNIQUE (student_id, course_id)
);

Behind the scenes, a unique constraint is enforced with an index, so it also speeds up searches on those columns.

NOT NULL and UNIQUE together

UNIQUE on its own still lets rows leave the column empty. When a value must be both filled in and different for every row, like a username or an employee code, combine the two:

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    employee_code VARCHAR(20) NOT NULL UNIQUE,
    national_id   VARCHAR(20) UNIQUE          -- optional, but no duplicates
);

NOT NULL UNIQUE behaves almost like a second primary key. It’s the usual choice for a “natural” business code when the table already uses a numeric ID as its primary key.

CHECK: Your Own Business Rules

CHECK lets you write any condition that must be true for every row:

ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price > 0);

ALTER TABLE products
ADD CONSTRAINT chk_discount_range CHECK (discount BETWEEN 0 AND 50);

ALTER TABLE bookings
ADD CONSTRAINT chk_dates CHECK (check_out > check_in);

The last one compares two columns in the same row, which is perfect for date ranges. One thing to remember: a CHECK passes when the result is unknown, so CHECK (age >= 0) still accepts NULL. Add NOT NULL if the value is required.

Also note that MySQL only started enforcing CHECK constraints in version 8.0.16. Older versions accepted the syntax and silently ignored it.

DEFAULT: Sensible Starting Values

DEFAULT fills in a value when an insert doesn’t provide one:

INSERT INTO customers (customer_id, name, email)
VALUES (3, 'Huda', 'huda@mail.com');

-- status is 'active' and created_at is today's date, automatically

Defaults are great for status flags, creation dates and counters that start at zero. Strictly speaking, DEFAULT isn’t a rule that rejects data, but it’s defined in the same place and it keeps inserts short and consistent.

Name Your Constraints

If you don’t name a constraint, the database invents a name like CK__customers__age__3B75D760. That’s painful when it shows up in an error message or when you need to drop it. Give each one a clear name:

CREATE TABLE customers (
    customer_id INT,
    email       VARCHAR(150) NOT NULL,
    age         INT,
    CONSTRAINT pk_customers       PRIMARY KEY (customer_id),
    CONSTRAINT uq_customers_email UNIQUE (email),
    CONSTRAINT chk_customers_age  CHECK (age >= 0)
);

-- Removing one later
ALTER TABLE customers DROP CONSTRAINT chk_customers_age;

Adding Constraints to an Existing Table

You can add constraints later with ALTER TABLE, but the existing data has to pass the rule first. If there are already duplicate emails, adding UNIQUE fails. Find the problem rows before you try:

-- Which emails are duplicated?
SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Fix or merge those rows, then add the constraint.

Constraints in a Real Business Database

The examples above use a small customers table. Here is how the same rules protect an accounting or ERP database, where bad data costs real money.

Journal lines: one side only

In a general ledger every line is either a debit or a credit, never both and never neither:

ALTER TABLE gl_journal_lines
ADD CONSTRAINT chk_gl_lines_one_side
CHECK ( (debit > 0 AND credit = 0) OR (credit > 0 AND debit = 0) );

Without this rule, a bad import line with both values filled in still saves, and the trial balance stops balancing. Someone then spends a day finding the cause.

Invoice numbers: unique per branch and year

Invoice numbers often restart each year and differ between branches, so the number alone isn’t unique, but the combination is:

ALTER TABLE invoices
ADD CONSTRAINT uq_invoices_branch_year_no
UNIQUE (branch_id, fiscal_year, invoice_no);

“Unique when filled in” in SQL Server

A tax registration number may be optional, but two customers must never share one. A UNIQUE constraint in SQL Server allows only one NULL, so use a filtered unique index instead:

CREATE UNIQUE INDEX uq_customers_tax_no
ON customers (tax_no)
WHERE tax_no IS NOT NULL;

The NOCHECK trap

During a big data migration, people often switch constraints off to speed up loading and then switch them back on:

ALTER TABLE invoices NOCHECK CONSTRAINT ALL;
-- ... bulk load ...
ALTER TABLE invoices CHECK CONSTRAINT ALL;   -- looks fine, but isn't

The second line protects new rows only. SQL Server doesn’t re-check the rows already in the table, so it marks those constraints as not trusted. Bad rows from the load stay in place, and the query optimizer stops using those constraints to simplify plans. Re-enable them with the double CHECK, which validates every row:

ALTER TABLE invoices WITH CHECK CHECK CONSTRAINT ALL;

And find any that are still untrusted:

SELECT OBJECT_NAME(parent_object_id) AS table_name, name, type_desc
FROM sys.check_constraints WHERE is_not_trusted = 1
UNION ALL
SELECT OBJECT_NAME(parent_object_id), name, type_desc
FROM sys.foreign_keys WHERE is_not_trusted = 1;

Turning constraint errors into friendly messages

Clear constraint names pay off in the application. In a Delphi or Laravel app you can catch the database error, look for the constraint name in the message, and show something the user understands:

Constraint name in the errorMessage to the user
uq_invoices_branch_year_no“This invoice number already exists for this branch and year.”
chk_gl_lines_one_side“Enter either a debit or a credit on each line, not both.”

That’s only practical when the constraint is called uq_invoices_branch_year_no and not UQ__invoices__3213E83F.

How to List the Constraints on a Table

When you inherit a database, the first question is often “what rules does this table already have?” Each database has its own way to show them.

SQL Server

EXEC sp_helpconstraint 'dbo.customers';

-- or as a query
SELECT name, type_desc
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('dbo.customers')
  AND type IN ('PK', 'UQ', 'F', 'C', 'D');

MySQL

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = DATABASE()
  AND table_name = 'customers';

PostgreSQL

SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'customers'::regclass;

NOT NULL usually doesn’t appear in these lists, because most databases store it as a property of the column. Check is_nullable in information_schema.columns instead. For more syntax differences between the three databases, see the SQL Server vs MySQL vs PostgreSQL cheat sheet.

Common Constraint Errors and How to Fix Them

Constraint errors look scary, but the message almost always tells you which rule was broken. Here are the ones you’ll meet most often:

Error message (shortened)DatabaseWhat it means
Violation of UNIQUE KEY constraint … Cannot insert duplicate keySQL Server (Msg 2627)The value already exists in a UNIQUE or primary key column
Duplicate entry ‘…’ for key ‘…’MySQL (1062)Same as above
duplicate key value violates unique constraintPostgreSQL (23505)Same as above
The INSERT statement conflicted with the CHECK constraintSQL Server (Msg 547)A CHECK rule returned false. The same message number is used for foreign key conflicts
Check constraint ‘…’ is violatedMySQL (3819)A CHECK rule returned false
new row … violates check constraintPostgreSQL (23514)A CHECK rule returned false
there is no unique constraint matching given keys for referenced tablePostgreSQL (42830)A foreign key points at columns that aren’t a primary key or UNIQUE

For duplicate and check errors, look at the row you were saving, not the table: the constraint name in the message tells you which column broke the rule. If the duplicates are already in the table, my guide to finding and deleting duplicate rows shows how to clean them up.

Fixing “there is no unique constraint matching given keys”

PostgreSQL raises this when you create a foreign key that points at a column (or set of columns) with no primary key or unique constraint. A foreign key must reference something guaranteed to be unique:

CREATE TABLE branches (
    branch_code VARCHAR(10) NOT NULL   -- no PRIMARY KEY or UNIQUE
);

CREATE TABLE invoices (
    invoice_id  INT PRIMARY KEY,
    branch_code VARCHAR(10) REFERENCES branches (branch_code)
);
-- ERROR: there is no unique constraint matching given keys
--        for referenced table "branches"

-- Fix: make the referenced column unique first
ALTER TABLE branches
ADD CONSTRAINT uq_branches_code UNIQUE (branch_code);

With a multi-column key, the foreign key must list exactly the same columns as the unique constraint. SQL Server and MySQL have the same rule, they just word the error differently. See Primary Keys vs. Foreign Keys for how the two work together.

Constraints vs. Application Checks

People sometimes ask whether they need both. I always say yes:

  • Application checks give users friendly messages straight away (“Please enter a valid email”).
  • Database constraints guarantee the rule holds for every single row, from every source, forever.

Years of working with business systems have taught me that any rule only enforced in the application will be broken eventually by an import, a script or a second app.

Frequently Asked Questions

What are the types of constraints in SQL?

The six main ones are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT. Some databases add others, such as PostgreSQL’s EXCLUDE constraint, but these six are the ones you’ll use every day.

What’s the difference between a column-level and a table-level constraint?

A column-level constraint is written next to one column (email VARCHAR(150) UNIQUE). A table-level constraint is written separately and can cover several columns (CONSTRAINT uq_student_course UNIQUE (student_id, course_id)). They do the same job. Use the table-level form when the rule involves more than one column or when you want to give it a clear name.

Do constraints slow down inserts?

Very slightly, because the database has to check each rule. UNIQUE and primary keys also maintain an index. For normal applications the cost is tiny compared with the cost of cleaning bad data later, and trusted constraints can even help the optimizer build faster query plans.

Can I turn a constraint off temporarily?

Yes. In SQL Server use NOCHECK CONSTRAINT, but read the NOCHECK trap above before you do: switch it back on WITH CHECK or the database stops trusting it. In MySQL you can set FOREIGN_KEY_CHECKS = 0 for foreign keys during a load. In most cases it’s safer to fix the data than to switch a rule off.

Conclusion

Constraints are rules the database enforces for you: NOT NULL for required values, UNIQUE for no duplicates, CHECK for your own conditions and DEFAULT for sensible starting values. Add them when you create a table, give them clear names, and you’ll spend much less time cleaning up bad data later.

If you’re designing a new database, pair this with our guides to data types and normalization.

Related articles

Scroll to Top