On this page Show
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. SynopsisRequired privilegesThe user must have the CREATE privilege on the table. Parameters
View schema changesThis schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS. Changing primary keys with ADD CONSTRAINT ... PRIMARY KEYWhen 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: ExamplesSetupThe 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: 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); 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 transactionYou 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 To add a foreign key constraint, use the steps shown below. Given two tables, users and vehicles, without foreign key constraints: 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) 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. 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; Drop and add a primary key constraintSuppose 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 tableIn 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: 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: 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:
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 tablesTo 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'); 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 tablesIn 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:
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:
To illustrate the different behavior of explicitly vs. implicitly partitioned indexes, we will perform the following tasks:
See also
|