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.
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. UsingADD CONSTRAINT ... PRIMARY KEYdrops the default primary key and replaces it with a new primary key. - A
DROP CONSTRAINTstatement precedes theADD CONSTRAINT ... PRIMARY KEYstatement, in the same transaction. For an example, see Drop and add the primary key constraint below.
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
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.
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)
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 CONSTRAINTSRENAME CONSTRAINTDROP CONSTRAINTVALIDATE CONSTRAINTALTER COLUMNCREATE TABLEALTER TABLESHOW JOBS- 'ALTER PRIMARY KEY'
Was this helpful?
