300

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

shA.t
  • 16,580
  • 5
  • 54
  • 111
deepu sankar
  • 4,335
  • 3
  • 26
  • 37
  • 2
    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 Apr 24 '16 at 13:41
  • 1
    feature request: [Foreign keys: DROP CONSTRAINT as a compatibility alias for DROP FOREIGN KEY](https://bugs.mysql.com/bug.php?id=3742) – Ben Creasy Dec 30 '17 at 16:57
  • Possible duplicate of [Remove Primary Key in MySQL](https://stackoverflow.com/questions/2111291/remove-primary-key-in-mysql) – 劉鎮瑲 Dec 19 '18 at 01:16
  • 1
    Your syntax is perfectly valid and now it is supported [demo](https://stackoverflow.com/a/59831214/5070879) – Lukasz Szozda Jan 20 '20 at 21:40

12 Answers12

480

Mysql has a special syntax for dropping foreign key constraints:

ALTER TABLE tbl_magazine_issue
  DROP FOREIGN KEY FK_tbl_magazine_issue_mst_users
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 27
    Postgres, MSSQL, and Oracle all have `alter table .. drop constraint`. MySQL is the odd one out, it seems. – Jared Beck Mar 23 '15 at 23:48
  • 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 Aug 18 '17 at 00:26
  • 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 – Lealo Aug 18 '17 at 00:35
  • 1
    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. – Rich Harding Nov 21 '17 at 21:33
  • 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 – SNS - Web et Informatique Feb 19 '21 at 07:16
  • For reference, if you happen to be using MariaDB: the `alter table .. drop constraint` approach is available since certain MariaDB 10.2 and 10.3 versions: https://mariadb.com/kb/en/alter-table/#drop-constraint – Per Lundberg Sep 22 '22 at 12:38
68

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`;
Wellington Lorindo
  • 2,405
  • 19
  • 21
  • 3
    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. – Rich Harding Nov 21 '17 at 21:32
  • 3
    As a single command: `ALTER TABLE table_name DROP FOREIGN KEY IF EXISTS id_name_fk, DROP INDEX IF EXISTS id_name_fk;` – Frank Forte Dec 20 '18 at 18:56
  • 1
    @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. – Ruslan Stelmachenko Nov 24 '20 at 17:13
28

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

Lothar
  • 529
  • 1
  • 5
  • 19
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 ...

Robert Knight
  • 2,888
  • 27
  • 21
11

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`;
jbobbins
  • 1,221
  • 3
  • 15
  • 28
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;

roelleor
  • 443
  • 6
  • 11
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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
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.

piscator
  • 8,028
  • 5
  • 23
  • 32
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/

Markus Barthlen
  • 389
  • 4
  • 15
  • 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 – Frank Forte Dec 20 '18 at 18:58
2
  1. Go to structure view of the table
  2. You will see 2 option at top a.Table structure b.Relation view.
  3. Now click on Relation view , here you can drop your foreign key constraint. You will get all relation here.
Akshay Sharma
  • 1,042
  • 1
  • 8
  • 21
1

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`;
sonium
  • 918
  • 1
  • 11
  • 25
-4

this will works on MySQL to drop constraints

alter table tablename drop primary key;

alter table tablename drop foreign key;
shA.t
  • 16,580
  • 5
  • 54
  • 111
Ranjitha
  • 17
  • `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` – alexandre-rousseau Oct 23 '17 at 12:29