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.
See also
ADD CONSTRAINTSHOW CONSTRAINTSRENAME CONSTRAINTVALIDATE CONSTRAINTDROP COLUMNDROP INDEXALTER TABLESHOW JOBS- 'ALTER PRIMARY KEY'
Was this helpful?
