How to remove constraints from my MySQL table?

mysql foreign-keys constraints alter
deepu sankar picture deepu sankar · Jan 2, 2013 · Viewed 304.2k times · Source

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

Answer

Bohemian picture Bohemian · Jan 2, 2013

Mysql has a special syntax for dropping foreign key constraints:

ALTER TABLE tbl_magazine_issue
  DROP FOREIGN KEY FK_tbl_magazine_issue_mst_users

Related questions

How to truncate a foreign key constrained table?

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID)) …

Read More
MySQL Removing Some Foreign keys

I have a table whose primary key is used in several other tables and has several foreign keys to other tables. CREATE TABLE location ( locationID INT NOT NULL AUTO_INCREMENT PRIMARY KEY ... ) ENGINE = InnoDB; CREATE TABLE assignment ( assignmentID INT NOT …

Read More
How to use an auto incremented primary key as a foreign key as well?

This is what I'm trying to do: I have 2 tables... CREATE TABLE `parent` ( `id` int(11) NOT NULL AUTO_INCREMENT, `data` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; CREATE TABLE `child` ( `parent_id` int(11) DEFAULT NULL, `related_…

Read More