MySQL & Database Design

Constraints & Data Integrity

13 min Lesson 29 of 40

Constraints & Data Integrity

Database constraints are rules enforced at the database level to maintain data accuracy, consistency, and reliability. They act as guardians, preventing invalid data from entering your database and ensuring business rules are consistently applied across all applications accessing the data.

Why Constraints Matter

Imagine an e-commerce system where multiple applications (web, mobile, API) access the database. Without constraints, one buggy application could insert an order with a negative price or create a user without an email. Constraints prevent such inconsistencies by enforcing rules at the database level.

Database-Level vs Application-Level Validation: Application validation is important for user experience, but database constraints are your last line of defense. They ensure data integrity even when applications have bugs or when direct database access occurs.

Types of Constraints

MySQL supports several types of constraints:

NOT NULL: Ensures column cannot contain NULL values
UNIQUE: Ensures all values in column are unique
PRIMARY KEY: Uniquely identifies each row (NOT NULL + UNIQUE)
FOREIGN KEY: Ensures referential integrity between tables
CHECK: Ensures values meet specific conditions (MySQL 8.0.16+)
DEFAULT: Provides default value when none specified

NOT NULL Constraint

The NOT NULL constraint prevents null values in a column:

-- Creating table with NOT NULL
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(50) NOT NULL,
    bio TEXT,  -- Can be NULL
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- This works
INSERT INTO users (email, username) VALUES ('john@example.com', 'john123');

-- This fails: Column 'email' cannot be null
INSERT INTO users (username) VALUES ('jane123');

-- Adding NOT NULL to existing column
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL;

Warning: Adding NOT NULL to an existing column fails if any rows already contain NULL values. Update those rows first or provide a DEFAULT value.

UNIQUE Constraint

The UNIQUE constraint ensures all values in a column are distinct:

-- Single column unique constraint
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    phone VARCHAR(20) UNIQUE
);

-- Named unique constraint
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50),
    name VARCHAR(255),
    CONSTRAINT uq_product_sku UNIQUE (sku)
);

-- Composite unique constraint (combination must be unique)
CREATE TABLE course_enrollments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_student_course UNIQUE (student_id, course_id)
);

UNIQUE allows multiple NULL values (NULL is not considered equal to NULL):

-- These both work (NULL is allowed multiple times in UNIQUE column)
INSERT INTO users (email, username, phone) VALUES ('a@ex.com', 'user1', NULL);
INSERT INTO users (email, username, phone) VALUES ('b@ex.com', 'user2', NULL);

-- This fails (duplicate phone number)
INSERT INTO users (email, username, phone) VALUES ('c@ex.com', 'user3', '555-1234');
INSERT INTO users (email, username, phone) VALUES ('d@ex.com', 'user4', '555-1234');

PRIMARY KEY Constraint

The PRIMARY KEY uniquely identifies each row and combines NOT NULL with UNIQUE:

-- Single column primary key
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

-- Named primary key
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (order_id)
);

-- Composite primary key (multiple columns)
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Best Practice: Use a single auto-incrementing INT or BIGINT as primary key for most tables. Reserve composite primary keys for junction tables (many-to-many relationships).

CHECK Constraint (MySQL 8.0.16+)

CHECK constraints ensure that values satisfy specific conditions:

-- Basic CHECK constraint
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL,
    discount_percent DECIMAL(5, 2),
    CONSTRAINT chk_price_positive CHECK (price > 0),
    CONSTRAINT chk_stock_non_negative CHECK (stock >= 0),
    CONSTRAINT chk_discount_range CHECK (discount_percent BETWEEN 0 AND 100)
);

-- This works
INSERT INTO products (name, price, stock, discount_percent)
VALUES ('Laptop', 999.99, 50, 10.00);

-- This fails: Check constraint 'chk_price_positive' is violated
INSERT INTO products (name, price, stock, discount_percent)
VALUES ('Free Item', 0, 10, 0);

-- This fails: Check constraint 'chk_discount_range' is violated
INSERT INTO products (name, price, stock, discount_percent)
VALUES ('Invalid', 100, 10, 150);

Advanced CHECK Constraints

CHECK constraints can reference multiple columns:

CREATE TABLE promotions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    min_purchase DECIMAL(10, 2),
    max_discount DECIMAL(10, 2),
    CONSTRAINT chk_date_order CHECK (end_date >= start_date),
    CONSTRAINT chk_discount_valid CHECK (max_discount <= min_purchase)
);

-- Employee salary validation
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    position ENUM('intern', 'junior', 'senior', 'manager') NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    CONSTRAINT chk_intern_salary CHECK (
        position != 'intern' OR salary BETWEEN 20000 AND 40000
    ),
    CONSTRAINT chk_manager_salary CHECK (
        position != 'manager' OR salary >= 80000
    )
);

Limitation: CHECK constraints cannot reference other tables or use subqueries. For cross-table validation, use triggers or application logic.

DEFAULT Constraint

DEFAULT provides automatic values when none is specified:

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    views INT DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Only title and content provided, others use defaults
INSERT INTO posts (title, content) VALUES ('My Post', 'Content here');

-- Result: status='draft', views=0, is_featured=FALSE, timestamps set automatically

Managing Constraints

You can add, modify, and drop constraints after table creation:

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

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

-- Drop constraint
ALTER TABLE users DROP CONSTRAINT uq_email;
ALTER TABLE products DROP CHECK chk_price;

-- Add NOT NULL
ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL;

-- Remove NOT NULL (make nullable)
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NULL;

Viewing Constraints

Query the information schema to see constraint details:

-- View all constraints for a table
SELECT
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    TABLE_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'products';

-- View CHECK constraint definitions
SELECT
    CONSTRAINT_NAME,
    CHECK_CLAUSE
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'your_database';

-- View column constraints
SHOW CREATE TABLE products;

Real-World Example: E-Commerce Order System

Here's a complete example with multiple constraint types:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
        DEFAULT 'pending' NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL,
    tax DECIMAL(10, 2) NOT NULL,
    shipping DECIMAL(10, 2) NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    shipped_date TIMESTAMP NULL,

    -- CHECK constraints
    CONSTRAINT chk_subtotal_positive CHECK (subtotal > 0),
    CONSTRAINT chk_tax_non_negative CHECK (tax >= 0),
    CONSTRAINT chk_shipping_non_negative CHECK (shipping >= 0),
    CONSTRAINT chk_total_correct CHECK (total = subtotal + tax + shipping),
    CONSTRAINT chk_shipped_date_after_order CHECK (
        shipped_date IS NULL OR shipped_date >= order_date
    )
);

-- This works
INSERT INTO orders (customer_id, order_number, subtotal, tax, shipping, total)
VALUES (123, 'ORD-2024-0001', 100.00, 8.00, 5.00, 113.00);

-- This fails: chk_total_correct violated
INSERT INTO orders (customer_id, order_number, subtotal, tax, shipping, total)
VALUES (123, 'ORD-2024-0002', 100.00, 8.00, 5.00, 100.00);

Best Practice: Use meaningful constraint names (e.g., chk_price_positive) rather than letting MySQL generate names. This makes error messages clearer and constraint management easier.

Constraint Violation Handling

When constraints are violated, MySQL returns specific error codes:

-- Error 1048: Column cannot be null (NOT NULL violation)
-- Error 1062: Duplicate entry (UNIQUE/PRIMARY KEY violation)
-- Error 3819: Check constraint is violated (CHECK constraint violation)
-- Error 1452: Foreign key constraint fails (FOREIGN KEY violation)

-- PHP example: Handling constraint violations
try {
    $pdo->exec(
        "INSERT INTO users (email, username) VALUES ('test@ex.com', 'test')"
    );
} catch (PDOException $e) {
    if ($e->getCode() == 23000) {  // Integrity constraint violation
        if (strpos($e->getMessage(), 'Duplicate entry') !== false) {
            echo "This email or username is already taken";
        }
    } else {
        throw $e;
    }
}

Practice Exercise:

Task: Create a booking system table with appropriate constraints.

Requirements:

Booking must have customer_id, room_id, check_in, check_out
Booking number must be unique
Check-out must be after check-in
Status must be pending/confirmed/cancelled (default: pending)
Total cost must be positive
Customer can't have overlapping bookings for same room

Solution:

CREATE TABLE bookings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_number VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    room_id INT NOT NULL,
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    status ENUM('pending', 'confirmed', 'cancelled')
        DEFAULT 'pending' NOT NULL,
    total_cost DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT chk_checkout_after_checkin
        CHECK (check_out > check_in),
    CONSTRAINT chk_cost_positive
        CHECK (total_cost > 0),
    CONSTRAINT uq_customer_room_dates
        UNIQUE (customer_id, room_id, check_in)
);

-- Test valid booking
INSERT INTO bookings
    (booking_number, customer_id, room_id, check_in, check_out, total_cost)
VALUES
    ('BK-2024-001', 123, 201, '2024-03-15', '2024-03-18', 450.00);

-- This fails: check_out must be after check_in
INSERT INTO bookings
    (booking_number, customer_id, room_id, check_in, check_out, total_cost)
VALUES
    ('BK-2024-002', 124, 202, '2024-03-15', '2024-03-15', 150.00);

Constraints vs Triggers

Choose the right tool for data validation:

Use Constraints When:
✓ Simple column-level validation
✓ Uniqueness requirements
✓ Referential integrity (foreign keys)
✓ Basic range checks
✓ Performance is critical

Use Triggers When:
✓ Complex business logic
✓ Cross-table validation
✓ Audit logging
✓ Calculated/derived values
✓ Custom error messages needed

Summary

In this lesson, you learned:

Constraints enforce data integrity at the database level
NOT NULL prevents null values in columns
UNIQUE ensures all values are distinct
PRIMARY KEY uniquely identifies rows (NOT NULL + UNIQUE)
CHECK constraints validate data against conditions (MySQL 8.0.16+)
DEFAULT provides automatic values
Use meaningful constraint names for better error messages
Constraints are your last line of defense against invalid data

Next Up: In the next lesson, we'll explore referential integrity and foreign key constraints in depth, including cascade operations!