DROP CONSTRAINT

On this page

The DROP CONSTRAINT statement is part of ALTER TABLE and removes CHECK and FOREIGN KEY constraints from columns.

PRIMARY KEY constraints can be dropped with DROP CONSTRAINT if an ADD CONSTRAINT statement follows the DROP CONSTRAINT statement in the same transaction.

Tip:

When you change a primary key with ALTER TABLE ... ALTER PRIMARY KEY, the old primary key index becomes a secondary index. If you do not want the old primary key to become a secondary index, use DROP CONSTRAINT/ADD CONSTRAINT to change the primary key.

Note:

For information about removing other constraints, see Constraints: Remove Constraints.

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

Required privileges

The user must have the CREATE privilege on the table.

Parameters

Parameter	Description
`table_name`	The name of the table with the constraint you want to drop.
`name`	The name of the constraint you want to drop.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

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

Drop a foreign key constraint

> SHOW CONSTRAINTS FROM vehicles;

  table_name |  constraint_name  | constraint_type |                         details                         | validated
-------------+-------------------+-----------------+---------------------------------------------------------+------------
  vehicles   | fk_city_ref_users | FOREIGN KEY     | FOREIGN KEY (city, owner_id) REFERENCES users(city, id) |   true
  vehicles   | primary           | PRIMARY KEY     | PRIMARY KEY (city ASC, id ASC)                          |   true
(2 rows)

> ALTER TABLE vehicles DROP CONSTRAINT fk_city_ref_users;

> SHOW CONSTRAINTS FROM vehicles;

  table_name | constraint_name | constraint_type |            details             | validated
-------------+-----------------+-----------------+--------------------------------+------------
  vehicles   | primary         | PRIMARY KEY     | PRIMARY KEY (city ASC, id ASC) |   true
(1 row)

Drop and add a primary key constraint

When you change a primary key with ALTER TABLE ... ALTER PRIMARY KEY, the old primary key index becomes a secondary index. If you do not want the old primary key to become a secondary index when changing a primary key, you can use DROP CONSTRAINT/ADD CONSTRAINT instead.

Suppose that you want to add name to the composite primary key of the users table.

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

Pricing

Contact us

Sign In