DROP CONSTRAINT

On this page Carat arrow pointing down

The DROP CONSTRAINT statement is part of ALTER TABLE and removes Check and Foreign Key constraints from columns.

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

ALTER TABLE IF EXISTS table_name DROP CONSTRAINT IF EXISTS name CASCADE RESTRICT

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.

Example

icon/buttons/copy
> SHOW CONSTRAINTS FROM orders;
+--------+---------------------------+-------------+-----------+----------------+
| Table  |           Name            |    Type     | Column(s) |    Details     |
+--------+---------------------------+-------------+-----------+----------------+
| orders | fk_customer_ref_customers | FOREIGN KEY | customer  | customers.[id] |
| orders | primary                   | PRIMARY KEY | id        | NULL           |
+--------+---------------------------+-------------+-----------+----------------+
icon/buttons/copy
> ALTER TABLE orders DROP CONSTRAINT fk_customer_ref_customers;
ALTER TABLE
icon/buttons/copy
> SHOW CONSTRAINTS FROM orders;
+--------+---------+-------------+-----------+---------+
| Table  |  Name   |    Type     | Column(s) | Details |
+--------+---------+-------------+-----------+---------+
| orders | primary | PRIMARY KEY | id        | NULL    |
+--------+---------+-------------+-----------+---------+
Note:
You cannot drop the primary constraint, which indicates your table's Primary Key.

See also


Yes No
On this page

Yes No