Constraints in SQL

Constraints are rules you attach to columns or tables that the database enforces automatically. They are your first and most reliable line of defence against bad data: a constraint rejects an invalid INSERT or UPDATE before it ever lands. Rather than hoping your application validates everything correctly, you let the database guarantee it.

There are six constraints you will use constantly: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

The six core constraints

-- PostgreSQL / MySQL compatible

CREATE TABLE student (
    -- PRIMARY KEY: unique + not null; the row's official identifier
    student_id   INTEGER PRIMARY KEY,

    -- NOT NULL: this column can never be empty
    full_name    VARCHAR(120) NOT NULL,

    -- UNIQUE: no two students may share an email
    email        VARCHAR(160) UNIQUE,

    -- CHECK: enforce a business rule on the value
    age          INTEGER CHECK (age >= 0 AND age <= 120),

    -- DEFAULT: value used when the INSERT does not supply one
    status       VARCHAR(20) NOT NULL DEFAULT 'active',

    city         VARCHAR(80)
);

CREATE TABLE enrolment (
    enrolment_id INTEGER PRIMARY KEY,

    -- FOREIGN KEY: must reference an existing student
    student_id   INTEGER NOT NULL REFERENCES student(student_id),

    course       VARCHAR(150) NOT NULL
);

Let us see what each one prevents.

NOT NULL

Stops a column from being left empty. INSERT INTO student (student_id) VALUES (1); fails because full_name is NOT NULL and was not supplied. Use it for any column that must always have a value.

UNIQUE

Guarantees no two rows share the same value in that column. A second student with the same email is rejected. Note: UNIQUE columns typically allow one NULL (PostgreSQL allows multiple NULLs; behaviour for multiple NULLs is one of the subtler standard-vs-vendor areas — most databases treat NULLs as distinct).

PRIMARY KEY

Combines UNIQUE and NOT NULL and designates the row's identifier. A table has exactly one. See keys in DBMS for the full key family.

FOREIGN KEY

Enforces referential integrity: a value must match an existing row in the referenced table (or be NULL). You cannot enrol student 99 if no student 99 exists. This is the constraint that keeps your tables consistently linked.

CHECK

Enforces an arbitrary boolean rule on the data. CHECK (age >= 0 AND age <= 120) rejects negative or impossible ages. You can write rich conditions:

-- A CHECK spanning multiple columns
CREATE TABLE fee_payment (
    payment_id  INTEGER PRIMARY KEY,
    amount      NUMERIC(10,2) NOT NULL,
    discount    NUMERIC(10,2) NOT NULL DEFAULT 0,
    -- the discount can never exceed the amount
    CHECK (discount <= amount)
);

Vendor note: PostgreSQL has enforced CHECK constraints for a long time. MySQL ignored CHECK constraints until version 8.0.16, where they became actually enforced. If you target older MySQL, do not rely on CHECK alone.

DEFAULT

Supplies a value when none is given. status defaults to 'active', so you can insert a student without mentioning status. Technically DEFAULT is a column default rather than a "constraint" in the strict sense, but it works alongside them to keep data complete.

Naming constraints

Giving constraints names makes errors readable and lets you drop them later:

CREATE TABLE student (
    student_id INTEGER,
    email      VARCHAR(160),
    age        INTEGER,
    CONSTRAINT pk_student      PRIMARY KEY (student_id),
    CONSTRAINT uq_student_email UNIQUE (email),
    CONSTRAINT chk_age          CHECK (age >= 0)
);

Now a violation reports chk_age instead of a cryptic auto-generated name — far easier to debug.

Adding and dropping constraints

Constraints can be added to existing tables:

-- Add a constraint after the table exists
ALTER TABLE student
ADD CONSTRAINT chk_age CHECK (age >= 0);

-- Remove it
ALTER TABLE student
DROP CONSTRAINT chk_age;       -- PostgreSQL
-- MySQL syntax differs slightly for some constraint types

Adding a constraint to a table that already contains violating rows will fail — the database checks existing data first. Clean the data, then add the rule.

Common mistakes

• Relying only on application validation. App code has bugs and bypass paths (scripts, admin tools). The database constraint is the guarantee that always holds.
• Assuming CHECK works on old MySQL. Before 8.0.16, MySQL parsed but ignored CHECK. Verify your version.
• Forgetting that UNIQUE allows NULLs. A UNIQUE column is not the same as "always present and distinct" — add NOT NULL if you need that.
• Not naming constraints. Auto-generated names make errors and migrations harder. Name the ones you may need to reference.
• Adding a constraint without cleaning data first. If existing rows violate the new rule, the ALTER TABLE is rejected. Fix the data, then constrain.

FAQ

What is the difference between PRIMARY KEY and UNIQUE? PRIMARY KEY = UNIQUE + NOT NULL + it is the row's official identifier, and a table has only one. A UNIQUE constraint enforces uniqueness only, allows a NULL, and you can have several per table.

Can a CHECK constraint reference another table? No. Standard CHECK constraints can only reference columns in the same row. For cross-table rules, use a FOREIGN KEY or a trigger.

Do constraints slow down inserts? Slightly — the database does extra validation — but the cost is small and the protection against corrupt data is enormous. Keep them.

Keep learning

Constraints implement the integrity rules of the relational model, build on keys in DBMS, and complement triggers in SQL for rules too complex for a CHECK.

Want to build schemas that refuse to hold bad data? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.