MySQL Bugs: #5746: DROP FOREIGN KEY [IF EXISTS] would be nice

Bug #5746	DROP FOREIGN KEY [IF EXISTS] would be nice
Submitted:	25 Sep 2004 17:13	Modified:	16 Sep 2006 9:55
Reporter:	Mike Veroukis	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.0.18	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any

Description:
The DROP FOREIGN KEY option would benefit greatly from an IF EXISTS clause (in the same way that the "DROP TABLE IF EXISTS tbl_name" works). As it is, if the FK doesn't already exist it can abort the rest of the script.

  - Mike

How to repeat:
Create a table, add a FK constraint, drop the FK constraint twice (without ever dropping the table). If you do drop the table, it then complains that the table doesn't exist, which is of course a seperate issue.

Suggested fix:
Add an [IF EXISTS] clause to the DROP FOREIGN KEY option to ALTER TABLE. Alternatively, make it fail silently, without aborting a script.

There is any solution for this bug? Well, I try to include in my code:

ALTER TABLE table1 DROP FOREIGN KEY FK_table1;
ALTER TABLE table2 DROP FOREIGN KEY FK_table2;

In the first execution this throws some warnings...

'Table "table1" doesn't exists..'

After the first, all OK.

Sorry for my english if it is very bad, I'm a spanish student!

Thank you for a feature request. I do not think that addining new (non-standard) IF EXISTS subclauses to ALTER TABLE is needed. Please, give complete example that shows when it will be useful, if you are not agree with me.

Yes this feature should have been  there.
Lets take a simple test case.
For a particular db table in a product release, we dropped the foreign key in the upgrade script but not in the table creation script.

Now if we want to upgrade to latest release, there are few with foreign key and few with no foreign key.
In the latest upgrade if you dont want the foreign key, then droping the foreing key requires, if exists condition other wise an error is thrown which is not desirable.

Thankyou,
meetppr.

I'd like to add another vote to this request. This would be a useful feature for scripts.

There is a workaround, but it's VERY UGLY :

http://forums.mysql.com/read.php?97,218825,247526

Please consider to add this feature because it's very needed and almost all databases including MySQL forks have such functionality.
It will be used in migration scripts especially if some migration script failed before on some DBs.
For integration test scenarios when you need to drop tables in DB and recreate them but to drop a table you need to drop foreign keys first etc. For example Hibernate with auto ddl tries to do so and it always fails with an error (https://hibernate.atlassian.net/browse/HHH-10639).