Column and Table Constraints

Learning Focus

Use this lesson to understand column-level and table-level constraints — the rules you embed in your schema to reject bad data before it ever reaches your application.

Concept Overview

What Are Constraints?

Constraints are rules that MySQL enforces automatically every time data is inserted or updated. Instead of relying on your application code to validate data (which can have bugs, be bypassed, or be inconsistent across services), constraints guarantee that invalid data never enters the database at all.

Think of constraints as a security guard at the door of each table — every row must pass inspection before being admitted.

Why Are Constraints Important?

• Data quality — bad data costs more the longer it lives in your system
• Defense in depth — even if application validation has a bug, the database catches it
• Self-documenting — constraints tell developers what's allowed without reading app code
• Consistency across services — if three apps write to the same table, constraints protect all of them equally

The Four Core Constraints

Constraint	What It Does	Analogy
NOT NULL	Requires a value — no blanks allowed	A required field on a form
UNIQUE	No duplicate values in the column	"Email already in use"
CHECK	Custom validation rule	"Age must be 13 or older"
DEFAULT	Auto-fills a value if none is provided	Pre-selected option on a form

---

Basic Syntax & Rules

NOT NULL — Mandatory Values

CREATE TABLE users (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email   VARCHAR(255) NOT NULL,  -- Cannot be blank
    name    VARCHAR(120) NOT NULL
);

An INSERT without email or name will fail:

INSERT INTO users (email) VALUES ('alice@example.com');
-- ERROR 1364: Field 'name' doesn't have a default value

UNIQUE — No Duplicates

CREATE TABLE users (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email   VARCHAR(255) NOT NULL,

    CONSTRAINT uq_users_email UNIQUE (email)
);

Inserting the same email twice will fail:

INSERT INTO users (email) VALUES ('alice@example.com');
INSERT INTO users (email) VALUES ('alice@example.com');
-- ERROR 1062: Duplicate entry 'alice@example.com' for key 'uq_users_email'

CHECK — Custom Validation Rules

CHECK constraints let you write any boolean condition. MySQL 8.0.16+ enforces them:

CREATE TABLE users (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email   VARCHAR(255) NOT NULL,
    age     INT,

    CONSTRAINT chk_users_age CHECK (age IS NULL OR age >= 13)
);

INSERT INTO users (email, age) VALUES ('kid@example.com', 10);
-- ERROR 3819: Check constraint 'chk_users_age' is violated

DEFAULT — Fallback Values

CREATE TABLE users (
    user_id    BIGINT AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(255) NOT NULL,
    status     VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (email) VALUES ('alice@example.com');
-- status → 'active', created_at → current time (auto-filled)

---

Step-by-Step Examples

Example 1: Building a Fully Constrained Users Table

This example combines all four constraint types into a single, production-ready table:

Step 1: Create the table with all constraints

CREATE TABLE users (
    user_id    BIGINT AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(255) NOT NULL,
    username   VARCHAR(50)  NOT NULL,
    age        INT,
    status     VARCHAR(20)  NOT NULL DEFAULT 'active',
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_users_email    UNIQUE (email),
    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT chk_users_age     CHECK (age IS NULL OR age >= 13),
    CONSTRAINT chk_users_status  CHECK (status IN ('active', 'suspended', 'deleted'))
);

Step 2: Test each constraint

-- ✅ Valid insert
INSERT INTO users (email, username, age)
VALUES ('alice@example.com', 'alice', 25);

-- ❌ NULL email (violates NOT NULL)
INSERT INTO users (email, username) VALUES (NULL, 'bob');
-- ERROR 1048: Column 'email' cannot be null

-- ❌ Duplicate email (violates UNIQUE)
INSERT INTO users (email, username) VALUES ('alice@example.com', 'alice2');
-- ERROR 1062: Duplicate entry

-- ❌ Age too young (violates CHECK)
INSERT INTO users (email, username, age) VALUES ('kid@example.com', 'kiddo', 10);
-- ERROR 3819: Check constraint 'chk_users_age' is violated

-- ❌ Invalid status (violates CHECK)
INSERT INTO users (email, username, status) VALUES ('bob@example.com', 'bob', 'banned');
-- ERROR 3819: Check constraint 'chk_users_status' is violated

-- ✅ Default status and timestamp auto-fill
INSERT INTO users (email, username) VALUES ('charlie@example.com', 'charlie');
SELECT * FROM users WHERE username = 'charlie';

Expected output for Charlie's row:

user_id	email	username	age	status	created_at
3	charlie@example.com	charlie	NULL	active	2026-02-10 13:00:00

---

Example 2: Adding Constraints to an Existing Table

You often need to add constraints to tables that already exist:

-- Add NOT NULL (requires modifying the column definition)
ALTER TABLE users MODIFY email VARCHAR(255) NOT NULL;

-- Add UNIQUE
ALTER TABLE users ADD CONSTRAINT uq_users_phone UNIQUE (phone);

-- Add CHECK
ALTER TABLE users ADD CONSTRAINT chk_users_age CHECK (age >= 13);

Warning: If existing data violates the constraint, the ALTER TABLE will fail. You must clean the data first.

Cleaning data before adding a constraint:

-- Find rows that would violate the new CHECK
SELECT user_id, age FROM users WHERE age < 13;

-- Fix or delete them
UPDATE users SET age = NULL WHERE age < 13;

-- Now the constraint can be added
ALTER TABLE users ADD CONSTRAINT chk_users_age CHECK (age IS NULL OR age >= 13);

---

Example 3: Multi-Column UNIQUE Constraint

Sometimes uniqueness depends on a combination of columns:

CREATE TABLE enrollments (
    enrollment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    student_id    BIGINT NOT NULL,
    course_id     BIGINT NOT NULL,
    enrolled_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- A student can only enroll in each course once
    CONSTRAINT uq_enrollment UNIQUE (student_id, course_id)
);

INSERT INTO enrollments (student_id, course_id) VALUES (1, 101);
INSERT INTO enrollments (student_id, course_id) VALUES (1, 101);
-- ERROR 1062: Duplicate entry '1-101' for key 'uq_enrollment'

---

Practical Use Cases

1. User Registration

NOT NULL on email/password, UNIQUE on email/username, CHECK on password length or age, DEFAULT on account status.

2. Financial Records

NOT NULL on amount and date fields, CHECK to ensure amounts are non-negative, DEFAULT on currency to 'USD'.

3. Inventory Management

CHECK to ensure quantity >= 0 (no negative stock), UNIQUE on SKU codes, DEFAULT on warehouse location.

4. Multi-Tenant SaaS

Multi-column UNIQUE constraints like UNIQUE (tenant_id, email) to allow the same email across tenants but prevent duplicates within one tenant.

5. Configuration Tables

NOT NULL and DEFAULT to ensure every config key has a value, CHECK on allowed value ranges.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Relying only on app-side validation	Invalid rows bypass business rules when another service writes directly	Always enforce critical rules as DB constraints
Adding constraints on dirty data	ALTER TABLE fails	Query for violations first, clean them, then add constraint
Generic constraint names	Error messages like "constraint1 violated" are hard to debug	Use descriptive names: chk_users_age, uq_orders_ref
Forgetting CHECK needs MySQL 8.0.16+	CHECK is parsed but ignored in older versions	Verify your MySQL version: SELECT VERSION();
Using DEFAULT without NOT NULL	Column can still be explicitly set to NULL	Combine both: NOT NULL DEFAULT 'active'
Too many constraints on volatile columns	Frequent ALTER TABLE during development	Plan constraints during schema design, not after

---

Best Practices

• Name every constraint — CONSTRAINT chk_users_age CHECK (...) is debuggable; unnamed constraints are not
• Validate at both layers — use app-side validation for user-friendly error messages, DB constraints for safety
• Use CHECK for domain rules — amounts >= 0, status in allowed list, dates in valid range
• Combine NOT NULL and DEFAULT — ensures a value always exists, even if the INSERT doesn't specify one
• Clean before you constrain — always check existing data before adding new constraints to production tables
• Document constraint rationale — add comments explaining why a constraint exists, not just what it does

---

Hands-On Practice

Exercise 1: Build a Products Table (Easy)

Create a products table with:

• product_id (PK, auto-increment)
• name (required, unique)
• price (required, must be > 0)
• stock_quantity (required, must be >= 0, default 0)
• status (required, must be 'active' or 'discontinued', default 'active')

Test by inserting valid products, then try violating each constraint.

Exercise 2: Add Constraints to an Existing Table (Medium)

You have this table with no constraints:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_email VARCHAR(255),
    total DECIMAL(10,2),
    status VARCHAR(20)
);

Add: NOT NULL on all columns, CHECK that total > 0, CHECK that status is one of 'pending', 'paid', 'refunded', and DEFAULT 'pending' on status. Show the ALTER TABLE statements.

Exercise 3: Debug a Failed Migration (Advanced)

Your team runs this migration and it fails:

ALTER TABLE users ADD CONSTRAINT chk_users_age CHECK (age >= 18);

The error says the check constraint is violated. Write SQL to:

1. Find all rows that violate the constraint
2. Decide how to handle them (update, delete, or change the constraint)
3. Successfully add the constraint

---

Connection to Other Concepts

Related Concept	How It Connects
Primary & Foreign Keys	Keys define identity and relationships; constraints define value rules
Indexes	UNIQUE constraints automatically create indexes
Data Types	Choosing the right type (e.g., DECIMAL vs FLOAT) works hand-in-hand with constraints
Schema Migrations	Adding constraints to existing tables requires careful data validation
Stored Procedures	Procedures can enforce complex business rules that go beyond what CHECK can express

What to Learn Next

• Index Strategy and Maintenance — optimize the queries that work with your constrained tables

---

Visual Learning Diagram

flowchart TD
    A["INSERT or UPDATE arrives"] --> B{"NOT NULL check"}
    B -->|Pass| C{"UNIQUE check"}
    B -->|Fail| X["❌ Rejected: NULL value"]
    C -->|Pass| D{"CHECK constraint"}
    C -->|Fail| Y["❌ Rejected: Duplicate value"]
    D -->|Pass| E{"Foreign Key check"}
    D -->|Fail| Z["❌ Rejected: Rule violated"]
    E -->|Pass| F["✅ Row saved"]
    E -->|Fail| W["❌ Rejected: No parent row"]

    classDef reject fill:#dc3545,stroke:#fff,color:#fff
    classDef accept fill:#28a745,stroke:#fff,color:#fff
    class X,Y,Z,W reject
    class F accept

Quick Reference

-- NOT NULL
ALTER TABLE t MODIFY col VARCHAR(255) NOT NULL;

-- UNIQUE
ALTER TABLE t ADD CONSTRAINT uq_name UNIQUE (col);

-- CHECK (MySQL 8.0.16+)
ALTER TABLE t ADD CONSTRAINT chk_name CHECK (col >= 0);

-- DEFAULT
ALTER TABLE t ALTER col SET DEFAULT 'value';

-- Remove constraint
ALTER TABLE t DROP CONSTRAINT constraint_name;

-- View constraints
SHOW CREATE TABLE t\G
SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME = 't';

What's Next

• Index Strategy and Maintenance