Constraints Explained: CHECK, UNIQUE, NOT NULL

1. Why constraints matter despite application validation

A constraint is a rule the database itself enforces, regardless of which application or process is currently writing to it. Many developers rely exclusively on validation in the application code and consider database constraints redundant. That thinking overlooks the fact that a database is rarely written to by only a single application in practice. Batch jobs, admin scripts, data imports, other microservices and manual fixes through a SQL client all access the same tables directly, without going through the main application's validation logic.

A constraint at the database level acts as a last line of defense that applies regardless of the access path. Even with a single application and airtight validation, a constraint protects against bugs in that exact validation logic, against race conditions between concurrent requests, and against future developers who accidentally remove or incorrectly port a validation rule. The combination of application validation for good error messages and database constraints for guaranteed integrity is not a contradiction, it is the only robust strategy.

The three most important constraint types for everyday practice are NOT NULL, UNIQUE and CHECK. Each covers a different class of rules: required fields, uniqueness and arbitrary logical conditions. The following sections show each type with concrete syntax and the pitfalls that occur most often in practice.

2. NOT NULL in detail

NOT NULL is the simplest and at the same time the most commonly underestimated constraint. It prevents a column from taking the value NULL, forcing every INSERT and UPDATE to supply a concrete value for that column. Without NOT NULL, a column accepts NULL by default, which leads to unexpected results in downstream queries: NULL values are ignored by aggregate functions like SUM or AVG, and with comparison operators like equals or not equals they yield neither true nor false, but unknown, which can subtly distort filter conditions.

The rule of thumb for NOT NULL is: any column that must always have a value in business terms should carry NOT NULL, even if the application currently always sets a value. This rule protects against future code paths that accidentally insert a NULL value, for instance after a refactoring that removes a required field from a form without adjusting the database column. For optional fields, where NULL means "not present" rather than "unknown" in business terms, NULL is the correct modeling choice, not a design flaw.

CREATE TABLE customer (
    customer_id   BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    email         VARCHAR(255) NOT NULL,
    first_name    VARCHAR(100) NOT NULL,
    last_name     VARCHAR(100) NOT NULL,
    -- optional: not every customer has a middle name
    middle_name   VARCHAR(100) NULL,
    -- optional: filled only after the account is verified
    verified_at   TIMESTAMP NULL,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Adding NOT NULL to an existing column requires a default
-- for all currently NULL rows first
UPDATE customer SET first_name = 'Unknown' WHERE first_name IS NULL;
ALTER TABLE customer MODIFY first_name VARCHAR(100) NOT NULL;

3. UNIQUE constraints and composite uniqueness

A UNIQUE constraint ensures that no value, or no combination of values across several columns, occurs more than once in a table. Unlike a primary key, UNIQUE allows exactly one NULL value per column in most database systems, or in PostgreSQL even several NULL values, because NULL is never considered equal to another NULL under SQL's three-valued logic. A UNIQUE constraint on email prevents duplicate customer accounts with the same email address, regardless of which path was used to insert the record.

Composite UNIQUE constraints across several columns are a frequently overlooked tool. A constraint on (tenant_id, sku) ensures that a product code is unique within a tenant, while different tenants are allowed to use the same product code independently of each other. This kind of uniqueness cannot be represented by two separate single-column UNIQUE constraints, it requires a single constraint spanning both columns together, since the combination, not each column on its own, is supposed to be unique.

CREATE TABLE product (
    product_id   BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    tenant_id     INT UNSIGNED NOT NULL,
    sku           VARCHAR(64) NOT NULL,
    name          VARCHAR(255) NOT NULL,
    -- composite uniqueness: sku is only unique per tenant
    CONSTRAINT uq_product_tenant_sku UNIQUE (tenant_id, sku)
);

-- Single-column unique constraint, added after table creation
ALTER TABLE customer
    ADD CONSTRAINT uq_customer_email UNIQUE (email);

4. CHECK constraints in detail

A CHECK constraint covers any logical condition that refers to one or several columns of the same row. Typical use cases are value ranges such as a positive price, a fixed value list such as a status field, or relationships between columns such as an end date that must not precede the start date. Up to MySQL 8.0.16, CHECK constraints were syntactically accepted but silently ignored, a common reason for unexpectedly invalid data in older MySQL versions. Since MySQL 8.0.16, as well as in practically every other relational database, CHECK constraints are enforced correctly.

CHECK constraints can be combined across several columns, which makes them a powerful tool for business invariants that would otherwise only be representable in trigger logic. A constraint like CHECK (end_date IS NULL OR end_date >= start_date) enforces chronological order without the application having to repeat this check on every single write. It is important to formulate CHECK constraints so they explicitly account for NULL values, since a condition involving NULL evaluates to neither true nor false in SQL, but unknown, and the constraint then effectively does not apply.

CREATE TABLE product (
    product_id   BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name          VARCHAR(255) NOT NULL,
    price         DECIMAL(10,2) NOT NULL,
    status        VARCHAR(20) NOT NULL,
    start_date    DATE NOT NULL,
    end_date      DATE NULL,
    CONSTRAINT chk_price_positive CHECK (price >= 0),
    CONSTRAINT chk_status_values
        CHECK (status IN ('draft', 'active', 'discontinued')),
    -- explicitly handle NULL: an open end date is always valid
    CONSTRAINT chk_date_order
        CHECK (end_date IS NULL OR end_date >= start_date)
);

5. Named constraints and maintainability

If a constraint is defined without an explicit name, the database automatically assigns a generated name, which is often cryptic and differs between database systems. Such a name makes it considerably harder to later drop, alter or identify the constraint in an error message. The convention CONSTRAINT constraint_name CHECK/UNIQUE/... should therefore be used consistently in every table definition, with a descriptive name that reveals table, column and constraint type, such as chk_product_price_positive or uq_customer_email.

Named constraints pay off especially during migrations. An ALTER TABLE DROP CONSTRAINT needs the exact name, and without a consistent naming scheme developers first have to look up that name in the system catalog before making a change. A fixed naming convention, documented project-wide and enforced in code reviews, saves considerable effort in practice at every subsequent schema change.

6. Constraints and migrations on existing data

Applying a constraint to a table with existing data afterwards fails immediately if even a single row violates the new rule. Before every ALTER TABLE ADD CONSTRAINT, the existing data therefore has to be cleaned up first, either through an UPDATE that fixes invalid values, or through a targeted analysis of which rows violate the rule, before even deciding how to handle them. A SELECT COUNT before the migration, counting violations, prevents nasty surprises from a failed ALTER TABLE on a large table.

For very large tables, adding a CHECK constraint in PostgreSQL with the NOT VALID option can initially happen without a full table scan, the constraint then immediately applies to new and modified rows, while existing rows are only checked with a separate VALIDATE CONSTRAINT command that does not require an exclusive lock. This two-step approach significantly reduces downtime for migrations on tables with many millions of rows.

-- PostgreSQL: add the constraint without a full table scan first,
-- validate existing rows in a second, non-blocking step
ALTER TABLE product
    ADD CONSTRAINT chk_price_positive CHECK (price >= 0) NOT VALID;

ALTER TABLE product
    VALIDATE CONSTRAINT chk_price_positive;

-- Count violations before attempting the constraint on any system
SELECT COUNT(*) AS violations
FROM product
WHERE price < 0;

7. Error handling: catching constraint violations

A constraint violation results in an error the application must explicitly handle, instead of passing it through to the end user as a generic server error. Every database system returns a specific error code for this: MySQL uses SQLSTATE 23000 for integrity violations, PostgreSQL differentiates more precisely between 23502 for NOT NULL, 23505 for UNIQUE and 23514 for CHECK violations. These codes let the application react in a targeted way, for instance with a comprehensible error message like "This email address is already registered" instead of an opaque database error.

A common anti-pattern is checking with a SELECT before every INSERT whether a value already exists, in order to preemptively avoid the constraint violation. This approach is prone to race conditions, because another process can insert the same value between the SELECT and the INSERT. The more robust approach is to attempt the INSERT directly and catch the resulting constraint violation in a targeted way in case of failure, either in the application code or with database-side constructs like INSERT ... ON CONFLICT in PostgreSQL or INSERT ... ON DUPLICATE KEY UPDATE in MySQL.

-- PostgreSQL: let the UNIQUE constraint do the work, no prior SELECT
INSERT INTO customer (email, first_name, last_name)
VALUES ('anna@example.com', 'Anna', 'Schmidt')
ON CONFLICT (email) DO NOTHING;

-- MySQL: equivalent pattern for the same race-condition-free insert
INSERT INTO customer (email, first_name, last_name)
VALUES ('anna@example.com', 'Anna', 'Schmidt')
ON DUPLICATE KEY UPDATE first_name = VALUES(first_name);

8. Vendor differences for constraints

Although NOT NULL, UNIQUE and CHECK are part of the SQL standard, behavior differs in detail between database systems. PostgreSQL allows several NULL values in the same column for UNIQUE constraints, because every NULL is treated as independently unknown. MySQL follows the same behavior in practice for UNIQUE indexes, but diverges on other details, such as the historical non-enforcement of CHECK before version 8.0.16, already mentioned above.

SQLite checks CHECK constraints by default, but is overall considerably more tolerant with data types than other systems, which makes CHECK constraints an even more important tool there for ensuring type correctness that SQLite itself does not strictly enforce. For cross-project portability, it is advisable to limit constraint definitions to the smallest common denominator of the SQL standard and to use vendor-specific extensions such as partial indexes for conditional uniqueness deliberately and with documentation.

9. Performance impact of constraints

A constraint is not free safety, every check costs computation time on every INSERT and UPDATE. NOT NULL and CHECK constraints that only look at the current row are very cheap, because they need no additional access to other rows or tables. UNIQUE constraints are more expensive, because the database has to maintain an index internally to check uniqueness efficiently, which causes additional storage and write overhead on every INSERT.

Foreign key constraints are usually the most expensive, because every check requires a lookup in the referenced table. In practice, the benefit of guaranteed data integrity almost always clearly outweighs the cost, especially because a missing constraint does not eliminate the cost, it merely shifts it into the future in the form of data repairs and debugging effort. Only for very high-frequency bulk imports, where data quality is already guaranteed beforehand, does it pay off to temporarily disable constraints during the import followed by re-validation afterward.

10. Summary

NOT NULL, UNIQUE and CHECK are the three fundamental constraints used to enforce data integrity directly in the database, regardless of which application or process is writing. NOT NULL protects required fields, UNIQUE prevents duplicate values individually or in combination, CHECK covers any logical condition up to relationships between several columns. Named constraints with descriptive names significantly ease maintenance and migrations, while the two-step NOT VALID plus VALIDATE strategy in PostgreSQL minimizes downtime on large tables.

Application validation and database constraints are not mutually exclusive, they complement each other: the application delivers comprehensible error messages for end users, the database guarantees integrity that survives bugs, race conditions and alternative access paths. Anyone who applies constraints consistently reduces debugging effort and prevents data inconsistencies that would otherwise only surface months later.

11. FAQ: Constraints Explained: CHECK, UNIQUE, NOT NULL