ALTER TABLE ADD CONSTRAINT

Adds a CHECK, NOT NULL, PRIMARY KEY, or UNIQUE constraint to an existing Delta table.

Category: ddl

Syntax

ALTER TABLE <table> ADD CONSTRAINT <name> CHECK (<expression>)
ALTER TABLE <table> ADD CONSTRAINT <name> NOT NULL (<column>)

Description

## Overview Adds a named constraint to an existing Delta table. CHECK constraints are actively enforced by the Delta write path; PRIMARY KEY and UNIQUE constraints are informational and stored as metadata. ## Behavior - CHECK constraints are persisted as Delta table properties with the key pattern `delta.constraints.<name>`. They are evaluated at write time, and any row that violates the expression causes the write transaction to fail. - NOT NULL constraints specified via this command are internally converted to CHECK constraints with the expression `<column> IS NOT NULL`. - PRIMARY KEY and UNIQUE constraints are advisory. They are stored as table properties but do not enforce uniqueness or non-null guarantees at write time. They serve as hints for query optimization and documentation. - The CONSTRAINT keyword is optional. `ADD CHECK (expr)` is also accepted, in which case a default constraint name is generated. - Adding a constraint does not validate existing data. If existing rows violate the constraint expression, subsequent reads continue to return those rows, but new writes that violate it will be rejected. ## Compatibility CHECK constraints are part of the Delta Lake protocol and are compatible with any Delta-aware writer that respects table properties. PRIMARY KEY and UNIQUE constraints are stored as metadata and are compatible with all readers.

Parameters

Name	Type	Description
`table`		Fully qualified table name or registered table name. The table must be registered in the current session.
`constraint`		The constraint to add.

Examples

-- Add a CHECK constraint ensuring positive prices
ALTER TABLE warehouse.sales.products
ADD CONSTRAINT price_positive CHECK (price > 0);

-- Add a NOT NULL constraint on a column
ALTER TABLE warehouse.sales.customers
ADD CONSTRAINT email_required NOT NULL (email);

-- Add a range-based CHECK constraint
ALTER TABLE warehouse.sales.orders
ADD CONSTRAINT valid_quantity CHECK (quantity >= 1 AND quantity <= 10000);

-- Add an informational primary key
ALTER TABLE warehouse.sales.products
ADD CONSTRAINT pk_products PRIMARY KEY (product_id);

-- Add a multi-column CHECK constraint
ALTER TABLE warehouse.sales.orders
ADD CONSTRAINT valid_dates CHECK (ship_date >= order_date);

Pitfalls

Adding a CHECK constraint does not retroactively validate existing rows. Data that was written before the constraint may violate it.
PRIMARY KEY and UNIQUE constraints are not enforced at write time. They are informational only and should not be relied upon for data integrity guarantees.
Constraint names must be unique within the table. Adding a constraint with a name that already exists will overwrite the previous constraint expression.