Advertisement
Advertisement


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

2015/05/03
1
257
5/3/2015 7:11:58 AM

Accepted Answer

Mysql has a special syntax for dropping foreign key constraints:

ALTER TABLE tbl_magazine_issue
  DROP FOREIGN KEY FK_tbl_magazine_issue_mst_users
2013/09/30
428
9/30/2013 10:42:04 AM

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`;
2013/08/19

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

2013/01/02

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

2015/01/06

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`;
2017/05/04

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;

2018/02/20