NOT NULL, UNIQUE, CHECK and DEFAULT in MySQL

What are constraints

Constraints are rules you put on columns to keep data valid automatically. Instead of checking values in your program every time, the database itself enforces them. The most common are NOT NULL, UNIQUE, DEFAULT and CHECK.

NOT NULL

CREATE TABLE students (
    roll INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

NOT NULL means the column must always have a value. Trying to insert a student without a name fails. Use it for required fields that must never be empty.

UNIQUE

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

UNIQUE stops duplicate values in a column. Two users cannot share the same email. Unlike a primary key, UNIQUE can allow one null and a table can have several UNIQUE columns.

DEFAULT

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(10) DEFAULT 'pending'
);
INSERT INTO orders (id) VALUES (1);   -- status becomes 'pending'

DEFAULT gives a fallback value used when you do not supply one. Here a new order with no status automatically becomes 'pending', saving you from setting it every time.

CHECK

CREATE TABLE students (
    roll INT PRIMARY KEY,
    marks INT CHECK (marks >= 0 AND marks <= 100)
);

CHECK enforces a condition on the value. Here marks must be between 0 and 100; MySQL rejects any row that breaks this. It guarantees values stay sensible.

All together

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 18),
    active BOOLEAN DEFAULT TRUE
);

You can combine constraints on one table. Here every employee must have a name, a unique email, an age of at least 18, and is active by default. The database enforces all these rules for you.

Common mistakes

• Missing NOT NULL on required fields. Then empty values slip in.
• Confusing UNIQUE and PRIMARY KEY. UNIQUE allows one null; primary key does not.
• Forgetting DEFAULT applies only when the column is omitted. Passing NULL explicitly overrides it.
• Assuming CHECK works on very old MySQL. CHECK is enforced from MySQL 8.0.16 onward.

Summary

• Constraints are automatic rules that keep data valid.
• NOT NULL requires a value; UNIQUE prevents duplicates.
• DEFAULT supplies a fallback value when none is given.
• CHECK enforces a condition, like a valid range.
• You can combine several constraints on one table.

Next, learn the key constraints in primary key vs foreign key, or change columns with ALTER TABLE.