The DROP CONSTRAINT
statement is part of ALTER TABLE
and removes CHECK
and FOREIGN KEY
constraints from columns.
New in v20.1: PRIMARY KEY
constraints can be dropped with DROP CONSTRAINT
if an ADD CONSTRAINT
statement follows the DROP CONSTRAINT
statement in the same transaction.
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.
For information about removing other constraints, see Constraints: Remove Constraints.
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.