只有存在外键才能删除 [英] Drop foreign key only if it exists

问题描述

我正在做这个，但是不行b

  ALTER TABLE`object`删除外键如果存在`object_ibfk_1`; 
  

我试着把这个IF EXISTS放在任何地方。如果在外键存在之前如何检查外键是否存在？

解决方案

>不想使用程序你可以这样做（对于MySQL）：

  set @ var =如果（（真正的SELECT语句从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'）; 
 
从@var准备stmt; 
执行stmt; 
 deallocate prepare stmt; 
  

如果有外键，我们会把alter table语句放在变量中，如果没有，虚拟语句。然后我们执行它。

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`;

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

解决方案

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;

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.

这篇关于只有存在外键才能删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！