The ADD CONSTRAINT
statement is part of ALTER TABLE
and can add the following constraints to columns:
The PRIMARY KEY
can only be applied through CREATE TABLE
. The DEFAULT
and NOT NULL
constraints are managed through ALTER COLUMN
.
This command can be combined with other ALTER TABLE
commands in a single statement. For a list of commands that can be combined, see ALTER TABLE
. For a demonstration, see Add and rename columns atomically.
Synopsis
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. |
constraint_name |
The name of the constraint, which must be unique to its table and follow these identifier rules. |
constraint_elem |
The CHECK , foreign key, 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. |
Viewing schema changes
This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS
.
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 check_id_non_zero CHECK (id > 0);
Check constraints can be added to columns that were created earlier in the transaction. For example:
> BEGIN;
> ALTER TABLE customers ADD COLUMN gdpr_status STRING;
> ALTER TABLE customers ADD CONSTRAINT check_gdpr_status CHECK (gdpr_status IN ('yes', 'no', 'unknown'));
> COMMIT;
BEGIN
ALTER TABLE
ALTER TABLE
COMMIT
The entire transaction will be rolled back, including any new columns that were added, in the following cases:
- If an existing column is found containing values that violate the new constraint.
- If a new column has a default value or is a computed column that would have contained values that violate the new constraint.
Add the foreign key constraint with CASCADE
To add a foreign key constraint, use the steps shown below.
Given two tables, customers
and orders
:
> SHOW CREATE customers;
table_name | create_statement
------------+----------------------------------------------------
customers | CREATE TABLE customers ( +
| id INT8 NOT NULL, +
| name STRING NOT NULL, +
| address STRING NULL, +
| CONSTRAINT "primary" PRIMARY KEY (id ASC),+
| FAMILY "primary" (id, name, address) +
| )
(1 row)
> SHOW CREATE orders;
table_name | create_statement
------------+----------------------------------------------------------------------------------------------------------------
orders | CREATE TABLE orders ( +
| id INT8 NOT NULL, +
| customer_id INT8 NULL, +
| status STRING NOT NULL, +
| CONSTRAINT "primary" PRIMARY KEY (id ASC), +
| FAMILY "primary" (id, customer_id, status), +
| CONSTRAINT check_status CHECK (status IN ('open':::STRING, 'complete':::STRING, 'cancelled':::STRING))+
| )
(1 row)
You can include a foreign key action to specify what happens when a foreign key is updated or deleted.
Using ON DELETE CASCADE
will ensure that when the referenced row is deleted, all dependent objects are also deleted.
CASCADE
does not list the objects it drops or updates, so it should be used with caution.
> ALTER TABLE orders ADD CONSTRAINT customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;
An index on the referencing columns is automatically created for you when you add a foreign key constraint to an empty table, if an appropriate index does not already exist. You can see it using SHOW INDEXES
:
> SHOW INDEXES FROM orders;
table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
------------+-------------------------------+------------+--------------+-------------+-----------+---------+----------
orders | primary | f | 1 | id | ASC | f | f
orders | orders_auto_index_customer_fk | t | 1 | customer_id | ASC | f | f
orders | orders_auto_index_customer_fk | t | 2 | id | ASC | f | t
(3 rows)
Adding a foreign key for a non-empty table without an appropriate index will fail, since foreign key columns must be indexed. For more information about the requirements for creating foreign keys, see Rules for creating foreign keys.