# DROP CONSTRAINT

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.

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.

## See also

- `ADD CONSTRAINT`
- `SHOW CONSTRAINTS`
- `RENAME CONSTRAINT`
- `VALIDATE CONSTRAINT`
- `DROP COLUMN`
- `DROP INDEX`
- `ALTER TABLE`
- `SHOW JOBS`
- 'ALTER PRIMARY KEY'