为迁移禁用 PostgreSQL 外键检查
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38112379/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Disable PostgreSQL foreign key checks for migrations
提问by eComEvo
I'm creating a lot of migrations that have foreign keys in PostgreSQL 9.4.
我在 PostgreSQL 9.4 中创建了很多具有外键的迁移。
This is creating a headache because the tables must all be in the exact order expected by the foreign keys when they are migrated. It gets even stickier if I have to run migrations from other packages that my new migrations depend on for a foreign key.
这令人头疼，因为表在迁移时必须完全按照外键所期望的顺序排列。如果我必须从我的新迁移依赖于外键的其他包运行迁移，它会变得更加棘手。
In MySQL, I can simplify this by simply adding SET FOREIGN_KEY_CHECKS = 0;to the top of my migration file. How can I do this temporarily in PostgresSQL only for the length of the migration code?
在 MySQL 中，我可以通过简单地添加SET FOREIGN_KEY_CHECKS = 0;到迁移文件的顶部来简化此操作。如何仅在迁移代码的长度内在 PostgresSQL 中临时执行此操作？
BTW, using the Laravel Schema Builder for this.
顺便说一句，为此使用 Laravel Schema Builder。
采纳答案by Pavel Stehule
PostgreSQL doesn't support any configuration option, but there is another possibility.
PostgreSQL 不支持任何配置选项，但还有另一种可能性。
postgres=# \d b
Table "public.b"
┌────────┬─────────┬───────────┐
│ Column │ Type │ Modifiers │
╞════════╪═════════╪═══════════╡
│ id │ integer │ │
└────────┴─────────┴───────────┘
Foreign-key constraints:
"b_id_fkey" FOREIGN KEY (id) REFERENCES a(id) DEFERRABLE
The referential integrity in Postgres is implemented by triggers, and you can disable triggers on table. With this method you can upload any data (risk), but it is significantly faster - because the check over large data is expensive. And if your upload is safe, then you can do it.
Postgres 中的参照完整性由触发器实现，您可以禁用表上的触发器。使用此方法您可以上传任何数据（风险），但速度明显更快 - 因为检查大数据的成本很高。如果您的上传是安全的，那么您就可以做到。
BEGIN;
ALTER TABLE b DISABLE TRIGGER ALL;
-- now the RI over table b is disabled
ALTER TABLE b ENABLE TRIGGER ALL;
COMMIT;
Next possibility is using deferred constraints. This move constraint check to commit time. So you should not to respect order with INSERTcommands:
下一种可能性是使用延迟约束。此移动约束检查提交时间。所以你不应该尊重命令的顺序INSERT：
ALTER TABLE b ALTER CONSTRAINT b_id_fkey DEFERRABLE;
BEGIN
postgres=# SET CONSTRAINTS b_id_fkey DEFERRED;
SET CONSTRAINTS
postgres=# INSERT INTO b VALUES(100); -- this is not in a table
INSERT 0 1
postgres=# INSERT INTO b VALUES(10);
INSERT 0 1
postgres=# COMMIT;
ERROR: insert or update on table "b" violates foreign key constraint "b_id_fkey"
DETAIL: Key (id)=(100) is not present in table "a".
This method should be preferred for you, because the inserted data will be checked.
这种方法应该是你的首选，因为插入的数据会被检查。
回答by andro83
For migration, it is easier to disable all triggers with:
对于迁移，更容易禁用所有触发器：
SET session_replication_role = 'replica';
And after migration reenable all with
并在迁移后重新启用所有
SET session_replication_role = 'origin';
