The ADD CONSTRAINT statement is part of ALTER TABLE and can add the following constraints to columns:

The Primary Key constraint can only be applied through CREATE TABLE. The Default constraint is managed through ALTER COLUMN.

Synopsis

ALTER TABLE IF EXISTS table_name ADD CONSTRAINT name constraint_elem

Required Privileges

The user must have the CREATE privilege on the table.

Parameters

Parameter Description
table_name The name of the table containing the column you want to constrain.
name The name of the constraint, which must be unique to its table and follow these identifier rules.
constraint_elem The Check, Foreign Keys, Unique constraint you want to add.

Adding/changing a Default constraint is done through ALTER COLUMN.

Adding/changing the table’s Primary Key is not supported through ALTER TABLE; it can only be specified during table creation.

Examples

Add the Unique Constraint

Adding the Unique constraint requires that all of a column’s values be distinct from one another (except for NULL values).

> ALTER TABLE orders ADD CONSTRAINT id_customer_unique UNIQUE (id, customer);

Add the Check Constraint

Adding the Check constraint requires that all of a column’s values evaluate to TRUE for a Boolean expression.

> ALTER TABLE orders ADD CONSTRAINT total_0_check CHECK (total > 0);

Add the Foreign Key Constraint

Adding the Foreign Key constraint requires that all of a column’s values are equal to an existing value in the column it references.

> ALTER TABLE orders ADD CONSTRAINT customer_fk FOREIGN KEY (customer) REFERENCEs customers (id);

See Also



Yes No