Constraints and Keys

Q: Q: Can a table have multiple UNIQUE constraints?

A: Yes. A table can have only one PRIMARY KEY, but can have multiple UNIQUE constraints. For example, both email and phone can each have a UNIQUE constraint.

Q: Q: Does the CHECK constraint support subqueries?

A: Most databases' CHECK constraints do not support subqueries. If you need cross-table validation, use triggers or implement it at the application layer.

Q: Q: What's the relationship between DEFAULT and NOT NULL?

A: If NOT NULL is set without DEFAULT, you must explicitly provide a value when inserting. If DEFAULT is set, you can omit the column during insertion and the default value is used automatically. They are often used together.

🌍 Real-World Analogy

Think of a database as a strict office:

PRIMARY KEY = Everyone's employee ID, never duplicated, never null
FOREIGN KEY = An employee must belong to a department that actually exists
UNIQUE = Everyone's email must be unique
NOT NULL = The name field cannot be left blank
CHECK = Age must be between 18 and 65
DEFAULT = When no status is filled in, default to "Active"

Constraints are the "rule guards" of a database—automatically checking data as it's written to prevent dirty data from entering.

🎯 Core Concepts

PRIMARY KEY

Uniquely identifies each row in a table. No duplicates allowed, NULL not allowed. A table can have only one primary key.

SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL
);

A primary key can also consist of multiple columns (composite primary key):

SQL

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

FOREIGN KEY

Ensures that values in one table must exist in another table, maintaining referential integrity.

SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Foreign key cascade operations:

Operation	Description
`ON DELETE CASCADE`	When the parent table row is deleted, related rows in the child table are also deleted
`ON DELETE SET NULL`	When the parent table row is deleted, the foreign key in the child table is set to NULL
`ON DELETE RESTRICT`	Deletion is prohibited if references exist (default)
`ON UPDATE CASCADE`	When the parent table's primary key is updated, the child table's foreign key is updated accordingly

SQL

FOREIGN KEY (department_id) REFERENCES departments(department_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

UNIQUE Constraint

Ensures all values in a column are not duplicated, but allows NULL (in most databases, multiple NULLs are allowed).

SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

UNIQUE vs PRIMARY KEY differences:

Feature	PRIMARY KEY	UNIQUE
NULL values	❌ Not allowed	✅ Allowed
Quantity	Only 1 per table	Can have multiple
Purpose	Unique row identifier	Column value uniqueness

NOT NULL Constraint

Ensures a column cannot store NULL values.

SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

CHECK Constraint

Ensures values in a column satisfy a specified condition.

SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(10,2) CHECK (salary > 0),
    age INT CHECK (age >= 18 AND age <= 65),
    gender CHAR(1) CHECK (gender IN ('M', 'F'))
);

DEFAULT Value

When inserting data without specifying a value for the column, the default value is used automatically.

SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active',
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10,2) DEFAULT 5000.00
);

Constraint Design Principles

Principle	Description
Minimum privilege	Only apply necessary constraints; don't over-restrict
Validate close to source	Define at column level when possible, not table level
Naming convention	Give constraints meaningful names for easier maintenance
Business first	Constraints should reflect business rules, not technical limitations

📝 Basic Syntax

SQL

-- Define constraints when creating a table
CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype NOT NULL,
    column3 datatype UNIQUE,
    column4 datatype CHECK (condition),
    column5 datatype DEFAULT value,
    column6 datatype,
    FOREIGN KEY (column6) REFERENCES other_table(column)
);

-- Add constraint to existing table
ALTER TABLE table_name ADD CONSTRAINT constraint_name
    UNIQUE (column_name);

ALTER TABLE table_name ADD CONSTRAINT constraint_name
    CHECK (condition);

-- Drop constraint
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

💡 Tip:

Constraint names should be unique within the database; recommended format: table_name_column_name_constraint_type

NOT NULL can only be added using ALTER TABLE ... MODIFY, not ADD CONSTRAINT

Adding a constraint to a table with existing data may fail if existing data doesn't satisfy the condition

📌 Examples

Example: Create an Employee Table With Constraints

SQL

CREATE TABLE employees (
    employee_id   INT           PRIMARY KEY,
    first_name    VARCHAR(50)   NOT NULL,
    last_name     VARCHAR(50)   NOT NULL,
    email         VARCHAR(100)  UNIQUE NOT NULL,
    phone         VARCHAR(20),
    hire_date     DATE          NOT NULL DEFAULT CURRENT_DATE,
    salary        DECIMAL(10,2) NOT NULL CHECK (salary > 0),
    department_id INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE SET NULL
);

-- Insert test data
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
VALUES (1, 'John', 'Doe', 'johndoe@company.com', '2026-01-15', 8500.00, 1);

-- The following statements will fail:
-- INSERT ... salary = -100    -- ❌ CHECK constraint
-- INSERT ... first_name NULL  -- ❌ NOT NULL constraint
-- INSERT ... duplicate email  -- ❌ UNIQUE constraint

▶ Try it Yourself

Example: Add and Drop Constraints on an Existing Table

SQL

-- Add CHECK constraint: salary must not exceed 500000
ALTER TABLE employees ADD CONSTRAINT chk_salary_max
    CHECK (salary <= 500000);

-- Add UNIQUE constraint: phone number must be unique
ALTER TABLE employees ADD CONSTRAINT uq_emp_phone
    UNIQUE (phone);

-- Drop constraint
ALTER TABLE employees DROP CONSTRAINT chk_salary_max;

-- Add foreign key constraint
ALTER TABLE employees ADD CONSTRAINT fk_emp_dept
    FOREIGN KEY (department_id) REFERENCES departments(department_id);

▶ Try it Yourself

🎬 Scenario Walkthroughs

Scenario 1: E-Commerce Order System Constraint Design

Design an order table to ensure data integrity.

SQL

-- Products table
CREATE TABLE products (
    product_id   INT           PRIMARY KEY,
    product_name VARCHAR(100)  NOT NULL,
    price        DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock        INT           NOT NULL DEFAULT 0 CHECK (stock >= 0),
    status       VARCHAR(20)   DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'discontinued'))
);

-- Orders table
CREATE TABLE orders (
    order_id     INT           PRIMARY KEY,
    customer_id  INT           NOT NULL,
    order_date   DATE          NOT NULL DEFAULT CURRENT_DATE,
    total_amount DECIMAL(12,2) CHECK (total_amount > 0),
    status       VARCHAR(20)   DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'shipped', 'cancelled')),
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

-- Order items table (composite primary key)
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price > 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Scenario 2: Employee Management System Constraint Migration

Add constraints to an existing table.

SQL

-- Existing table lacks constraints; add them step by step

-- 1. Ensure email format is correct
ALTER TABLE employees ADD CONSTRAINT chk_email_format
    CHECK (email LIKE '%@%.%');

-- 2. Ensure hire date is reasonable
ALTER TABLE employees ADD CONSTRAINT chk_hire_date
    CHECK (hire_date >= '2000-01-01' AND hire_date <= CURRENT_DATE);

-- 3. Ensure department foreign key relationship
ALTER TABLE employees ADD CONSTRAINT fk_emp_dept
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
    ON DELETE SET NULL;

-- 4. View all constraints on a table (SQL Server)
SELECT name, type_desc
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('employees')
  AND type IN ('C', 'F', 'UQ', 'PK');

❓ FAQ

Q: Can a table have multiple UNIQUE constraints? A: Yes. A table can have only one PRIMARY KEY, but can have multiple UNIQUE constraints. For example, both email and phone can each have a UNIQUE constraint.

Q: Do foreign keys reduce performance? A: Foreign keys require additional checks during writes, which slightly impacts write performance. But for most applications, data integrity is more important than the minor performance difference. For high-write scenarios, consider validation at the application layer.

Q: Does the CHECK constraint support subqueries? A: Most databases' CHECK constraints do not support subqueries. If you need cross-table validation, use triggers or implement it at the application layer.

Q: What's the relationship between DEFAULT and NOT NULL? A: If NOT NULL is set without DEFAULT, you must explicitly provide a value when inserting. If DEFAULT is set, you can omit the column during insertion and the default value is used automatically. They are often used together.

📖 Summary

Constraint	Purpose	Allows NULL	Allows Multiple
PRIMARY KEY	Unique row identifier	❌	1 per table
FOREIGN KEY	References another table's primary key	✅	✅
UNIQUE	Column value uniqueness	✅	✅
NOT NULL	Column cannot be null	❌	✅
CHECK	Column value satisfies condition	Depends on definition	✅
DEFAULT	Default value when not specified	-	1 per column

Constraints are the database's "first line of defense," automatically validating data as it's written
Prefer defining constraints at the database level rather than relying solely on the application layer
Name your constraints for easier maintenance and debugging

📝 Exercises

Create a customers table with: primary key, non-null name, unique email, optional phone, registration date (defaults to today), and age (18-120).
Create an orders table with a foreign key referencing customers, order amount must be greater than 0, and status can only be pending, paid, shipped, or completed.
Add a CHECK constraint to the existing employees table to ensure salary is not below the minimum wage standard of 2000.
Think about it: If you delete a department from the departments table while employees in the employees table still belong to that department, what happens with different ON DELETE strategies?

Next Lesson

Next we'll apply everything through Practice: Multi-Table Query Comprehensive — combining JOINs, subqueries, and set operations to solve real business scenarios.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Constraints and Keys

Constraints and Keys

🌍 Real-World Analogy

🎯 Core Concepts

PRIMARY KEY

FOREIGN KEY

UNIQUE Constraint

NOT NULL Constraint

CHECK Constraint

DEFAULT Value

Constraint Design Principles

📝 Basic Syntax

📌 Examples

Example: Create an Employee Table With Constraints

Example: Add and Drop Constraints on an Existing Table

🎬 Scenario Walkthroughs

Scenario 1: E-Commerce Order System Constraint Design

Scenario 2: Employee Management System Constraint Migration

❓ FAQ

📖 Summary

📝 Exercises

Next Lesson

🙏 帮我们做得更好