MySQL Constraints

Types of Constraints

id INT PRIMARY KEY

FOREIGN KEY (user_id) 
REFERENCES users(id)

email VARCHAR(100) UNIQUE

name VARCHAR(50) NOT NULL

age INT CHECK (age >= 0)

status VARCHAR(20) 
DEFAULT 'active'

🔹 PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies each record in a table. Each table can have only one primary key, which must contain unique values and cannot be NULL. Primary keys are automatically indexed for fast lookups and are commonly used with AUTO_INCREMENT for automatic ID generation.

-- Primary key in column definition
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- Primary key as table constraint
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT,
    course_name VARCHAR(100),
    PRIMARY KEY (course_id)
);

-- Composite primary key (multiple columns)
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

🔹 FOREIGN KEY Constraint

A FOREIGN KEY creates a link between two tables by referencing the primary key of another table. This maintains referential integrity, ensuring that relationships between tables remain consistent. Foreign keys prevent orphaned records and can automatically cascade updates or deletions to related records.

-- Create parent table first
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

-- Create child table with foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Foreign key with cascade options
CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_name VARCHAR(100),
    FOREIGN KEY (order_id) 
        REFERENCES orders(order_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

🔹 UNIQUE Constraint

The UNIQUE constraint ensures all values in a column are different from each other. Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and UNIQUE columns can contain NULL values (though only one NULL per column in most cases). This is perfect for fields like email addresses or usernames.

-- Unique constraint in column definition
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20)
);

-- Unique constraint as table constraint
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_code VARCHAR(50),
    product_name VARCHAR(100),
    UNIQUE (product_code)
);

-- Composite unique constraint
CREATE TABLE user_profiles (
    user_id INT,
    platform VARCHAR(50),
    profile_url VARCHAR(200),
    UNIQUE (user_id, platform)
);

🔹 NOT NULL Constraint

The NOT NULL constraint ensures a column must always have a value and cannot be left empty. This is essential for required fields where missing data would make records incomplete or invalid. By default, columns allow NULL unless explicitly marked as NOT NULL.

-- NOT NULL in column definitions
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),  -- This allows NULL
    hire_date DATE NOT NULL
);

-- Attempting to insert NULL will fail
INSERT INTO employees (first_name, last_name, email, hire_date)
VALUES ('John', 'Doe', '[email protected]', '2024-01-15');

-- This will cause an error (email is NULL)
-- INSERT INTO employees (first_name, last_name, hire_date)
-- VALUES ('Jane', 'Smith', '2024-01-20');

🔹 CHECK Constraint

The CHECK constraint validates data against a specified condition before allowing insertion or updates. This enforces business rules at the database level, such as ensuring ages are positive, prices are above zero, or dates fall within valid ranges. CHECK constraints help maintain data quality automatically.

-- CHECK constraint examples
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock_quantity INT CHECK (stock_quantity >= 0),
    discount_percent INT CHECK (discount_percent BETWEEN 0 AND 100)
);

-- Multiple conditions in CHECK
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18 AND age <= 65),
    salary DECIMAL(10, 2) CHECK (salary > 0),
    department VARCHAR(50) CHECK (department IN ('Sales', 'IT', 'HR', 'Finance'))
);

-- Named CHECK constraint
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(12, 2),
    CONSTRAINT chk_positive_balance CHECK (balance >= 0)
);

🔹 DEFAULT Constraint

The DEFAULT constraint provides automatic values for columns when no value is specified during insertion. This is useful for setting initial states, timestamps, or standard values. DEFAULT values reduce the need for explicit value specification and ensure consistency across records when fields are omitted.

-- DEFAULT constraint examples
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    role VARCHAR(20) DEFAULT 'user',
    credits INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert without specifying default columns
INSERT INTO users (username) 
VALUES ('john_doe');

-- View the inserted record with defaults
SELECT * FROM users WHERE username = 'john_doe';

🔹 Adding Constraints to Existing Tables

You can add constraints to existing tables using ALTER TABLE statements. This is useful when modifying database schemas or adding data integrity rules to legacy tables. Be cautious when adding constraints to tables with existing data, as the operation will fail if current data violates the new constraint.

-- Add PRIMARY KEY
ALTER TABLE users 
ADD PRIMARY KEY (user_id);

-- Add FOREIGN KEY
ALTER TABLE orders 
ADD FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id);

-- Add UNIQUE constraint
ALTER TABLE users 
ADD UNIQUE (email);

-- Add CHECK constraint
ALTER TABLE products 
ADD CONSTRAINT chk_price CHECK (price > 0);

-- Add DEFAULT value
ALTER TABLE users 
ALTER COLUMN status SET DEFAULT 'active';

-- Modify column to NOT NULL
ALTER TABLE users 
MODIFY email VARCHAR(100) NOT NULL;

🔹 Dropping Constraints

Constraints can be removed when they're no longer needed or need to be redefined. Named constraints are easier to drop, which is why it's good practice to name your constraints explicitly. Dropping constraints removes data validation rules, so ensure this aligns with your data integrity requirements.

-- Drop PRIMARY KEY
ALTER TABLE users 
DROP PRIMARY KEY;

-- Drop FOREIGN KEY (need constraint name)
ALTER TABLE orders 
DROP FOREIGN KEY fk_customer_id;

-- Drop UNIQUE constraint
ALTER TABLE users 
DROP INDEX email;

-- Drop CHECK constraint
ALTER TABLE products 
DROP CHECK chk_price;

-- Remove DEFAULT value
ALTER TABLE users 
ALTER COLUMN status DROP DEFAULT;

-- View constraint names
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'users';

🔹 Complete Example with Multiple Constraints

This comprehensive example demonstrates a real-world database design with multiple constraint types working together. It shows how constraints enforce business rules, maintain data integrity, and establish relationships between tables. This represents best practices for database design with proper data validation at every level.

-- Complete example: E-commerce database
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended'))
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INT DEFAULT 0 CHECK (stock >= 0),
    category VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) CHECK (total_amount >= 0),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT
);

-- Verify table structures
SHOW CREATE TABLE orders;