仅当存在外键时才删除它

2022-01-20 00:00:00 sql foreign-keys mysql

我在 MySQL 数据库中.

I'm on a MySQL database.

我正在这样做，但它不起作用.

I'm doing this, but it doesn't work.

ALTER TABLE `object` DROP FOREIGN KEY IF EXISTS `object_ibfk_1`;

我试图把这个 IF EXISTS 放在任何我能做的地方.如何在删除之前检查外键是否存在?

I've tried to put this IF EXISTS wherever I could. How can check if foreign key is exists before drop it?

推荐答案

如果你想删除外键(如果存在)并且不想使用过程你可以这样做(对于 MySQL) :

If you want to drop foreign key if it exists and do not want to use procedures you can do it this way (for MySQL) :

set @var=if((SELECT true FROM information_schema.TABLE_CONSTRAINTS WHERE
            CONSTRAINT_SCHEMA = DATABASE() AND
            TABLE_NAME        = 'table_name' AND
            CONSTRAINT_NAME   = 'fk_name' AND
            CONSTRAINT_TYPE   = 'FOREIGN KEY') = true,'ALTER TABLE table_name
            drop foreign key fk_name','select 1');

prepare stmt from @var;
execute stmt;
deallocate prepare stmt;

如果有外键，我们将alter table语句放入变量中，如果没有，我们放入一个虚拟语句.然后我们执行它.

If there is foreign key we put alter table statement in variable and if there isn't we put a dummy statement. And then we execute it.

相关文章