Constraints and Integrity

UNIQUE Constraint

Ensures that all values in a column (Or a set of columns) are distinct and no duplicates are allowed. It is a way to enforce data integrity and prevent duplicate entries in critical fields, such as email addresses, usernames, and id numbers.

Single-Column UNIQUE

A column with a UNIQUE constraint cannot have the same value in multiple rows.

Ensure unique email addresses

Given a sample table definition:

-- Email must be unique across all rows and attempting to insert a duplicate email will result in an error.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

-- This will work.

INSERT INTO users (user_id, username, email) VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO users (user_id, username, email) VALUES (2, 'Bob', 'bob@example.com');

-- This will fail.
INSERT INTO users (user_id, username, email) VALUES (3, 'Charlie', 'alice@example.com');
```sql

Multi-Column (Composite) UNIQUE

You can enforce uniqueness across multiple columns together. The combination of values must be unique.

Ensure unique combination of product and batch

Given a sample table definition:

-- product_id and batch_number combination must be unique.
-- The same batch number can exist for different products, but not for the same product.

CREATE TABLE product_batches (
    product_id INT,
    batch_number VARCHAR(50),
    manufacture_date DATE,
    UNIQUE (product_id, batch_number)
);

INSERT INTO product_batches (product_id, batch_number, manufacture_date)
VALUES (1, 'BATCH001', '2025-11-01');  -- OK

INSERT INTO product_batches (product_id, batch_number, manufacture_date)
VALUES (2, 'BATCH001', '2025-11-02');  -- OK (Different product)

INSERT INTO product_batches (product_id, batch_number, manufacture_date)
VALUES (1, 'BATCH001', '2025-11-03');  -- ERROR (Duplicate combination)

tip

UNIQUE can be applied during table creation or added later using ALTER TABLE.
A table can have multiple UNIQUE constraints, unlike PRIMARY KEY which is limited to one.
NULLs are allowed in UNIQUE columns in most databases, but multiple NULLs are treated differently depending on the system. To avoid this issue just set the field to be NOT NULL.

CHECK Constraint

Ensures that the values in a column meet a specific condition. It helps maintain data integrity by restricting invalid or out-of-range data from being inserted into a table.

Single-Column CHECK

A CHECK constraint can enforce rules on a single column.

Restrict employee age

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    age INT,
    CHECK (age >= 18 AND age <= 65)
);

Explanation:
- Only allows ages between 18 and 65.
- Any attempt to insert age 17 and below and age 70 and above will fail.

Multi-Column CHECK

CHECK constraint can also reference multiple columns for more complex rules.

Ensure start_date is before end_date

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100),
    start_date DATE,
    end_date DATE,
    CHECK (start_date < end_date)
);

Explanation:
- Prevents inserting a project where the start date is after the end date.
- Ensures logical consistency in the table. Simple way to enforce the start date before end date logic.

Using CHECK with ENUM-like restrictions

You can restrict a column to a set of allowed values.

Restrict product status

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    status VARCHAR(20),
    CHECK (status IN ('Available', 'Out of Stock', 'Discontinued'))
);

Explanation:
- Only allows the three predefined values ('Available', 'Out of Stock', 'Discontinued') for status.
- Any other value will result in an error.

tip

CHECK constraints are enforced at insert or update time.
Helps maintain valid and consistent data.
Enforcing the check via database rather than application level.

NOT NULL Constraint

Ensures that a column cannot have a NULL value. This is one of the simplest and most commonly used constraints to enforce mandatory data entry in a table.

Single-Column NOT NULL

A column with the NOT NULL constraint must always have a value when inserting or updating a row.

Example 1

Given a sample table definition:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    age INT
);

emp_name cannot be NULL.
If you try to insert a row without a name, the database will reject it.

-- Valid
INSERT INTO employees (emp_id, emp_name, age) VALUES (1, 'Alice', 30);

-- Invalid (emp_name is missing)
INSERT INTO employees (emp_id, age) VALUES (2, 25); -- ERROR

Multiple NOT NULL Columns

You can have multiple columns that are required.

Example 1

Given a sample table definition:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2)
);

Both customer_id and order_date are mandatory.
The field amount can be left NULL if unknown.

-- Valid
INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES (1, 1, '2025-11-12', 30.00);

-- Invalid (customer_id and order_date are missing)
INSERT INTO orders (order_id, amount) VALUES (1, 30.00); -- ERROR

tip

Ensures data integrity by preventing missing values.
Often used for primary key columns, which must always have a value.
Can be combined with other constraints like UNIQUE or CHECK for stronger validation.

CHECK, NOT NULL, and UNIQUE - Combined Example

We want to create a table to track products in a warehouse. The table should enforce:
- NOT NULL: Certain fields like product_name and price must always have values.
- UNIQUE: Each product_code must be unique.
- CHECK: The price and quantity columns cannot be negative.

Product Inventory Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,                       -- Always unique
    product_code VARCHAR(20) NOT NULL UNIQUE,         -- Must exist and be unique
    product_name VARCHAR(100) NOT NULL,               -- Must exist
    category VARCHAR(50),
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),  -- Price cannot be negative
    quantity INT DEFAULT 0 CHECK (quantity >= 0)      -- Quantity cannot be negative
);

NOT NULL: product_code, product_name, and price cannot be empty.
UNIQUE: product_code ensures no two products share the same code.
CHECK:
- price >= 0 ensures valid product pricing.
- quantity >= 0 ensures inventory cannot have negative numbers.

-- Valid
INSERT INTO products (product_id, product_code, product_name, category, price, quantity)
VALUES (1, 'PRD001', 'Laptop', 'Electronics', 1200.00, 10);

-- Invalid: duplicate product_code
INSERT INTO products (product_id, product_code, product_name, category, price, quantity)
VALUES (2, 'PRD001', 'Monitor', 'Electronics', 300.00, 5); -- ERROR

-- Invalid: negative price
INSERT INTO products (product_id, product_code, product_name, category, price, quantity)
VALUES (3, 'PRD002', 'Keyboard', 'Electronics', -50.00, 15); -- ERROR

-- Invalid: NULL product_name
INSERT INTO products (product_id, product_code, product_name, category, price, quantity)
VALUES (4, 'PRD003', NULL, 'Electronics', 20.00, 50); -- ERROR

ON DELETE CASCADE / ON UPDATE CASCADE

In relational databases, foreign keys establish relationships between tables. One is the parent table while the other is child table.
The CASCADE options define how changes in a parent table affect related rows in a child table.
Helps maintain referential integrity automatically.

Option	Effect on Child Table
ON DELETE CASCADE	Deletes dependent child rows when the parent is deleted
ON UPDATE CASCADE	Updates dependent child rows when the parent key changes

ON DELETE CASCADE

Automatically deletes child rows when the corresponding parent row is deleted.
Useful for maintaining referential integrity without manual cleanup. This means the developer does not have to delete the child rows before the parent row.

Customers and Orders

-- Create tables
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

-- Populate tables
INSERT INTO customers VALUES (1, 'Alice');
INSERT INTO orders VALUES (101, 1, '2025-11-19');
INSERT INTO orders VALUES (102, 1, '2025-11-20');

-- Delete parent
DELETE FROM customers WHERE customer_id = 1;

orders with customer_id = 1 are automatically deleted.

ON UPDATE CASCADE

Automatically updates child rows when the parent key changes.
Useful when primary keys might change which is rare.

Update customer Id

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
);

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

-- Populate tables
INSERT INTO customers VALUES (1, 'Alice');
INSERT INTO orders VALUES (101, 1, '2025-11-19');
INSERT INTO orders VALUES (102, 1, '2025-11-20');

-- Update customer Id
UPDATE customers SET customer_id = 10 WHERE customer_id = 1;

All orders referencing customer_id = 1 are updated to 10.

CASCADE Warning

Cascading deletes or updates can remove or change many rows unexpectedly. Use with caution.

UNIQUE Constraint​

Single-Column UNIQUE​

Multi-Column (Composite) UNIQUE​

CHECK Constraint​

Single-Column CHECK​

Multi-Column CHECK​

Using CHECK with ENUM-like restrictions​

NOT NULL Constraint​

Single-Column NOT NULL​

Multiple NOT NULL Columns​

CHECK, NOT NULL, and UNIQUE - Combined Example​

ON DELETE CASCADE / ON UPDATE CASCADE​

ON DELETE CASCADE​

ON UPDATE CASCADE​