# ADD CONSTRAINT

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

- `CHECK`
- Foreign key
- `UNIQUE`

Note:

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).

Warning:
`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

- Constraints
- Foreign Key Constraint
- `ALTER COLUMN`
- `CREATE TABLE`
- `ALTER TABLE`