SQL Constraints Tutorial

Example Table

We will use the following table named employees for our examples:

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        position VARCHAR(50),
        salary DECIMAL(10, 2) CHECK (salary > 0),
        age INT CHECK (age >= 18),
        UNIQUE (name)
    );

    INSERT INTO employees (employee_id, name, position, salary, age) VALUES
    (1, 'John Doe', 'Manager', 60000.00, 45),
    (2, 'Jane Smith', 'Developer', 55000.00, 30),
    (3, 'Emily Johnson', 'Designer', 50000.00, 25);
    

Employees Table

employee_id	name	position	salary	age
1	John Doe	Manager	60000.00	45
2	Jane Smith	Developer	55000.00	30
3	Emily Johnson	Designer	50000.00	25

Using SQL Constraints

SQL constraints are used to specify rules for the data in a table. These are enforced to maintain the integrity and accuracy of the data.

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value.

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        position VARCHAR(50),
        salary DECIMAL(10, 2),
        age INT
    );
    

Result:

Command	CREATE TABLE employees (..., name VARCHAR(50) NOT NULL, ...);
Result	The name column cannot have NULL values.

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        name VARCHAR(50) NOT NULL UNIQUE,
        position VARCHAR(50),
        salary DECIMAL(10, 2),
        age INT
    );
    

Result:

Command	CREATE TABLE employees (..., name VARCHAR(50) NOT NULL UNIQUE, ...);
Result	The name column must have unique values.

CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy a specific condition.

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        position VARCHAR(50),
        salary DECIMAL(10, 2) CHECK (salary > 0),
        age INT CHECK (age >= 18)
    );
    

Result:

Command	CREATE TABLE employees (..., salary DECIMAL(10, 2) CHECK (salary > 0), age INT CHECK (age >= 18));
Result	The salary must be greater than 0. The age must be at least 18.

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table.

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        position VARCHAR(50),
        salary DECIMAL(10, 2),
        age INT
    );
    

Result:

Command	CREATE TABLE employees (..., employee_id INT PRIMARY KEY, ...);
Result	The employee_id column uniquely identifies each record.