SQL Constraints

What are constraints?

A constraint is a rule you declare on a table so the database itself refuses to store data that breaks the rule. Instead of trusting every application to check values before writing them, you push the guarantee down into the database, where it is applied to every insert, update, and delete no matter which program made the request. If a statement would violate a constraint, the database rejects it and reports an error rather than saving bad data.

Constraints can be defined when you first build a table with CREATE TABLE, or added to an existing table later with ALTER TABLE. You can attach them to a single column or to a combination of columns.

The common constraint types

• NOT NULL - the column must always contain a value and can never be left empty.
• UNIQUE - every value in the column (or set of columns) must be different from all others.
• PRIMARY KEY - a combination of NOT NULL and UNIQUE that identifies each row uniquely.
• FOREIGN KEY - a value must match an existing value in another table, linking the two together.
• CHECK - a value must satisfy a boolean condition that you write.
• DEFAULT - supplies a preset value when no value is provided during an insert.

CREATE TABLE Employees (
    EmployeeID   INT NOT NULL,
    Email        VARCHAR(255) UNIQUE,
    FullName     VARCHAR(120) NOT NULL,
    Salary       DECIMAL(10, 2) CHECK (Salary >= 0),
    Department   VARCHAR(60) DEFAULT 'Unassigned',
    PRIMARY KEY (EmployeeID)
);

In the example above, EmployeeID cannot be empty and must be unique because it is the primary key, Email must be unique, Salary can never be negative, and Department falls back to the text 'Unassigned' whenever an insert omits it.

ALTER TABLE Employees
ADD CONSTRAINT chk_salary CHECK (Salary >= 0);