ADD CONSTRAINT

On this page Carat arrow pointing down
Warning:
CockroachDB v20.2 is no longer supported. For more details, see the Release Support Policy.

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

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

ALTER TABLE IF EXISTS table_name ADD CONSTRAINT constraint_name constraint_elem

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

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:

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:

icon/buttons/copy
$ 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).

icon/buttons/copy
> 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.

icon/buttons/copy
> 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:

icon/buttons/copy
> 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:

icon/buttons/copy
> 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)
icon/buttons/copy
> 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.

icon/buttons/copy
> ALTER TABLE vehicles ADD CONSTRAINT users_fk FOREIGN KEY (city, owner_id) REFERENCES users (city, id) ON DELETE CASCADE;
Note:

New in v20.2: By default, referenced columns must be in the same database as the referencing foreign key column. To enable cross-database foreign key references, set the sql.cross_db_fks.enabled cluster setting to true.

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.

icon/buttons/copy
> 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.

icon/buttons/copy
> ALTER TABLE users ALTER COLUMN name SET NOT NULL;

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

icon/buttons/copy
> 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
icon/buttons/copy
> 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


Yes No
On this page

Yes No