Ariel Chelsău Ariel Chelsău - 1 year ago 124
MySQL Question

Drop unnamed Foreign Key in MySql

If a foreign key was created without a name, MySql will give it a default one. For instance, for table 'Test' the foreign key will be named 'test_ibfk_1'. When I drop the foreign key locally by using this name it works like a charm but on the development server it fails with errno: 152.

I know this name is case sensitive but either lower or upper case the result is the same.

My question: Is it safe to rely on the default name to manipulate constraints (in MySql at least)?

Thanks in advance!

Answer Source

You need to know the name of foreign key. If it was created without name, then name will be autogenerated. You should get information about the foreign key.

Use one of these queries to get foreign key names -

  constraint_schema = <'db_name'> AND table_name = <'table_name'>;

  constraint_schema = <'db_name'> AND table_name = <'table_name'> AND   
  referenced_table_name IS NOT NULL;

...and use ALTER TABLE <table_name> DROP INDEX <fk_name>; to drop foreign key.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download