Eric Campbell 5 min read MySQL

How to Use Constraints in MySQL

Database constraints are rules enforced by MySQL at the schema level to maintain data integrity. Unlike application-level validation, constraints guarantee data consistency regardless of how data...

Key Insights

  • Constraints enforce data integrity at the database level, providing a safety net that application code alone cannot guarantee—even if your application logic is perfect, direct database access or bugs can corrupt data without constraints.
  • Foreign key constraints with CASCADE options automate referential integrity maintenance, but improper use can lead to unexpected data deletion; always explicitly name your constraints for easier debugging and management.
  • CHECK constraints (MySQL 8.0.16+) move business rule validation from application code to the database layer, ensuring data validity regardless of which application or tool inserts the data.

Understanding Database Constraints

Database constraints are rules enforced by MySQL at the schema level to maintain data integrity. Unlike application-level validation, constraints guarantee data consistency regardless of how data enters your database—whether through your application, a direct SQL query, a batch import, or a third-party tool.

The fundamental principle is simple: constraints prevent bad data from ever being written. This is far superior to detecting and fixing corrupt data after the fact. When a constraint violation occurs, MySQL rejects the operation and returns an error, forcing you to handle the issue immediately.

Here’s the difference constraints make:

-- Without constraints: anything goes
CREATE TABLE users_bad (
    id INT,
    email VARCHAR(255),
    age INT
);

-- With constraints: data integrity enforced
CREATE TABLE users_good (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    age INT CHECK (age >= 18 AND age <= 120)
);

The second table prevents duplicate emails, null emails, missing IDs, and unrealistic ages. The first table accepts garbage.

PRIMARY KEY Constraints

A PRIMARY KEY uniquely identifies each row in a table. MySQL automatically creates an index on the primary key column(s), making lookups extremely fast. Every table should have a primary key—it’s not optional in well-designed databases.

Rules for primary keys:

  • Must contain unique values
  • Cannot contain NULL values
  • Only one primary key per table (but can span multiple columns)

Single-column primary key:

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

Composite primary key (multiple columns):

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Composite keys are useful when the combination of two or more columns uniquely identifies a row. In this example, the same product can appear in multiple orders, and each order can contain multiple products, but the specific combination is unique.

Adding a primary key to an existing table:

ALTER TABLE legacy_table
ADD PRIMARY KEY (id);

This fails if the column contains duplicates or NULLs. Clean your data first.

FOREIGN KEY Constraints

Foreign keys enforce referential integrity between tables. They ensure that a value in one table must exist as a primary key in another table, maintaining the parent-child relationship.

Basic foreign key setup:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Now you cannot create an order for a non-existent customer. Try inserting customer_id = 999 when no such customer exists, and MySQL rejects it.

CASCADE options automate related data management:

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

With ON DELETE CASCADE, deleting a customer automatically deletes all their orders. With ON UPDATE CASCADE, updating a customer’s ID updates all related orders. Use CASCADE carefully—it’s powerful but can cause unintended data loss.

Other referential actions:

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE SET NULL
        ON UPDATE RESTRICT
);
  • SET NULL: Sets foreign key to NULL when parent is deleted
  • RESTRICT: Prevents deletion/update of parent if children exist (default behavior)
  • NO ACTION: Similar to RESTRICT

UNIQUE, NOT NULL, and DEFAULT Constraints

These constraints handle common data validation scenarios.

UNIQUE constraint prevents duplicate values:

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20) UNIQUE
);

Both username and email must be unique. Phone can be NULL (unless combined with NOT NULL), but if provided, must be unique.

NOT NULL with DEFAULT values:

CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

This ensures every post has a status (defaulting to ‘draft’) and timestamps are automatically managed.

Multiple unique constraints:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    ssn VARCHAR(11) UNIQUE,
    employee_number VARCHAR(20) NOT NULL UNIQUE
);

Email, SSN, and employee number must all be unique independently.

CHECK Constraints

CHECK constraints (available since MySQL 8.0.16) enforce custom validation rules. This moves business logic from application code to the database layer.

Age range validation:

CREATE TABLE members (
    member_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    age INT NOT NULL,
    CONSTRAINT chk_age CHECK (age >= 18 AND age <= 100)
);

Enumerated values:

CREATE TABLE tasks (
    task_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    CONSTRAINT chk_status CHECK (status IN ('pending', 'in_progress', 'completed', 'cancelled'))
);

This is more maintainable than ENUM types when you need to modify allowed values.

Date logic validation:

CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    CONSTRAINT chk_dates CHECK (end_date IS NULL OR end_date >= start_date)
);

This ensures end_date cannot come before start_date.

Managing Constraints

Viewing existing constraints:

SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'your_database_name';

Adding constraints to existing tables:

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

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

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

Dropping constraints:

ALTER TABLE users DROP CONSTRAINT uk_email;
ALTER TABLE orders DROP FOREIGN KEY fk_customer;

Temporarily disabling foreign key checks (useful for data migration):

SET FOREIGN_KEY_CHECKS = 0;
-- Perform data operations
SET FOREIGN_KEY_CHECKS = 1;

Never leave foreign key checks disabled permanently. Use this only for specific operations like importing data or reordering table creation.

Best Practices and Common Pitfalls

Always name your constraints explicitly. MySQL generates names like users_ibfk_1 automatically, which are meaningless when debugging. Use descriptive names:

CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
CONSTRAINT chk_positive_price CHECK (price > 0),
CONSTRAINT uk_user_email UNIQUE (email)

Use foreign keys judiciously with large datasets. Foreign key checks add overhead to INSERT, UPDATE, and DELETE operations. For massive bulk operations, consider temporarily disabling checks, but ensure data integrity through other means.

Leverage CHECK constraints for business rules instead of relying solely on application validation. Your database outlives any single application, and direct database access bypasses application logic.

Don’t over-constrain. Every constraint adds validation overhead. Balance data integrity with performance. For example, complex CHECK constraints with subqueries can significantly slow down writes.

Understand CASCADE implications. ON DELETE CASCADE is convenient but dangerous. Accidentally deleting a parent record can wipe out thousands of child records. Consider using RESTRICT and handling deletions explicitly in application code for critical data.

Use NOT NULL liberally. NULL values complicate queries and application logic. Make columns NOT NULL unless NULL has specific business meaning (like “unknown” vs. “not applicable”).

Constraints are your first line of defense against data corruption. Invest time in designing them properly, and your database will remain consistent even as applications and requirements evolve.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.