MySQL Constraints Cheatsheet

This cheatsheet covers the common constraints used in MySQL to enforce rules on data in tables.

NOT NULL Constraint
UNIQUE Constraint
PRIMARY KEY Constraint
FOREIGN KEY Constraint
CHECK Constraint
DEFAULT Constraint
AUTO_INCREMENT

NOT NULL Constraint

Ensures that a column cannot have a NULL value.

-- Adding NOT NULL constraint when creating a table
CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

-- Adding NOT NULL constraint to an existing column
ALTER TABLE employees
MODIFY email VARCHAR(100) NOT NULL;

UNIQUE Constraint

Ensures that all values in a column are different.

-- Adding UNIQUE constraint when creating a table
CREATE TABLE users (
    id INT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100)
);

-- Adding UNIQUE constraint to an existing column
ALTER TABLE users
ADD UNIQUE (email);

-- Adding UNIQUE constraint with a custom name
ALTER TABLE users
ADD CONSTRAINT uc_username UNIQUE (username);

PRIMARY KEY Constraint

Uniquely identifies each record in a table.

-- Adding PRIMARY KEY constraint when creating a table
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
);

-- Adding PRIMARY KEY constraint to an existing table
ALTER TABLE products
ADD PRIMARY KEY (id);

-- Composite PRIMARY KEY
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

FOREIGN KEY Constraint

Links data between two tables.

-- Adding FOREIGN KEY constraint when creating a table
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Adding FOREIGN KEY constraint to an existing table
ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Adding FOREIGN KEY with custom name and actions
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE RESTRICT;

CHECK Constraint

Ensures that all values in a column satisfy certain conditions.

-- Adding CHECK constraint when creating a table
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2) CHECK (price > 0)
);

-- Adding CHECK constraint to an existing table
ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);

DEFAULT Constraint

Specifies a default value for a column when no value is specified.

-- Adding DEFAULT constraint when creating a table
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'Pending'
);

-- Adding DEFAULT constraint to an existing column
ALTER TABLE orders
ALTER status SET DEFAULT 'Pending';

AUTO_INCREMENT

Automatically generates a unique number when a new record is inserted.

-- Using AUTO_INCREMENT when creating a table
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- Modifying an existing column to use AUTO_INCREMENT
ALTER TABLE customers
MODIFY id INT AUTO_INCREMENT;

Dropping Constraints

-- Drop PRIMARY KEY constraint
ALTER TABLE table_name
DROP PRIMARY KEY;

-- Drop FOREIGN KEY constraint
ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

-- Drop UNIQUE constraint
ALTER TABLE table_name
DROP INDEX constraint_name;

-- Drop CHECK constraint (MySQL 8.0.16 and higher)
ALTER TABLE table_name
DROP CHECK constraint_name;

📚Cheatsheets