The ADD CONSTRAINT
statement is part of ALTER TABLE
and can add the following constraints to columns:
The ADD CONSTRAINT
statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
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
.
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. |
View 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 existing 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 existing primary index without creating a secondary index.
You can use ADD CONSTRAINT ... PRIMARY KEY
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
. UsingADD CONSTRAINT ... PRIMARY KEY
drops the default primary key and replaces it with a new primary key. - A
DROP CONSTRAINT
statement precedes theADD CONSTRAINT ... PRIMARY KEY
statement, in the same transaction. For an example, see Drop and add the primary key constraint.
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);
In the process of adding the constraint CockroachDB will run a background job to validate existing table data. If CockroachDB finds a row that violates the constraint during the validation step, the ADD CONSTRAINT
statement will fail.
Add constraints to columns created during a transaction
You can add check constraints 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
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 users_pkey PRIMARY KEY (city ASC, id ASC)
| )
(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 vehicles_pkey PRIMARY KEY (city ASC, id ASC),
| )
(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.
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;
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.
> 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 users_pkey PRIMARY KEY (city ASC, id ASC)
| )
(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 existing "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 users_pkey PRIMARY KEY (city ASC, name ASC, id ASC),
| )
(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
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:
- Add a column to the
users
table in the MovR dataset. - Add a
UNIQUE
constraint to that column. - Verify that the index is automatically partitioned for better multi-region performance by using
SHOW INDEXES
andSHOW 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 | users_pkey | false | 1 | region | ASC | false | true
users | users_pkey | false | 2 | id | ASC | false | false
users | users_pkey | false | 3 | city | N/A | true | false
users | users_pkey | false | 4 | name | N/A | true | false
users | users_pkey | false | 5 | address | N/A | true | false
users | users_pkey | false | 6 | credit_card | N/A | true | false
users | users_pkey | false | 7 | email | N/A | true | false
users | user_email_unique | false | 1 | region | ASC | false | true
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 | true
users | users_city_idx | true | 2 | city | ASC | false | false
users | users_city_idx | true | 3 | id | ASC | false | true
(13 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:
- Run a one-time-only validation query to ensure that the existing data in the table satisfies the unique constraint.
- Thereafter, the optimizer will automatically add a "uniqueness check" when necessary to any
INSERT
,UPDATE
, orUPSERT
statement affecting the columns in the unique constraint.
Note that the SQL engine will avoid sending requests to nodes in other regions when it can instead read a value from a unique column that is stored locally. This capability 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 users_pkey PRIMARY KEY (city ASC, id ASC)
);
> 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)
When using DEFAULT gen_random_uuid()
on columns in REGIONAL BY ROW
tables, uniqueness checks on those columns are disabled by default for performance purposes. CockroachDB assumes uniqueness based on the way this column generates UUIDs
. To enable this check, you can modify the sql.optimizer.uniqueness_checks_for_gen_random_uuid.enabled
cluster setting. Note that while there is virtually no chance of a collision occurring when enabling this setting, it is not truly zero.
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, aUNIQUE
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 theUNIQUE
index definition will enforce these semantics and allow you to get better performance onINSERT
s,UPDATE
s, andUPSERT
s, 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 theUNIQUE
(orPRIMARY 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.
Start
cockroach demo
as follows:cockroach demo --geo-partitioned-replicas
Create a multi-region database and an
employees
table. There are three indexes in the table, allUNIQUE
and all partitioned by thecrdb_region
column. The table schema guarantees that bothid
andemail
are globally unique, whiledesk_id
is only unique per region. The indexes onid
andemail
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 forid
,email
, anddesk_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;
In the statement below, we add a new user with the required
id
,email
, anddesk_id
columns. CockroachDB needs to do additional work to enforce global uniqueness for theid
andemail
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 twoconstraint-check
post queries to check the uniqueness of the implicitly partitioned indexesid
andemail
. There is no check needed fordesk_id
(really(crdb_region, desk_id)
), since that constraint is automatically enforced by the explicitly partitioned index we added in theCREATE 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
The statement below updates the user's
email
column. Because the unique index on theemail
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 foremail
since we're not updating any other columns (see theconstraint-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
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 theEXPLAIN
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]