SQL Constraints

SQL constraints are rules enforced on data columns in a table. They restrict the types of data that can be inserted, updated, or deleted, ensuring the overall accuracy, reliability, and data integrity of the database.

If an operation violates a constraint rule, the database engine aborts the transaction and throws an error.

Integrity Constraint Categories

Constraints fall into four main categories depending on what level of data integrity they protect:

Overview of Core SQL Constraints

Constraint	Description	Scope Level
`NOT NULL`	Enforces that a column cannot store `NULL` (missing) values.	Column
`UNIQUE`	Guarantees that all values stored in a column (or group of columns) are distinct.	Column & Table
`PRIMARY KEY`	Uniquely identifies each record in a table. Combines `NOT NULL` and `UNIQUE`.	Column & Table
`FOREIGN KEY`	Prevents actions that would destroy links between tables; enforces referential integrity.	Column & Table
`CHECK`	Ensures that all values in a column satisfy a specific boolean logical condition.	Column & Table
`DEFAULT`	Assigns a pre-set default value to a column when no value is provided during insertion.	Column
`CREATE INDEX`	Used to create and retrieve data from the database very quickly (covered in dedicated chapter).	Table

Constraint Implementation Syntax

Constraints can be applied when creating a table via CREATE TABLE or added later using ALTER TABLE.

1. Column-Level vs. Table-Level Constraints

Column-Level Constraints: Defined inline alongside the specific column declaration.
Table-Level Constraints: Defined after all columns are declared. Required when creating composite constraints (spanning multiple columns).

CREATE TABLE Employees (
    -- Column-level constraints
    EmployeeID  INT PRIMARY KEY,
    FirstName   VARCHAR(50) NOT NULL,
    Email       VARCHAR(100) UNIQUE,
    Salary      DECIMAL(10, 2) DEFAULT 3000.00,
    Age         INT CHECK (Age >= 18),
    
    -- Table-level constraint (Explicitly named constraint)
    CONSTRAINT CHK_Salary_Range CHECK (Salary >= 1000.00 AND Salary <= 500000.00)
);

Key Rules & Usage

1. `NOT NULL`

Ensures every row contains a valid value for the column.

ALTER TABLE Employees
ALTER COLUMN FirstName VARCHAR(50) NOT NULL;

2. `UNIQUE`

Prevents duplicate values across rows. Unlike PRIMARY KEY, a UNIQUE column can accept NULL values (behavior varies: SQL Server permits one NULL; PostgreSQL and MySQL permit multiple NULLs).

ALTER TABLE Employees
ADD CONSTRAINT UQ_Employee_Email UNIQUE (Email);

3. `PRIMARY KEY`

A table can have only one primary key. It can consist of a single column or multiple columns (composite primary key).

-- Composite Primary Key (Table-level required)
CREATE TABLE OrderItems (
    OrderID    INT,
    ProductID  INT,
    Quantity   INT NOT NULL,
    PRIMARY KEY (OrderID, ProductID)
);

4. `FOREIGN KEY`

Matches a column (or group of columns) in a child table to the PRIMARY KEY or UNIQUE key of a parent table.

CREATE TABLE Orders (
    OrderID     INT PRIMARY KEY,
    CustomerID  INT,
    OrderDate   DATE,
    CONSTRAINT FK_Orders_Customers 
        FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
        ON DELETE CASCADE
);

Dropping Existing Constraints

To delete an active constraint rule, reference its name using ALTER TABLE:

-- PostgreSQL / SQL Server / Oracle
ALTER TABLE Employees
DROP CONSTRAINT CHK_Salary_Range;

-- MySQL (Requires specific keyword for keys/foreign keys)
ALTER TABLE Employees
DROP CHECK CHK_Salary_Range;