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.
See also
- Constraints
- Foreign Key Constraint
SHOW CONSTRAINTSRENAME CONSTRAINTDROP CONSTRAINTVALIDATE CONSTRAINTALTER COLUMNCREATE TABLEALTER TABLESHOW JOBS
Was this helpful?
