postgresql 如何在 Postgres 8.2 中禁用参照完整性？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/139884/
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
How do I disable referential integrity in Postgres 8.2?
提问by sanbikinoraion
Google results on this one are a bit thin, but suggest that it is not easily possible.
谷歌在这个方面的结果有点单薄，但表明这并不容易。
My specific problem is that I need to renumber the IDs in two tables that are related to each other such that table B has an "table_a_id" column in it. I can't renumber table A first because then its children in B point to the old IDs. I can't renumber table B first because then they would point to the new IDs before they were created. Now repeat for three or four tables.
我的具体问题是我需要对两个相互关联的表中的 ID 重新编号，以便表 B 中有一个“table_a_id”列。我不能先对表 A 重新编号，因为它在 B 中的子项指向旧 ID。我不能先对表 B 重新编号，因为它们会在创建新 ID 之前指向它们。现在重复三到四张桌子。
I don't really want to have to fiddle around with individual relationships when I could just "start transaction; disable ref integrity; sort IDs out; re-enable ref integrity; commit transaction". Mysql and MSSQL both provide this functionality IIRC so I would be surprised if Postgres didn't.
当我可以“开始事务；禁用引用完整性；整理 ID；重新启用引用完整性；提交事务”时，我真的不想摆弄个人关系。Mysql 和 MSSQL 都提供了这个功能 IIRC，所以如果 Postgres 没有，我会感到惊讶。
Thanks!
谢谢！
采纳答案by Joel B Fant
It does not seem possible. Other suggestions almost always refer to dropping the constraints and recreating them after work is done.
似乎不可能。其他建议几乎总是提到删除约束并在工作完成后重新创建它们。
However, it seems you can make constraints DEFERRABLE, such that they are not checked until the end of a transaction. See PostgreSQL documentation for CREATE TABLE(search for 'deferrable', it's in the middle of the page).
但是，您似乎可以进行约束DEFERRABLE，以便在事务结束之前不检查它们。请参阅PostgreSQL 文档CREATE TABLE（搜索 'deferrable'，它位于页面中间）。
回答by Nick Johnson
There are two things you can do (these are complementary, not alternatives):
您可以做两件事（这些是互补的，而不是替代品）：
- Create your foreign key constraints as DEFERRABLE. Then, call "SET CONSTRAINTS DEFERRED;", which will cause foreign key constraints not to be checked until the end of the transaction. Note that the default if you don't specify anything is NOT DEFERRABLE (annoyingly).
- Call "ALTER TABLE mytable DISABLE TRIGGER ALL;", which prevents any triggers executing while you load data, then "ALTER TABLE mytable ENABLE TRIGGER ALL;" when you're done to re-enable them.
- 将外键约束创建为 DEFERRABLE。然后，调用“SET CONSTRAINTS DEFERRED;”，这将导致直到事务结束才检查外键约束。请注意，如果您不指定任何内容，则默认值是 NOT DEFERRABLE（烦人）。
- 调用“ALTER TABLE mytable DISABLE TRIGGER ALL;”，以防止在加载数据时执行任何触发器，然后调用“ALTER TABLE mytable ENABLE TRIGGER ALL;” 当您完成重新启用它们时。
回答by Dimitris
I found these 2 excellent scripts which generate the sql for dropping the constraints and then recreating them. here they are:
我发现这 2 个优秀的脚本生成用于删除约束然后重新创建它们的 sql。他们来了：
For dropping the constraints
用于删除约束
SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" DROP CONSTRAINT "'||conname||'";'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END,contype,nspname,relname,conname
For recreating them
为了重新创造它们
SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" ADD CONSTRAINT "'||conname||'" '|| pg_get_constraintdef(pg_constraint.oid)||';'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END DESC,contype DESC,nspname DESC,relname DESC,conname DESC;
Run these queries and the output will be the sql scripts that you need for dropping and creating the constraints.
运行这些查询，输出将是删除和创建约束所需的 sql 脚本。
Once you drop the constraints you can do all you like with the tables. When you are done re-introduce them.
删除约束后，您就可以对表格进行任何您喜欢的操作。完成后重新介绍它们。
回答by Liam
I think you need to make a list of your foreign key constraints, drop them, do your changes, then add the constraints again. Check the documentation for alter table drop constraintand alter table add constraint.
我认为您需要列出外键约束，删除它们，进行更改，然后再次添加约束。检查的文件alter table drop constraint和alter table add constraint。
回答by zzzeek
Here's a Python script that will delete all constraints in a transaction, run some queries, then recreate all those constraints. pg_get_constraintdefmakes this super-easy:
这是一个 Python 脚本，它将删除事务中的所有约束，运行一些查询，然后重新创建所有这些约束。 pg_get_constraintdef使这变得非常简单：
class no_constraints(object):
def __init__(self, connection):
self.connection = connection
def __enter__(self):
self.transaction = self.connection.begin()
try:
self._drop_constraints()
except:
self.transaction.rollback()
raise
def __exit__(self, exc_type, exc_value, traceback):
if exc_type is not None:
self.transaction.rollback()
else:
try:
self._create_constraints()
self.transaction.commit()
except:
self.transaction.rollback()
raise
def _drop_constraints(self):
self._constraints = self._all_constraints()
for schemaname, tablename, name, def_ in self._constraints:
self.connection.execute('ALTER TABLE "%s.%s" DROP CONSTRAINT %s' % (schemaname, tablename, name))
def _create_constraints(self):
for schemaname, tablename, name, def_ in self._constraints:
self.connection.execute('ALTER TABLE "%s.%s" ADD CONSTRAINT %s %s' % (schamename, tablename, name, def_))
def _all_constraints(self):
return self.connection.execute("""
SELECT n.nspname AS schemaname, c.relname, conname, pg_get_constraintdef(r.oid, false) as condef
FROM pg_constraint r, pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE r.contype = 'f'
and r.conrelid=c.oid
""").fetchall()
if __name__ == '__main__':
# example usage
from sqlalchemy import create_engine
engine = create_engine('postgresql://user:pass@host/dbname', echo=True)
conn = engine.connect()
with no_contraints(conn):
r = conn.execute("delete from table1")
print "%d rows affected" % r.rowcount
r = conn.execute("delete from table2")
print "%d rows affected" % r.rowcount
回答by Sean the Bean
If the constraints are DEFERRABLE, this is really easy. Just use a transaction block and set your FK constraints to be deferred at the beginning of the transaction.
如果约束是DEFERRABLE，这真的很容易。只需使用事务块并在事务开始时将 FK 约束设置为延迟。
From http://www.postgresql.org/docs/9.4/static/sql-set-constraints.html:
从http://www.postgresql.org/docs/9.4/static/sql-set-constraints.html：
SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit.
SET CONSTRAINTS 设置当前事务中约束检查的行为。在每个语句的末尾检查 IMMEDIATE 约束。在事务提交之前不会检查 DEFERRED 约束。
So you could do:
所以你可以这样做：
BEGIN;
SET CONSTRAINTS
table_1_parent_id_foreign,
table_2_parent_id_foreign,
-- etc
DEFERRED;
-- do all your renumbering
COMMIT;
Unfortunately, it seems Postgres defaults all constraints to NOT DEFERRABLE, unless DEFERRABLEis explicitly set. (I'm guessing this is for performance reasons, but I'm not certain.) As of Postgres 9.4, it isn't too hard to alter the constraints to make them deferrable if needed:
不幸的是，似乎 Postgres 将所有约束默认为NOT DEFERRABLE，除非DEFERRABLE明确设置。（我猜这是出于性能原因，但我不确定。）从 Postgres 9.4 开始，如果需要，更改约束以使其可延迟并不太难：
ALTER TABLE table_1 ALTER CONSTRAINT table_1_parent_id_foreign DEFERRABLE;
(See http://www.postgresql.org/docs/9.4/static/sql-altertable.html.)
（参见http://www.postgresql.org/docs/9.4/static/sql-altertable.html。）
I think this approach would be preferable to dropping and recreating your constraints as some have described, or to disabling all (or all user) triggers until the end of the transaction, which requires superuser privileges, as noted in an earlier comment by @clapas.
我认为这种方法比某些人描述的删除和重新创建约束更可取，或者在事务结束之前禁用所有（或所有用户）触发器，这需要超级用户权限，如@clapas之前的评论中所述。
回答by Daniel F
I think that an easear solution would be to create "temporary" columns associating where you want them to be.
我认为一个简单的解决方案是创建“临时”列，将它们关联到您想要的位置。
update the values with the foreign keys to the new columns
使用新列的外键更新值
drop the inicial columns
删除初始列
rename to the new "temporary" columns to the same names then the inicial ones.
将新的“临时”列重命名为与初始列相同的名称。
