Constraints in MySQL (with Output)

In this lesson: All MySQL constraints with examples and output — NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

What a constraint really is

A constraint is a rule you attach to a column so that MySQL itself refuses to store wrong data. Think of it as a security guard standing at each column: before any value gets in, the guard checks it against the rule. If the value breaks the rule, the whole insert is turned away with an error.

Why does this matter? Because in a real project, data comes from forms, imports and other programs — you cannot personally check every value. Constraints move that checking into the database, where it can never be skipped or forgotten. A fee can never be negative, an email can never repeat, a student row can never be created without a roll number. The rule is enforced every single time, automatically.

NOT NULL - no empty value

NOT NULL forces a column to always have a value. Without it, a column can be left empty (NULL), which often causes bugs later when you try to calculate or compare.

CREATE TABLE students (
    roll_no INT NOT NULL,
    name    VARCHAR(50) NOT NULL
);

-- This fails because name is missing:
INSERT INTO students (roll_no) VALUES (1);

MySQL blocked the row because name is required. That error is the constraint doing its job — it stopped an incomplete record before it entered the table.

UNIQUE - no duplicates

UNIQUE makes sure no two rows share the same value in that column. It is perfect for things like email, phone number or admission number, where a repeat would be a mistake.

CREATE TABLE users (
    id    INT,
    email VARCHAR(80) UNIQUE
);

INSERT INTO users VALUES (1, 'a@site.com');
INSERT INTO users VALUES (2, 'a@site.com');  -- duplicate!

The second insert failed the moment it tried to repeat an email. You did not have to check for duplicates yourself — the database guaranteed it.

PRIMARY KEY - the row identity

A PRIMARY KEY is the single column (or set of columns) that uniquely identifies each row. It is really two rules in one: UNIQUE + NOT NULL. Every table should have exactly one.

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title   VARCHAR(100)
);

Now book_id can never be empty and can never repeat. It becomes the reliable handle you use to find, update or link a specific book. To read more about how identity columns connect tables, see primary and foreign key.

FOREIGN KEY - linking tables

A FOREIGN KEY connects one table to another. It says: the value in this column must already exist in the parent table. This is what stops "orphan" rows, like an order that points to a customer who does not exist.

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Fails if customer 99 does not exist in customers:
INSERT INTO orders VALUES (1, 99);

MySQL refused to create an order for a customer that is not in the customers table. The link between the two tables is protected automatically.

CHECK and DEFAULT

CHECK enforces a condition on the value, and DEFAULT supplies a value when none is given.

CREATE TABLE products (
    id    INT PRIMARY KEY,
    price DECIMAL(8,2) CHECK (price >= 0),
    stock INT DEFAULT 0
);

INSERT INTO products (id, price) VALUES (1, 250.00);
INSERT INTO products (id, price) VALUES (2, -5.00);  -- fails CHECK

The first row worked and its stock defaulted to 0. The second row was rejected because a negative price breaks the CHECK rule. Two constraints, working quietly together.

Full example with all constraints

Here is one table that uses every constraint at once, so you can see how they combine in real code.

CREATE TABLE admissions (
    adm_no   INT PRIMARY KEY,                 -- UNIQUE + NOT NULL
    email    VARCHAR(80) UNIQUE NOT NULL,     -- no repeat, no empty
    class_id INT,
    fees     DECIMAL(10,2) CHECK (fees >= 0), -- never negative
    status   VARCHAR(10) DEFAULT 'active',    -- auto value
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

Read it top to bottom and each line tells you a rule the data must follow. Anyone who later reads this table definition instantly understands the shape and limits of the data — that is a second, hidden benefit of constraints.

Common mistakes

• Expecting CHECK to work on old MySQL. Before 8.0.16 it was ignored silently. Confirm your version if a CHECK rule seems to do nothing.
• Adding a UNIQUE constraint to dirty data. If duplicates already exist, ALTER TABLE will refuse until you remove them.
• Forgetting a foreign key needs matching data types. The child and parent columns must be the same type, or MySQL rejects the foreign key.
• Confusing NULL with 0 or empty string. NOT NULL blocks NULL, but it still allows an empty string '' or a 0 — add a CHECK if you need more.

Summary

• Constraints are rules MySQL enforces so wrong data can never be stored.
• NOT NULL requires a value; UNIQUE blocks duplicates.
• PRIMARY KEY is UNIQUE + NOT NULL and identifies each row.
• FOREIGN KEY links tables and stops orphan rows.
• CHECK enforces a condition (MySQL 8.0.16+); DEFAULT fills in a value.

Next, understand the difference between the identity rules in detail with NOT NULL, UNIQUE, CHECK and DEFAULT, or pick the right column type first in MySQL data types.