add-constraint.md

title	summary	toc
ADD CONSTRAINT	Use the ADD CONSTRAINT statement to add constraints to columns.	true

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.

{% include {{ page.version.version }}/sql/combine-alter-table-commands.md %}

Synopsis

{% include {{ page.version.version }}/sql/diagrams/add_constraint.html %}

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

{% include {{ page.version.version }}/misc/schema-change-view-job.md %}

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.

{{site.data.alerts.callout_info}} ALTER TABLE ... ADD PRIMARY KEY is an alias for ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY. {{site.data.alerts.end}}

Examples

{% include {{page.version.version}}/sql/movr-statements.md %}

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).

{% include copy-clipboard.html %}

> 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.

{% include copy-clipboard.html %}

> 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:

{% include copy-clipboard.html %}

> 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

{{site.data.alerts.callout_info}} 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. {{site.data.alerts.end}}

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:

{% include copy-clipboard.html %}

> 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)

{% include copy-clipboard.html %}

> 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.

{{site.data.alerts.callout_danger}} CASCADE does not list the objects it drops or updates, so it should be used with caution. {{site.data.alerts.end}}

{% include copy-clipboard.html %}

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

{{site.data.alerts.callout_info}} 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. {{site.data.alerts.end}}

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.

{% include copy-clipboard.html %}

> 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.

{% include copy-clipboard.html %}

> ALTER TABLE users ALTER COLUMN name SET NOT NULL;

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

{% include copy-clipboard.html %}

> 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

{% include copy-clipboard.html %}

> 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 CONSTRAINTS
• RENAME CONSTRAINT
• DROP CONSTRAINT
• VALIDATE CONSTRAINT
• ALTER COLUMN
• CREATE TABLE
• ALTER TABLE
• SHOW JOBS
• 'ALTER PRIMARY KEY'