SQL constraint

A SQL constraint is a rule the database enforces on the values that can appear in a column or table. Constraints sit at the boundary between schema and data — declared in DDL but enforced on every DML operation. They turn the database into an active participant in data quality, rather than a passive store.

The constraints worth recognizing by name:

• NOT NULL — the column cannot be left blank. Every row must provide a value.
• UNIQUE — no two rows can have the same value in this column (or combination of columns). Note: most databases allow multiple NULLs in a UNIQUE column because NULL = NULL is itself NULL, not true.
• PRIMARY KEY — marks a column (or combination) as the table’s primary key. Implies UNIQUE and NOT NULL, and a table can have at most one.
• FOREIGN KEY — marks a column as a reference to another table’s primary key. The database refuses inserts that would point to nonexistent rows.
• CHECK — a boolean expression that every row must satisfy (e.g. CHECK (age >= 0)). Standard SQL; enforced in MySQL 8.0+, PostgreSQL, SQLite, SQL Server.
• DEFAULT — supplies a value when none is given on insert. Strictly speaking a column attribute rather than a constraint, but usually grouped here.

Auto-incrementing primary keys aren’t a constraint in standard SQL — they’re a column attribute, and every dialect spells them differently: AUTO_INCREMENT in MySQL, SERIAL or GENERATED ... AS IDENTITY in PostgreSQL, AUTOINCREMENT in SQLite, IDENTITY in SQL Server. Every time a row is inserted without specifying a value for that column, the database picks the next integer automatically.

Constraints can be declared inline with a column or as a separate clause at the end of the CREATE TABLE:

CREATE TABLE students (
    student_id   INT NOT NULL AUTO_INCREMENT,
    email        VARCHAR(255) UNIQUE,
    family_name  VARCHAR(255) NOT NULL,
    PRIMARY KEY (student_id)
);

Added later with ALTER TABLE:

ALTER TABLE students
ADD UNIQUE KEY (phone);

If we try to violate a constraint, the database refuses the operation and raises an error. Inserting a row with a duplicate primary-key value gives Duplicate entry ... for key 'PRIMARY'. Inserting a row with a foreign-key value that doesn’t exist in the referenced table gives a foreign-key violation. These errors are the database doing its job — better to fail loudly at insert time than to discover later that the table has impossible data.

For other constraint-like features: ON DELETE CASCADE governs what happens to dependent rows when a referenced row is deleted; the ENUM data type (MySQL-specific) acts as an implicit constraint by allowing only specific values.