# ADD CONSTRAINT

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

- `UNIQUE`
- `CHECK`
- `FOREIGN KEY`

To add a primary key constraint to a table, you should explicitly define the primary key at table creation. To replace an existing primary key, you can use `ADD CONSTRAINT ... PRIMARY KEY`. For details, see Changing primary keys with `ADD CONSTRAINT ... PRIMARY KEY`.

The `DEFAULT` and `NOT NULL` constraints are managed through `ALTER COLUMN`.

Tip:

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

## Changing primary keys with `ADD CONSTRAINT ... PRIMARY KEY`

New in v20.1: When you change a primary key with `ALTER TABLE ... ALTER PRIMARY KEY`, the old primary key index becomes a secondary index. The secondary index created by `ALTER PRIMARY KEY` takes up node memory and can slow down write performance to a cluster. If you do not have queries that filter on the primary key that you are replacing, you can use `ADD CONSTRAINT` to replace the old primary index without creating a secondary index.

`ADD CONSTRAINT ... PRIMARY KEY` can be used to add a primary key to an existing table if one of the following is true:

- No primary key was explicitly defined at table creation. In this case, the table is created with a default primary key on `rowid`. Using `ADD CONSTRAINT ... PRIMARY KEY` drops the default primary key and replaces it with a new primary key.
- A `DROP CONSTRAINT` statement precedes the `ADD CONSTRAINT ... PRIMARY KEY` statement, in the same transaction. For an example, see Drop and add the primary key constraint below.

Note:

`ALTER TABLE ... ADD PRIMARY KEY` is an alias for `ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY`.

## Examples

### Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` dataset preloaded:

    $ cockroach demo

### 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 users ADD CONSTRAINT id_name_unique UNIQUE (id, name);

### 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 rides ADD CONSTRAINT check_revenue_positive CHECK (revenue >= 0);

Check constraints can be added to columns that were created earlier in the transaction. For example:

    > BEGIN;
    > ALTER TABLE users ADD COLUMN is_owner STRING;
    > ALTER TABLE users ADD CONSTRAINT check_is_owner CHECK (is_owner IN ('yes', 'no', 'unknown'));
    > COMMIT;

    BEGIN
    ALTER TABLE
    ALTER TABLE
    COMMIT

Note:

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, `users` and `vehicles`, without foreign key constraints:

    > SHOW CREATE users;

      table_name |                      create_statement
    -------------+--------------------------------------------------------------
      users      | CREATE TABLE users (
                 |     id UUID NOT NULL,
                 |     city VARCHAR NOT NULL,
                 |     name VARCHAR NULL,
                 |     address VARCHAR NULL,
                 |     credit_card VARCHAR NULL,
                 |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
                 |     FAMILY "primary" (id, city, name, address, credit_card)
                 | )
    (1 row)

    > SHOW CREATE vehicles;

      table_name |                                       create_statement
    -------------+------------------------------------------------------------------------------------------------
      vehicles   | CREATE TABLE vehicles (
                 |     id UUID NOT NULL,
                 |     city VARCHAR NOT NULL,
                 |     type VARCHAR NULL,
                 |     owner_id UUID NULL,
                 |     creation_time TIMESTAMP NULL,
                 |     status VARCHAR NULL,
                 |     current_location VARCHAR NULL,
                 |     ext JSONB NULL,
                 |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
                 |     FAMILY "primary" (id, city, type, owner_id, creation_time, status, current_location, ext)
                 | )
    (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.

Warning:

`CASCADE` does not list the objects it drops or updates, so it should be used with caution.

    > ALTER TABLE vehicles ADD CONSTRAINT users_fk FOREIGN KEY (city, owner_id) REFERENCES users (city, 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 vehicles;

      table_name |          index_name          | non_unique | seq_in_index | column_name | direction | storing | implicit
    -------------+------------------------------+------------+--------------+-------------+-----------+---------+-----------
      vehicles   | primary                      |   false    |            1 | city        | ASC       |  false  |  false
      vehicles   | primary                      |   false    |            2 | id          | ASC       |  false  |  false
      vehicles   | vehicles_auto_index_users_fk |    true    |            1 | city        | ASC       |  false  |  false
      vehicles   | vehicles_auto_index_users_fk |    true    |            2 | owner_id    | ASC       |  false  |  false
      vehicles   | vehicles_auto_index_users_fk |    true    |            3 | id          | ASC       |  false  |   true
    (5 rows)

Note:

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.

### Drop and add a primary key constraint

Suppose that you want to add `name` to the composite primary key of the `users` table, without creating a secondary index of the existing primary key.

    > SHOW CREATE TABLE users;

      table_name |                      create_statement
    -------------+--------------------------------------------------------------
      users      | CREATE TABLE users (
                 |     id UUID NOT NULL,
                 |     city VARCHAR NOT NULL,
                 |     name VARCHAR NULL,
                 |     address VARCHAR NULL,
                 |     credit_card VARCHAR NULL,
                 |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
                 |     FAMILY "primary" (id, city, name, address, credit_card)
                 | )
    (1 row)

First, add a `NOT NULL` constraint to the `name` column with `ALTER COLUMN`.

    > ALTER TABLE users ALTER COLUMN name SET NOT NULL;

Then, in the same transaction, `DROP` the old `"primary"` constraint and `ADD` the new one:

    > BEGIN;
    > ALTER TABLE users DROP CONSTRAINT "primary";
    > ALTER TABLE users ADD CONSTRAINT "primary" PRIMARY KEY (city, name, id);
    > COMMIT;

    NOTICE: primary key changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes

    > SHOW CREATE TABLE users;

      table_name |                          create_statement
    -------------+---------------------------------------------------------------------
      users      | CREATE TABLE users (
                 |     id UUID NOT NULL,
                 |     city VARCHAR NOT NULL,
                 |     name VARCHAR NOT NULL,
                 |     address VARCHAR NULL,
                 |     credit_card VARCHAR NULL,
                 |     CONSTRAINT "primary" PRIMARY KEY (city ASC, name ASC, id ASC),
                 |     FAMILY "primary" (id, city, name, address, credit_card)
                 | )
    (1 row)

Using `ALTER PRIMARY KEY` would have created a `UNIQUE` secondary index called `users_city_id_key`. Instead, there is just one index for the primary key constraint.

## See also

- Constraints
- Foreign Key Constraint
- `SHOW CONSTRAINTS`
- `RENAME CONSTRAINT`
- `DROP CONSTRAINT`
- `VALIDATE CONSTRAINT`
- `ALTER COLUMN`
- `CREATE TABLE`
- `ALTER TABLE`
- `SHOW JOBS`
- 'ALTER PRIMARY KEY'