Relational database management systems enforce data accuracy and consistency through integrity constraints. These rules validate records during insertion, updates, and deletions, preventing invalid or malformed data from persisting. MySQL implements several constraint types, with the CHECK condition becoming fully operational starting with version 8.0.

Constraint Scope and Definition Levels

Constraints operate at two distinct scopes:

• Column-level constraints are defined inline with the column specification. They apply directly to a single column and do not require explicit column references.
• Table-level constraints are declared separately from column definitions. They can span multiple columns and require explicit column references within the constraint definition.

Defining Constraints During Table Creation

The following example demonstrates a column-level implementation for an inventory tracking schema:

CREATE TABLE warehouse_inventory (
    inventory_id INT PRIMARY KEY AUTO_INCREMENT,
    item_sku VARCHAR(20) UNIQUE NOT NULL,
    item_name VARCHAR(100) NOT NULL,
    department VARCHAR(50) CHECK (department IN ('Logistics', 'Retail', 'Manufacturing')),
    unit_price DECIMAL(10,2) CHECK (unit_price > 0),
    last_audit_date DATE,
    storage_status CHAR(1) DEFAULT 'A'
);

Atttempting to insert data that violates these rules triggers specific error codes. For instance, supplying a NULL value for item_name generates a 1048 error, while a duplicate item_sku returns a 1062 conflict. Providing a negative unit_price or an invalid department triggers constraint violation notices. Omitting the primary key value or passing DEFAULT and NULL for the inventory_id column correctly utilizes the auto-increment mechanism. Note that transaction rollbacks or syntax errors may consume sequence numbers, resulting in non-sequential IDs, which is standard behavior for surrogate keys.

To implement the same logic using table-level syntax, constraint names are explicitly assigned, improving schema maintainability:

CREATE TABLE warehouse_inventory_v2 (
    inventory_id INT AUTO_INCREMENT,
    item_sku VARCHAR(20) NOT NULL,
    item_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    unit_price DECIMAL(10,2),
    last_audit_date DATE,
    storage_status CHAR(1) DEFAULT 'A',
    CONSTRAINT pk_inventory PRIMARY KEY (inventory_id),
    CONSTRAINT uk_inventory_sku UNIQUE (item_sku),
    CONSTRAINT chk_inventory_dept CHECK (department IN ('Logistics', 'Retail', 'Manufacturing')),
    CONSTRAINT chk_inventory_price CHECK (unit_price > 0)
);

Modifying Existing Tables

Constraints can also be applied to pre-existing schemas using ALTER TABLE. Note that AUTO_INCREMENT requires the column to be indexed as a key first.

CREATE TABLE legacy_inventory (
    inventory_id INT,
    item_sku VARCHAR(20),
    item_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    unit_price DECIMAL(10,2)
);

ALTER TABLE legacy_inventory ADD CONSTRAINT pk_legacy PRIMARY KEY (inventory_id);
ALTER TABLE legacy_inventory MODIFY inventory_id INT AUTO_INCREMENT;
ALTER TABLE legacy_inventory ADD CONSTRAINT uk_legacy_sku UNIQUE (item_sku);
ALTER TABLE legacy_inventory ADD CONSTRAINT chk_legacy_price CHECK (unit_price > 0);

-- Verify schema updates
DESCRIBE legacy_inventory;

Core Constraint Types

1. Primary Key (PK) The primary key uniquely identifies each row in a table. It implicitly combines NOT NULL and UNIQUE properties. Primary keys enable efficient index-based lookups. Single-column keys support both definition levels, while composite keys require table-level syntax.

2. Not Null (NN) This constraint prohibits NULL values in a specific column. Any insertion or update attempting to set a restricted column to NULL will fail. Non-null rules are strictly applied at the column level.

-- Applying NN to an existing column
ALTER TABLE legacy_inventory MODIFY department VARCHAR(50) NOT NULL;
-- Removing NN
ALTER TABLE legacy_inventory MODIFY department VARCHAR(50) NULL;

3. Unique (UK) Unique constraints guarantee that all values in a column, or a combination of columns, remain distinct across the entire dataset. Both column-level and table-level declarations are supported. Multiple unique constraints can coexist on a single table.

4. Check (CK) Check constraints evaluate a boolean expression before committing data. Records failing the condition are rejected. Native support for CHECK clauses was introduced in MySQL 8.0. Prior versions parsed the syntax but ignored enforcement.

-- Valid condition example
quantity INT CHECK (quantity BETWEEN 0 AND 10000)

5. Default Value When an insertion omits a specific column, the DEFAULT clause populates it with a predefined value. This constraint is exclusive defined at the column level.

6. Auto Increment The AUTO_INCREMENT attribute automatically generates sequential numeric identifiers. Each table permits only one auto-incrementing column, which must also be indexed (typically as a primary key). Omitting the key constraint during definition triggers an Incorrect table definition error. Values typical start at 1 and increment by 1.

-- Enabling auto-increment on an existing primary key
ALTER TABLE legacy_inventory MODIFY inventory_id INT AUTO_INCREMENT;
-- Disabling auto-increment
ALTER TABLE legacy_inventory MODIFY inventory_id INT;

Executing DESCRIBE or querying the information_schema verifies the applied constraints and table structure.