从所有表中删除外键关系 [英] Remove Foreign Key Relationships From All Tables
问题描述
看看这个页面
http://dev.mysql.com/doc/refman/5.0/en/key-column-usage-table.html
$ pre code $ select $ c $ select $($'$'$'$ $ $'
where constraint_schema ='your_db'and referenced_table_name ='table_name';
然后运行生成的输出。
你可以做类似的事情来截断所有的表。
$ $ $ $ $ $ $ $ $ select select('truncate',table_name,';')
from information_schema.tables
where table_schema ='your_db'and table_type ='base table'
这个将截断指定数据库中的所有表。所以小心使用它。
I have a database that has several tables. Many of the tables have fields with foreign key constraints. I want to truncate the tables and then repopulate them with new data, and I also want to take off the foreign keys, as some of the relationships have changed. basically, I want to build the FK constraints up from scratch again. How can I remove the current FK constraints from all tables?
You can play with the information_schema. Take a look at this page
http://dev.mysql.com/doc/refman/5.0/en/key-column-usage-table.html
select concat('alter table ',table_name,' drop foreign key ',constraint_name,';')
from information_schema.key_column_usage
where constraint_schema = 'your_db' and referenced_table_name = 'table_name';
then run the output generated.
You can do something similar in order to truncate all tables.
select concat('truncate ',table_name,';')
from information_schema.tables
where table_schema = 'your_db' and table_type = 'base table'
this one will truncate all tables within the specified database. So use it with care.
这篇关于从所有表中删除外键关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!