Mysql：以编程方式删除所有外键 [英] Mysql: Programmatically remove all foreign keys

问题描述

目前我正在处理一些数据库问题，在所有地方都有错误的地方定义了外键，我想把它们全部删除，然后从头开始。我不希望删除列，只是外键关系。

如何从整个数据库中删除所有外键？ （或按表）。

谢谢。

编辑：忘记说了，我已经提供PHPMyAdmin使用。

解决方案

下面是一个PHP脚本，循环访问information_schema.key_column_usage表并放下每个外键：

 <？php 
 $ DBNAME ='dbname'; 
 mysql_connect（'localhost'，'username'，'password'）; 
 
 mysql_select_db（$ DBNAME）; 
 $ result = mysql_query（SELECT DISTINCT table_name，constraint_name
。FROM information_schema.key_column_usage
。WHERE constraint_schema ='$ DBNAME'
AND referenced_table_name IS NOT NULL）; 
 while（$ row = mysql_fetch_assoc（$ result））{
 mysql_query（ALTER TABLE`$ row [table_name]`
DROP FOREIGN KEY`$ row [constraint_name]` ）
或者死（mysql_error（））; 
} 
 mysql_free_result（$ result）; 
 mysql_close（）; 
？> 
  

I'm working with a bit of a dodgey database at the moment, there are foreign keys defined in all the wrong places all over the place, I'd like to remove them all and then start from scratch. I don't wish to remove the column, just the foreign key relationships.

How can I remove all foreign keys from an entire database? (Or table by table).

Thanks.

Edit: Forgot to say, I have PHPMyAdmin available to use.

解决方案

Here's a PHP script to loop through the information_schema.key_column_usage table and drop each foreign key:

<?php
$DBNAME = 'dbname';
mysql_connect('localhost', 'username', 'password');

mysql_select_db($DBNAME);
$result = mysql_query("SELECT DISTINCT table_name, constraint_name"
  . " FROM information_schema.key_column_usage"
  . " WHERE constraint_schema = '$DBNAME'"
  . " AND referenced_table_name IS NOT NULL");
while($row = mysql_fetch_assoc($result)) {
  mysql_query("ALTER TABLE `$row[table_name]`"
    . " DROP FOREIGN KEY `$row[constraint_name]`")
    or die(mysql_error());
}
mysql_free_result($result);
mysql_close();
?>

这篇关于Mysql：以编程方式删除所有外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！