How to remove constraints from my MySQL table?
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 'constraintFK_tbl_magazine_issue_mst_users
' at line 1
Mysql has a special syntax for dropping foreign key constraints:
ALTER TABLE tbl_magazine_issue
DROP FOREIGN KEY FK_tbl_magazine_issue_mst_users
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`;
There is no such thing as DROP CONSTRAINT
in MySQL. In your case you could use DROP FOREIGN KEY
instead.
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 ...