删除数据库中的所有外键(MySql)

Delete all foreign keys in database(MySql)

提问by Borut Flis

I would like to rename a column in a table that is a foreign key to many tables. Apparently this is only possible if you delete the constraints, as I found out in this link.

我想重命名一个表中的一列，该列是许多表的外键。显然，这只有在您删除约束时才有可能，正如我在此链接中发现的那样。

I dont want to delete all the constratints manually is there a way to delete all the foreign key constraints in the database?

我不想手动删除所有约束有没有办法删除数据库中的所有外键约束？

I have also tried SET FOREIGN_KEY_CHECKS=0;but I still cant rename the column.

我也尝试过，SET FOREIGN_KEY_CHECKS=0;但我仍然无法重命名该列。

采纳答案by Augusto

Executing the following query

执行以下查询

select * from information_schema.key_column_usage

will show you all the constraints (with the column name, constraint type, table and schema) that exist in your database. You'll notice these columns:

将向您显示数据库中存在的所有约束（包括列名、约束类型、表和模式）。您会注意到这些列：

CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
POSITION_IN_UNIQUE_CONSTRAINT
REFERENCED_TABLE_SCHEMA
REFERENCED_TABLE_NAME
REFERENCED_COLUMN_NAME

Then, if you're planning to delete each constraint you have referencing your column, you should consider the REFERENCED_*columns and run something like:

然后，如果您打算删除引用列的每个约束，您应该考虑REFERENCED_*列并运行如下内容：

DELETE FROM information_schema.key_column_usage 
WHERE 
    REFERENCED_TABLE_SCHEMA='myschema'
    AND
    REFERENCED_TABLE_NAME='mytable'
    AND
    REFERENCED_COLUMN_NAME='mycolumn'

http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html

http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html

回答by lopisan

You can use this SQL to generate ALTER TABLES (!!YOUR_SCHEMA_HERE!!needs to be replaced by your schema):

您可以使用此 SQL 生成 ALTER TABLES（!!YOUR_SCHEMA_HERE!!需要由您的架构替换）：

SELECT concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';')
FROM information_schema.table_constraints
WHERE constraint_type='FOREIGN KEY'
AND table_schema='!!YOUR_SCHEMA_HERE!!';

It will generate SQL like this:

它将生成这样的 SQL：

alter table viewpoint_test.answer_code DROP FOREIGN KEY fk_answer_code_codebook_item1;
alter table viewpoint_test.answer_code DROP FOREIGN KEY fk_answer_code_questionary_answer1;
alter table viewpoint_test.codebook DROP FOREIGN KEY codebook_ibfk_1;
...

回答by Md. Maruf Hossain

You Can Try using As Like of Following ..

您可以尝试使用 As Like of following ..

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName);

Also you can try with Reference Key.As like .....

您也可以尝试使用参考键。就像.....

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName)
REFERENCES anotherTableName(reference_id);

回答by Dewey

The following query will build the correct syntax automatically. Make sure you put your real DB schema name in the WHEREcondition. Just execute each line returned and all your FOREIGN KEYSwill be gone.

以下查询将自动构建正确的语法。确保将真实的数据库架构名称放在WHERE条件中。只需执行返回的每一行，你的所有FOREIGN KEYS意志就会消失。

I leave the reverse (adding them back) as an exercise for you.

我把相反的（把它们加回来）作为你的练习。

SELECT CONCAT("alter table ", TABLE_NAME," drop foreign key ", CONSTRAINT_NAME,"; ") AS runMe
FROM information_schema.key_column_usage 
WHERE TABLE_SCHEMA='MY_SCHEMA_NAME' AND CONSTRAINT_NAME <> 'PRIMARY';

If you need it to be schema independent, you can write: TABLE_SCHEMA=DATABASE()to get the current active DB name

如果你需要它与模式无关，你可以写： TABLE_SCHEMA=DATABASE()to get the current active DB name

回答by MaylorTaylor

I was still having some issues after finding this thread, but I have seemed to find a workflow that works for me.

找到这个线程后，我仍然遇到了一些问题，但我似乎找到了一个适合我的工作流程。

Database Deletion

数据库删除

If you are using MySQL Workbench, you will need to turn SAFE UPDATE off by doing the following 3 steps:

如果您使用的是 MySQL Workbench，则需要通过执行以下 3 个步骤来关闭安全更新：

1. MySql Workbench > Preferences > SQL EDITOR > (bottom of settings)
2. turn the SAFE UPDATE off
3. then log out of your connection and reconnect

1. MySql Workbench > Preferences > SQL EDITOR >（设置底部）
2. 关闭安全更新
3. 然后注销您的连接并重新连接

Steps to start purge database and start new

启动清除数据库和启动新数据库的步骤

1. Delete all data from tables

1. 删除表中的所有数据

(This MySQL script will create a DELETE FROM script for each table in your database.)

（此 MySQL 脚本将为数据库中的每个表创建一个 DELETE FROM 脚本。）

SELECT concat('DELETE FROM ',table_schema,'.',table_name,';') FROM information_schema.table_constraints WHERE table_schema='!!TABLE_SCHEMA!!';

SELECT concat('DELETE FROM ',table_schema,'.',table_name,';') FROM information_schema.table_constraints WHERE table_schema='!!TABLE_SCHEMA!!';

Copy the output of this and run it. You might need this line-by-line.

复制它的输出并运行它。您可能需要逐行执行此操作。

1. Delete all Foreign Keys from your tables

2. 从表中删除所有外键

(This MySql script will create na ALTER TABLE _ DROP FOREIGN KEY script for each table in your database.)

（此 MySql 脚本将为数据库中的每个表创建 na ALTER TABLE _ DROP FOREIGN KEY 脚本。）

SELECT concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') FROM information_schema.table_constraints WHERE constraint_type='FOREIGN KEY' AND table_schema='!!TABLE_SCHEMA!!';

SELECT concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') FROM information_schema.table_constraints WHERE constraint_type='FOREIGN KEY' AND table_schema='!!TABLE_SCHEMA!!';

Copy the output of this and run it. You might need this line-by-line.

复制它的输出并运行它。您可能需要逐行执行此操作。

After you've done this, you SHOULD be able to run your DROP DATABASE script.

完成此操作后，您应该能够运行 DROP DATABASE 脚本。