MySQL Constraints : Primary Keys, Foreign Keys, and Beyond

MySQL Constraints

Constraints are rules enforced on table columns to ensure data integrity. They prevent invalid data from entering your MySQL database. This tutorial uses MySQL 8.0+ syntax throughout.

Table of Contents

• NOT NULL
• DEFAULT
• UNIQUE
• PRIMARY KEY & AUTO_INCREMENT
• Composite Keys
• FOREIGN KEY
• CHECK
• Adding & Dropping Constraints
• Full Example

NOT NULL

By default, a column allows NULL values (unknown/missing data). NOT NULL forces the column to always have a value.

CREATE TABLE users (
    id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

Here, id and username must be provided on insert. email can be left empty.

DEFAULT

Assigns a fallback value when no value is provided during insert.

CREATE TABLE users (
    id INT NOT NULL,
    is_active INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (id) VALUES (1);
-- is_active = 1, created_at = current timestamp

UNIQUE

Ensures all values in a column (or a group of columns) are different from each other.

CREATE TABLE users (
    id INT NOT NULL,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE
);

INSERT INTO users (id, email, username) VALUES (1, 'a@b.com', 'alice');
INSERT INTO users (id, email, username) VALUES (2, 'a@b.com', 'bob');
-- ERROR: duplicate email

A table can have multiple UNIQUE columns. In MySQL, multiple NULL values are allowed in a UNIQUE column — they never count as duplicates.

PRIMARY KEY & AUTO_INCREMENT

A PRIMARY KEY is a column (or set of columns) that uniquely identifies each row. It combines NOT NULL + UNIQUE into one constraint. Each table should have one primary key.

AUTO_INCREMENT automatically generates sequential numbers (1, 2, 3...) for the primary key so you don't have to specify it.

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    PRIMARY KEY (id)
);

INSERT INTO users (username, email) VALUES ('alice', 'a@b.com');
INSERT INTO users (username, email) VALUES ('bob', 'b@c.com');
-- id is assigned automatically: 1, 2, 3...

MySQL behavior: Each table has one AUTO_INCREMENT column, must be indexed (PRIMARY KEY works), and must be a numeric type. Gaps can occur due to rollbacks or deletions — the sequence doesn't reuse numbers.

Other databases use different syntax: PostgreSQL uses SERIAL, SQL Server uses IDENTITY(1,1), SQLite uses AUTOINCREMENT.

Composite Keys

A composite key is a primary key made of multiple columns. Used when no single column is unique, but a combination of columns is.

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrolled_date DATE,
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollments VALUES (1, 101, '2024-01-01');
INSERT INTO enrollments VALUES (1, 102, '2024-01-01');
INSERT INTO enrollments VALUES (2, 101, '2024-01-02');
INSERT INTO enrollments VALUES (1, 101, '2024-01-03');
-- ERROR: (1, 101) already exists

Composite keys also work with FOREIGN KEY when referencing tables that have composite primary keys.

FOREIGN KEY

A FOREIGN KEY links rows between two tables. It ensures a value in one table matches an existing value in another table (usually its primary key). This maintains referential integrity.

MySQL requirement: The table must use the InnoDB engine (default since MySQL 5.5). MyISAM tables ignore foreign key definitions silently.

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO orders (user_id, amount) VALUES (1, 99.99);
-- OK: user 1 exists

INSERT INTO orders (user_id, amount) VALUES (99, 50.00);
-- ERROR: no user with id 99

ON DELETE / ON UPDATE actions control what happens when the referenced row is removed or changed:

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

Self-referencing FOREIGN KEY — a table that references its own primary key. Common for hierarchies:

CREATE TABLE employees (
    emp_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    manager_id INT,
    PRIMARY KEY (emp_id),
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

INSERT INTO employees (name, manager_id) VALUES ('Alice', NULL);   -- CEO
INSERT INTO employees (name, manager_id) VALUES ('Bob', 1);        -- reports to Alice
INSERT INTO employees (name, manager_id) VALUES ('Carol', 1);      -- reports to Alice

CHECK

Validates that a value meets a specified condition before allowing insert or update.

CREATE TABLE products (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    quantity INT,
    PRIMARY KEY (id),
    CHECK (price > 0),
    CHECK (quantity >= 0)
);

INSERT INTO products (name, price, quantity) VALUES ('Laptop', -500, 10);
-- ERROR: price must be > 0

INSERT INTO products (name, price, quantity) VALUES ('Laptop', 500, -5);
-- ERROR: quantity must be >= 0

You can also name your CHECK constraints for clearer error messages:

CREATE TABLE employees (
    id INT NOT NULL AUTO_INCREMENT,
    age INT,
    salary DECIMAL(10,2),
    PRIMARY KEY (id),
    CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65),
    CONSTRAINT chk_salary CHECK (salary > 0)
);

Note: MySQL ≤ 8.0.15 ignores CHECK constraints. They are enforced from MySQL 8.0.16 onward.

Adding & Dropping Constraints

Constraints aren't only for CREATE TABLE — you can add or remove them on existing tables with ALTER TABLE.

Adding constraints:

ALTER TABLE users ADD UNIQUE (email);

ALTER TABLE products ADD CHECK (price > 0);

ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- Add a named constraint
ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);

Dropping constraints (MySQL):

ALTER TABLE users DROP INDEX email;                -- drop UNIQUE
ALTER TABLE products DROP CHECK chk_price;         -- drop CHECK (8.0.16+)
ALTER TABLE orders DROP FOREIGN KEY fk_user_id;    -- drop FOREIGN KEY
ALTER TABLE employees DROP PRIMARY KEY;            -- drop PRIMARY KEY

Multi-column CHECK — validates across more than one column:

CREATE TABLE tasks (
    task_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(200),
    start_date DATE NOT NULL,
    end_date DATE,
    priority VARCHAR(10) DEFAULT 'medium',
    PRIMARY KEY (task_id),
    CHECK (end_date IS NULL OR end_date >= start_date)
);

INSERT INTO tasks (title, start_date, end_date) VALUES ('Design', '2024-01-10', '2024-01-05');
-- ERROR: end_date cannot be before start_date

INSERT INTO tasks (title, start_date) VALUES ('Design', '2024-01-10');
-- OK: end_date is NULL, CHECK passes

Adding a multi-column CHECK later:

ALTER TABLE tasks ADD CHECK (priority IN ('low', 'medium', 'high', 'critical'));

Full Example

A complete schema showing all constraints working together:

CREATE TABLE departments (
    dept_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    budget DECIMAL(12,2) DEFAULT 0.00,
    PRIMARY KEY (dept_id)
);

CREATE TABLE employees (
    emp_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2),
    dept_id INT NOT NULL,
    PRIMARY KEY (emp_id),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE RESTRICT,
    CHECK (salary > 0)
);

CREATE TABLE projects (
    project_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    lead_id INT,
    PRIMARY KEY (project_id),
    FOREIGN KEY (lead_id) REFERENCES employees(emp_id) ON DELETE SET NULL
);

CREATE TABLE project_members (
    emp_id INT NOT NULL,
    project_id INT NOT NULL,
    role VARCHAR(50) DEFAULT 'member',
    PRIMARY KEY (emp_id, project_id),
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE
);

What this schema enforces:

• Every department has a unique name and a default budget of 0
• Every employee has a name, email, hire date, belongs to a real department, and gets a positive salary
• No two employees can share an email
• Every project has a lead who is an actual employee (but the project survives if the lead leaves)
• An employee can be on many projects, a project can have many members — no duplicates allowed
• Deleting a department is blocked if it has employees
• Deleting an employee removes them from all projects automatically