ADD CONSTRAINT

On this page

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.

Synopsis

Required privileges

The user must have the CREATE privilege on the table.

Parameters

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

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.

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

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.

> ALTER TABLE vehicles ADD CONSTRAINT users_fk FOREIGN KEY (city, owner_id) REFERENCES users (city, id) ON DELETE CASCADE;

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.

Add a unique index to a REGIONAL BY ROW table

New in v21.1: In multi-region deployments, most users should use REGIONAL BY ROW tables instead of explicit index partitioning. When you add an index to a REGIONAL BY ROW table, it is automatically partitioned on the crdb_region column. Explicit index partitioning is not required.

While CockroachDB process an ADD REGION or DROP REGION statement on a particular database, creating or modifying an index will throw an error. Similarly, all ADD REGION and DROP REGION statements will be blocked while an index is being modified on a REGIONAL BY ROW table within the same database.

This example assumes you have a simulated multi-region database running on your local machine following the steps described in Low Latency Reads and Writes in a Multi-Region Cluster. It shows how a UNIQUE index is partitioned, but it's similar to how all indexes are partitioned on REGIONAL BY ROW tables.

To show how the automatic partitioning of indexes on REGIONAL BY ROW tables works, we will:

1. Add a column to the users table in the MovR dataset.
2. Add a UNIQUE constraint to that column.
3. Verify that the index is automatically partitioned for better multi-region performance by using SHOW INDEXES and SHOW PARTITIONS.

First, add a column and its unique constraint. We'll use email since that is something that should be unique per user.

ALTER TABLE users ADD COLUMN email STRING;

ALTER TABLE users ADD CONSTRAINT user_email_unique UNIQUE (email);

Next, issue the SHOW INDEXES statement. You will see that the implicit region column that was added when the table was converted to regional by row is now indexed:

SHOW INDEXES FROM users;

  table_name |    index_name     | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+-------------------+------------+--------------+-------------+-----------+---------+-----------
  users      | primary           |   false    |            1 | region      | ASC       |  false  |  false
  users      | primary           |   false    |            2 | id          | ASC       |  false  |  false
  users      | user_email_unique |   false    |            1 | region      | ASC       |  false  |  false
  users      | user_email_unique |   false    |            2 | email       | ASC       |  false  |  false
  users      | user_email_unique |   false    |            3 | id          | ASC       |  false  |   true
  users      | users_city_idx    |    true    |            1 | region      | ASC       |  false  |  false
  users      | users_city_idx    |    true    |            2 | city        | ASC       |  false  |  false
  users      | users_city_idx    |    true    |            3 | id          | ASC       |  false  |   true
(8 rows)

Next, issue the SHOW PARTITIONS statement. The output below (which is edited for length) will verify that the unique index was automatically partitioned for you. It shows that the user_email_unique index is now partitioned by the database regions europe-west1, us-east1, and us-west1.

SHOW PARTITIONS FROM TABLE users;

  database_name | table_name | partition_name | column_names |       index_name        | partition_value |  ...
----------------+------------+----------------+--------------+-------------------------+-----------------+-----
  movr          | users      | europe-west1   | region       | users@user_email_unique | ('europe-west1')|  ...
  movr          | users      | us-east1       | region       | users@user_email_unique | ('us-east1')    |  ...
  movr          | users      | us-west1       | region       | users@user_email_unique | ('us-west1')    |  ...
  ...

To ensure that the uniqueness constraint is enforced properly across regions when rows are inserted, or the email column of an existing row is updated, the database needs to do the following additional work when indexes are partitioned as shown above:

1. Run a one-time-only validation query to ensure that the existing data in the table satisfies the unique constraint.
2. Thereafter, the optimizer will automatically add a "uniqueness check" when necessary to any INSERT, UPDATE, or UPSERT statement affecting the columns in the unique constraint.

Note that there is a performance benefit for queries that select a single row (e.g., SELECT * FROM users WHERE email = 'anemailaddress@gmail.com'). If 'anemailaddress@gmail.com' is found in the local region, there is no need to search remote regions. This feature, whereby the SQL engine will avoid sending requests to nodes in other regions when it can read a value from a unique column that is stored locally, is known as locality optimized search.

Using DEFAULT gen_random_uuid() in REGIONAL BY ROW tables

To auto-generate unique row identifiers in REGIONAL BY ROW tables, use the UUID column with the gen_random_uuid() function as the default value:

> CREATE TABLE users (
        id UUID NOT NULL DEFAULT gen_random_uuid(),
        city STRING NOT NULL,
        name STRING NULL,
        address STRING NULL,
        credit_card STRING NULL,
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
        FAMILY "primary" (id, city, name, address, credit_card)
);

> INSERT INTO users (name, city) VALUES ('Petee', 'new york'), ('Eric', 'seattle'), ('Dan', 'seattle');

> SELECT * FROM users;

                   id                  |   city   | name  | address | credit_card
+--------------------------------------+----------+-------+---------+-------------+
  cf8ee4e2-cd74-449a-b6e6-a0fb2017baa4 | new york | Petee | NULL    | NULL
  2382564e-702f-42d9-a139-b6df535ae00a | seattle  | Eric  | NULL    | NULL
  7d27e40b-263a-4891-b29b-d59135e55650 | seattle  | Dan   | NULL    | NULL
(3 rows)

Using implicit vs. explicit index partitioning in REGIONAL BY ROW tables

In REGIONAL BY ROW tables, all indexes are partitioned on the region column (usually called crdb_region).

These indexes can either include or exclude the partitioning key (crdb_region) as the first column in the index definition:

• If crdb_region is included in the index definition, a UNIQUE index will enforce uniqueness on the set of columns, just like it would in a non-partitioned table.
• If crdb_region is excluded from the index definition, that serves as a signal that CockroachDB should enforce uniqueness on only the columns in the index definition.

In the latter case, the index alone cannot enforce uniqueness on columns that are not a prefix of the index columns, so any time rows are inserted or updated in a REGIONAL BY ROW table that has an implicitly partitioned UNIQUE index, the optimizer must add uniqueness checks.

Whether or not to explicitly include crdb_region in the index definition depends on the context:

• If you only need to enforce uniqueness at the region level, then including crdb_region in the UNIQUE index definition will enforce these semantics and allow you to get better performance on INSERTs, UPDATEs, and UPSERTs, since there will not be any added latency from uniqueness checks.
• If you need to enforce global uniqueness, you should not include crdb_region in the UNIQUE (or PRIMARY KEY) index definition, and the database will automatically ensure that the constraint is enforced.

To illustrate the different behavior of explicitly vs. implicitly partitioned indexes, we will perform the following tasks:

• Create a schema that includes an explicitly partitioned index, and an implicitly partitioned index.
• Check the output of several queries using EXPLAIN to show the differences in behavior between the two.

1. Start cockroach demo as follows:

   cockroach demo --geo-partitioned-replicas
   

2. Create a multi-region database and an employees table. There are three indexes in the table, all UNIQUE and all partitioned by the crdb_region column. The table schema guarantees that both id and email are globally unique, while desk_id is only unique per region. The indexes on id and email are implicitly partitioned, while the index on (crdb_region, desk_id) is explicitly partitioned. UNIQUE indexes can only directly enforce uniqueness on all columns in the index, including partitioning columns, so each of these indexes enforce uniqueness for id, email, and desk_id per region, respectively.

   CREATE DATABASE multi_region_test_db PRIMARY REGION "europe-west1" REGIONS "us-west1", "us-east1";
   

   USE multi_region_test_db;
   

   CREATE TABLE employee (
     id INT PRIMARY KEY,
     email STRING UNIQUE,
     desk_id INT,
     UNIQUE (crdb_region, desk_id)
   ) LOCALITY REGIONAL BY ROW;
   

3. In the statement below, we add a new user with the required id, email, and desk_id columns. CockroachDB needs to do additional work to enforce global uniqueness for the id and email columns, which are implicitly partitioned. This additional work is in the form of "uniqueness checks" that the optimizer adds as part of mutation queries.

   EXPLAIN INSERT INTO employee VALUES (1, 'joe@example.com', 1);
   

   The EXPLAIN output below shows that the optimizer has added two constraint-check post queries to check the uniqueness of the implicitly partitioned indexes id and email. There is no check needed for desk_id (really (crdb_region, desk_id)), since that constraint is automatically enforced by the explicitly partitioned index we added in the CREATE TABLE statement above.

                                            info
   --------------------------------------------------------------------------------------
     distribution: local
     vectorized: true
   
     • root
     │
     ├── • insert
     │   │ into: employee(id, email, desk_id, crdb_region)
     │   │
     │   └── • buffer
     │       │ label: buffer 1
     │       │
     │       └── • values
     │             size: 5 columns, 1 row
     │
     ├── • constraint-check
     │   │
     │   └── • error if rows
     │       │
     │       └── • lookup join (semi)
     │           │ table: employee@primary
     │           │ equality: (lookup_join_const_col_@15, column1) = (crdb_region,id)
     │           │ equality cols are key
     │           │ pred: column10 != crdb_region
     │           │
     │           └── • cross join
     │               │ estimated row count: 3
     │               │
     │               ├── • values
     │               │     size: 1 column, 3 rows
     │               │
     │               └── • scan buffer
     │                     label: buffer 1
     │
     └── • constraint-check
         │
         └── • error if rows
             │
             └── • lookup join (semi)
                 │ table: employee@employee_email_key
                 │ equality: (lookup_join_const_col_@25, column2) = (crdb_region,email)
                 │ equality cols are key
                 │ pred: (column1 != id) OR (column10 != crdb_region)
                 │
                 └── • cross join
                     │ estimated row count: 3
                     │
                     ├── • values
                     │     size: 1 column, 3 rows
                     │
                     └── • scan buffer
                           label: buffer 1
   

4. The statement below updates the user's email column. Because the unique index on the email column is implicitly partitioned, the optimizer must perform a uniqueness check.

   EXPLAIN UPDATE employee SET email = 'joe1@exaple.com' WHERE id = 1;
   

   In the EXPLAIN output below, the optimizer performs a uniqueness check for email since we're not updating any other columns (see the constraint-check section).

                                                     info
   --------------------------------------------------------------------------------------------------------
     distribution: local
     vectorized: true
   
     • root
     │
     ├── • update
     │   │ table: employee
     │   │ set: email
     │   │
     │   └── • buffer
     │       │ label: buffer 1
     │       │
     │       └── • render
     │           │ estimated row count: 1
     │           │
     │           └── • union all
     │               │ estimated row count: 1
     │               │ limit: 1
     │               │
     │               ├── • scan
     │               │     estimated row count: 1 (100% of the table; stats collected 1 minute ago)
     │               │     table: employee@primary
     │               │     spans: [/'us-east1'/1 - /'us-east1'/1]
     │               │
     │               └── • scan
     │                     estimated row count: 1 (100% of the table; stats collected 1 minute ago)
     │                     table: employee@primary
     │                     spans: [/'europe-west1'/1 - /'europe-west1'/1] [/'us-west1'/1 - /'us-west1'/1]
     │
     └── • constraint-check
         │
         └── • error if rows
             │
             └── • lookup join (semi)
                 │ table: employee@employee_email_key
                 │ equality: (lookup_join_const_col_@18, email_new) = (crdb_region,email)
                 │ equality cols are key
                 │ pred: (id != id) OR (crdb_region != crdb_region)
                 │
                 └── • cross join
                     │ estimated row count: 3
                     │
                     ├── • values
                     │     size: 1 column, 3 rows
                     │
                     └── • scan buffer
                           label: buffer 1
   

5. If we only update the user's desk_id as shown below, no uniqueness checks are needed, since the index on that column is explicitly partitioned (it's really (crdb_region, desk_id)).

   EXPLAIN UPDATE employee SET desk_id = 2 WHERE id = 1;
   

   Because no uniqueness check is needed, there is no constraint-check section in the EXPLAIN output.

                                                 info
   ------------------------------------------------------------------------------------------------
     distribution: local
     vectorized: true
   
     • update
     │ table: employee
     │ set: desk_id
     │ auto commit
     │
     └── • render
         │ estimated row count: 1
         │
         └── • union all
             │ estimated row count: 1
             │ limit: 1
             │
             ├── • scan
             │     estimated row count: 1 (100% of the table; stats collected 2 minutes ago)
             │     table: employee@primary
             │     spans: [/'us-east1'/1 - /'us-east1'/1]
             │
             └── • scan
                   estimated row count: 1 (100% of the table; stats collected 2 minutes ago)
                   table: employee@primary
                   spans: [/'europe-west1'/1 - /'europe-west1'/1] [/'us-west1'/1 - /'us-west1'/1]
   

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'

Was this helpful?