Constraints & Keys

Lesson 2 of 16 Intermediate Interactive

Constraints enforce data integrity.

Constraint Types

PRIMARY KEY � unique identifier
FOREIGN KEY � references another table
NOT NULL � cannot be NULL
UNIQUE � no duplicates
DEFAULT � default value
CHECK � validation rule

Syntax

MYSQL

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10,2) CHECK (total >= 0),
    status VARCHAR(20) DEFAULT "pending",
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

Table Constraints

SQL

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    code VARCHAR(20) UNIQUE,
    total DECIMAL(10,2) CHECK (total >= 0),
    status ENUM("pending", "paid", "shipped") DEFAULT "pending",
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

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

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

-- Remove constraint
ALTER TABLE users DROP INDEX uc_email;

Try It Yourself

Practice

Exercise

Create a table with PRIMARY KEY, UNIQUE, and FOREIGN KEY.

Answer

CREATE TABLE reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    rating INT CHECK (rating BETWEEN 1 AND 5),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Quick Quiz

ON DELETE CASCADE does what?

Prevents deletion

Deletes child rows

Sets NULL

Raises error

Automatically deletes child rows when parent is deleted.

Interview Questions

Both enforce uniqueness. PRIMARY KEY cannot be NULL and only one per table. UNIQUE allows NULLs and multiple per table.

CREATE & DROP

Tutorial Complete

Back to MySQL