MySQL — Constraints

What are Constraints?

Constraints enforce rules on table columns to maintain data integrity.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INT CHECK (age >= 18),
    status VARCHAR(20) DEFAULT 'active'
);

Types of Constraints

Constraint	Purpose
NOT NULL	Column cannot be empty
UNIQUE	All values must be different
PRIMARY KEY	Unique identifier, not null
FOREIGN KEY	Links to another table
CHECK	Validates a condition
DEFAULT	Provides a default value

NOT NULL

ALTER TABLE users MODIFY name VARCHAR(100) NOT NULL;

Adding Constraints After Creation

ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 18);
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

Dropping Constraints

ALTER TABLE users DROP CHECK chk_age;
ALTER TABLE users DROP INDEX uq_email;

Naming Constraints

CREATE TABLE orders (
    id INT,
    CONSTRAINT pk_orders PRIMARY KEY (id),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Mini Practice

Write SQL code that:

1. Creates a table with all constraint types
2. Adds a CHECK constraint after creation
3. Adds a UNIQUE constraint to an existing column
4. Drops a constraint

Up Next

Continue with Primary Key — the unique identifier for each row.