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