The ADD CONSTRAINT statement is part of ALTER TABLE and can add the following constraints to columns:
The PRIMARY KEY and NOT NULL constraints can only be applied through CREATE TABLE. The DEFAULT constraint is managed through ALTER COLUMN.
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
Whenever you initiate a schema change, CockroachDB registers it as a job, which you can view 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 total_0_check CHECK (total > 0);
Add the foreign key constraint with CASCADE
Before you can add the foreign key constraint to columns, the columns must already be indexed. If they are not already indexed, use CREATE INDEX to index them and only then use the ADD CONSTRAINT statement to add the Foreign Key constraint to the columns.
For example, let's say you have two tables, orders and customers:
> SHOW CREATE customers;
+-----------+-------------------------------------------------+
| Table | CreateTable |
+-----------+-------------------------------------------------+
| customers | CREATE TABLE customers ( |
| | id INT 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 | CreateTable |
+--------+-------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE orders ( |
| | id INT NOT NULL, |
| | customer_id INT 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)
To ensure that each value in the orders.customer_id column matches a unique value in the customers.id column, you want to add the Foreign Key constraint to orders.customer_id. So you first create an index on orders.customer_id:
> CREATE INDEX ON orders (customer_id);
Then you add the foreign key constraint.
You can include a foreign key action to specify what happens when a foreign key is updated or deleted.
In this example, let's use ON DELETE CASCADE (i.e., when referenced row is deleted, all dependent objects are also deleted).
CASCADE does not list objects it drops or updates, so it should be used cautiously.> ALTER TABLE orders ADD CONSTRAINT customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;
If you had tried to add the constraint before indexing the column, you would have received an error:
pq: foreign key requires an existing index on columns ("customer_id")
See also
Was this helpful?
