How to remove constraints from my MySQL table?

Question

I want to remove constraints from my table. My query is:

ALTER TABLE `tbl_magazine_issue` 
DROP CONSTRAINT `FK_tbl_magazine_issue_mst_users`

But I got an error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint FK_tbl_magazine_issue_mst_users' at line 1

It is worth noting that if you created a CHECK constraint, there is no need to drop it because no actual constraint is created. You can select from information_schema.table_constraints to verify, and you can even run the add constraint over and over again without any error. MySQL does not support CHECK constraints but allows the SQL intended to create them (without actually creating the constraints). — ADTC
– ADTC, Commented Apr 24, 2016 at 13:41
feature request: Foreign keys: DROP CONSTRAINT as a compatibility alias for DROP FOREIGN KEY — Ben Creasy
– Ben Creasy, Commented Dec 30, 2017 at 16:57

Bohemian · Accepted Answer · 2013-09-30 10:42:04Z

493

Mysql has a special syntax for dropping foreign key constraints:

ALTER TABLE tbl_magazine_issue
  DROP FOREIGN KEY FK_tbl_magazine_issue_mst_users

edited Sep 30, 2013 at 10:42

answered Jan 2, 2013 at 12:35

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jared Beck Over a year ago

Postgres, MSSQL, and Oracle all have alter table .. drop constraint. MySQL is the odd one out, it seems.

Lealo Over a year ago

It says it cannot drop "column2" beacuses it is needed in a foreign key constraint. When I do as you say I get "Cant DROP column2; check that column/key exists"

Lealo Over a year ago

Alright I got it, the foreign is a seperate thing just connecting the column to other tables columns. Mine had a standard name given to it. Also now I now that you can drop foreign keys safetely without the column itself being dropped

Rich Harding Over a year ago

Wellington Lorindo's solution could be seen as more correct, because simply removing the foreign key will not remove the related index. Of course the index may have been created separately, but if it was created as a consequence of adding the foreign key in the first place, then it will not be removed simply by dropping the foreign key.

SNS - Web et Informatique Over a year ago

Same problem -> need to pass foreign key id with a specific syntax, for me : ALTER TABLE users DROP FOREIGN KEY users_ibfk_1 -> watch syntax [table name]_ibfk_1 -> this foreign key was given to me by the error rendred by my SQL resquest of suppress

|

Wellington Lorindo · Accepted Answer · 2013-08-19 15:40:52Z

71

I had the same problem and I got to solve with this code:

ALTER TABLE `table_name` DROP FOREIGN KEY `id_name_fk`;
ALTER TABLE `table_name` DROP INDEX  `id_name_fk`;

answered Aug 19, 2013 at 15:40

Wellington Lorindo

2,46522 silver badges21 bronze badges

3 Comments

Rich Harding Over a year ago

This could be seen as more correct than the accepted solution, because simply removing the foreign key will not remove the index. Of course the index may have been created separately, but if it was created as a consequence of adding the foreign key in the first place then it will not be removed simply by dropping the foreign key.

Frank Forte Over a year ago

As a single command: ALTER TABLE table_name DROP FOREIGN KEY IF EXISTS id_name_fk, DROP INDEX IF EXISTS id_name_fk;

Ruslan Stelmachenko Over a year ago

@FrankForte the "single command" doesn't work as MySQL (at least 5.7.28 and AFAIK 8.0 too) doesn't support DROP FOREIGN KEY IF EXISTS syntax. MariaDB supports it.

Lothar · Accepted Answer · 2013-01-02 12:35:42Z

28

There is no such thing as DROP CONSTRAINT in MySQL. In your case you could use DROP FOREIGN KEY instead.

answered Jan 2, 2013 at 12:35

Lothar

5271 gold badge5 silver badges19 bronze badges

Comments

Robert Knight · Accepted Answer · 2015-01-06 10:58:33Z

17

If the constraint is not a foreign key, eg. one added using 'UNIQUE CONSTRAINT (colA, colB)' then it is an index that can be dropped using ALTER TABLE ... DROP INDEX ...

answered Jan 6, 2015 at 10:58

Robert Knight

2,91728 silver badges22 bronze badges

Comments

jbobbins · Accepted Answer · 2017-05-04 17:34:06Z

13

To add a little to Robert Knight's answer, since the title of the post itself doesn't mention foreign keys (and since his doesn't have complete code samples and since SO's comment code blocks don't show as well as the answers' code blocks), I'll add this for unique constraints. Either of these work to drop the constraint:

ALTER TABLE `table_name` DROP KEY `uc_name`;

or

ALTER TABLE `table_name` DROP INDEX `uc_name`;

answered May 4, 2017 at 17:34

jbobbins

1,3014 gold badges19 silver badges28 bronze badges

Comments

roelleor · Accepted Answer · 2018-02-20 13:12:34Z

9

Also nice, you can temporarily disable all foreign key checks from a mysql database: SET FOREIGN_KEY_CHECKS=0; And to enable it again: SET FOREIGN_KEY_CHECKS=1;

answered Feb 20, 2018 at 13:12

roelleor

4536 silver badges13 bronze badges

Comments

Lukasz Szozda · Accepted Answer · 2020-01-20 21:38:45Z

5

The simplest way to remove constraint is to use syntax ALTER TABLE tbl_name DROP CONSTRAINT symbol; introduced in MySQL 8.0.19:

As of MySQL 8.0.19, ALTER TABLE permits more general (and SQL standard) syntax for dropping and altering existing constraints of any type, where the constraint type is determined from the constraint name

ALTER TABLE tbl_magazine_issue DROP CONSTRAINT FK_tbl_magazine_issue_mst_users;

db<>fiddle demo

answered Jan 20, 2020 at 21:38

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Comments

piscator · Accepted Answer · 2018-12-06 16:04:58Z

4

Some ORM's or frameworks use a different naming convention for foreign keys than the default FK_[parent table]_[referenced table]_[referencing field], because they can be altered.

Laravel for example uses [parent table]_[referencing field]_foreign as naming convention. You can show the names of the foreign keys by using this query, as shown here:

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_SCHEMA = '<database>' AND REFERENCED_TABLE_NAME = '<table>';

Then remove the foreign key by running the before mentioned DROP FOREIGN KEY query and its proper name.

edited Dec 6, 2018 at 16:04

answered Nov 25, 2016 at 9:48

piscator

8,8495 gold badges26 silver badges35 bronze badges

Comments

Markus Barthlen · Accepted Answer · 2018-08-30 15:33:55Z

3

For those that come here using MariaDB:

Note that MariaDB allows DROP CONSTRAINT statements in general, for example for dropping check constraints:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

https://mariadb.com/kb/en/library/alter-table/

answered Aug 30, 2018 at 15:33

Markus Barthlen

3894 silver badges15 bronze badges

1 Comment

Frank Forte Over a year ago

Yes, this is for constraints that are within the same table, for example CONSTRAINT CHECK(a > b). For foreign key constraints, it appears you still need the DROP FOREIGN KEY syntax, at least in MariaDB version 10.2

sonium · Accepted Answer · 2022-05-27 07:34:12Z

3

In MySQL you have to specify what kind of constraint you want to remove:

Example:

  CONSTRAINT `shop_ibfk_1` FOREIGN KEY (`fb_user_id`) REFERENCES `fb_user` (`id`),
  CONSTRAINT `shop_chk_1` CHECK ((`import_lock` in (0,1)))

the first one you would remove with:

alter mytable shop drop FOREIGN KEY `shop_ibfk_1`;

the second one with

alter mytable drop CHECK `shop_chk_1`;

answered May 27, 2022 at 7:34

sonium

1,0901 gold badge13 silver badges30 bronze badges

Comments

Akshay Sharma · Accepted Answer · 2016-06-22 06:38:11Z

2

Go to structure view of the table
You will see 2 option at top a.Table structure b.Relation view.
Now click on Relation view , here you can drop your foreign key constraint. You will get all relation here.

answered Jun 22, 2016 at 6:38

Akshay Sharma

1,1121 gold badge8 silver badges21 bronze badges

1 Comment

Silviu St Over a year ago

That's great, worked for me when you don't know the id

shA.t · Accepted Answer · 2015-05-03 07:13:43Z

-5

this will works on MySQL to drop constraints

alter table tablename drop primary key;

alter table tablename drop foreign key;

edited May 3, 2015 at 7:13

shA.t

17.1k5 gold badges59 silver badges121 bronze badges

answered May 3, 2015 at 6:30

Ranjitha

15

1 Comment

alexandre-rousseau Over a year ago

DROP PRIMARY KEY should not work. DROP FOREIGN KEY works but you need to specify whose to drop. For example ALTER TABLE tablename DROP FOREIGN KEY id_name_fk

Collectives™ on Stack Overflow

How to remove constraints from my MySQL table?

12 Answers 12

6 Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

6 Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

Linked

Related