如何禁用和启用表mysql incl中的所有约束. PK FK CHK UNI等 [英] How to disable and enable all constraints in table mysql incl. PK FK CHK UNI etc

问题描述

我需要将测试数据加载到表中，并且想禁用所有可能的约束，我已经找到了如何全局关闭外键的方法，但是我需要禁用所有约束，包括PK，FK，CHK，UNIQ等.有可能在全球范围内做到吗?当然，那之后我必须调整所有约束条件.

I need to load test data to table and I want to disable every possible constraint, i ve found how to globally turn off the foreign keys, but I need to disable all constraints, PK,FK, CHK, UNIQ etc. Is it possible to do it globally? Of course after that I have to tun all constraints on.

推荐答案

使用以下命令禁用约束:

Use the following to disable constraints:

-- disable UNIQ, PK, ...
ALTER TABLE <tablename> DISABLE KEYS;
-- diable FK
SET FOREIGN_KEY_CHECKS=0;

检查实例此站点，以获取更多示例.恢复方式:

Check for instance this site for more examples. Restore with:

SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE <tablename> ENABLE KEYS;

从参考文献:

• foreign_key_checks
• D ISABLE密钥:使用ALTER TABLE ... DISABLE KEYS告诉MySQL停止更新非唯一索引...."

• foreign_key_checks
• DISABLE KEYS: " Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. ..."

ps :来自 InnoDB性能调整技巧

SET autocommit=0; 
SET unique_checks=0; 
SET foreign_key_checks=0;

这篇关于如何禁用和启用表mysql incl中的所有约束. PK FK CHK UNI等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！