Drop an unnamed foreign key in MySql

Question

If the foreign key was created without a name, MySql will provide it with a default value. For example, for the Test table, the foreign key will be called test_ibfk_1. When I delete a foreign key locally using this name, it works like a charm, but on the development server it fails with errno: 152.

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

My question is: is it safe to use the default name for constraint management (at least in MySql)?

Thanks in advance!

Answer 1

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

Use one of these queries to get foreign key names -

SELECT constraint_name FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE constraint_schema = <'db_name'> AND table_name = <'table_name'>; SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE 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 reset the foreign key.

Answer 2

You can delete without knowing the name by creating a concatenation request and executing automatically:

 set @s:=''; select @s:=concat(@s, 'alter table ', 'your_table', ' drop foreign key ',CONSTRAINT_NAME, ';') from information_schema.key_column_usage where CONSTRAINT_SCHEMA = 'your_database' and TABLE_NAME ='your_table' and REFERENCED_TABLE_NAME = 'the_foreign_reference_table'; prepare stmt from @s; execute stmt; deallocate prepare stmt; 

In this case, you do not need to know the name of the foreign key, you only need the name of the table for which you want to delete the foreign key, the database name and the reference table.

Answer 3

A hasty reader does not shy away from this answer, simply because it is long. You will not find another solution , I promise :)

The accepted answer does not discard the key, it only finds its name. To really remove a key with an unknown name, you will need to use prepared statements. The most common solution is a script that you can configure with five variables:

 -- YOU MUST SPECIFY THESE VARIABLES TO FULLY IDENTIFY A CONSTRAINT SET @table_name = '...'; SET @column_name = '...'; SET @referenced_table_name = '...'; SET @referenced_column_name = '...'; -- make sure to limit queries to a single db schema SET @db_name = '...'; -- find the name of the foreign key and store it in a var SET @constraint_name = ( SELECT constraint_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @table_name AND COLUMN_NAME = @column_name AND CONSTRAINT_SCHEMA = @db_name AND referenced_table_name = @referenced_table_name AND referenced_column_name = @referenced_column_name); -- prepare the drop statement in a string and run it SET @s = concat('alter table ', @table_name, ' drop foreign key ', @constraint_name); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;