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

User · Answer

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     lt database gt   AND REFERENCED TABLE NAME     lt table gt      Then remove the foreign key by running the before mentioned DROP FOREIGN KEY query and its proper name

User · Answer

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

User · Answer

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

User · Answer

this will works on MySQL to drop constraints  alter table tablename drop primary key   alter table tablename drop foreign key

User · Answer

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 lt  fiddle demo

User · 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

User · Answer

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

User · Answer

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

User · Answer

There is no DROP CONSTRAINT In MySql  This work like magic in mysql 5 7  ALTER TABLE answer DROP KEY const name

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] How to remove constraints from my MySQL table?

Examples related to mysql

Examples related to foreign-keys

Examples related to constraints

Examples related to alter