如何在Postgres 8.2中禁用参照完整性？

Question

谷歌对此问题的结果有些匮乏，但表明这并不容易实现。

我的具体问题是，我需要重新编号两个相关联的表中的ID，使得表B中有一个“table_a_id”列。我不能先重新编号表A，因为它在B中的子级指向旧ID。我也不能先重新编号表B，因为它们会在创建之前指向新ID。现在重复三到四个表。

我不想通过操纵单个关系来解决问题，当我可以只需“开始事务；禁用引用完整性；排序ID；重新启用引用完整性；提交事务”。Mysql和MSSQL都提供了这个功能，所以如果Postgres没有的话，我会感到惊讶。

谢谢！

- sanbikinoraion

---

请查看：http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html - Victor

Answer 1

您可以采取两种措施（这两种措施是互补的，而非二选一）：

• 将外键约束条件设置为DEFERRABLE。然后执行"SET CONSTRAINTS DEFERRED;"命令，此时外键约束条件在事务结束之前不会被检查。需要注意的是，默认情况下约束条件是NOT DEFERRABLE（非常令人烦恼）。
• 执行"ALTER TABLE mytable DISABLE TRIGGER ALL;"命令，以防止在加载数据时执行任何触发器；当操作完成后执行"ALTER TABLE mytable ENABLE TRIGGER ALL;"命令，以重新启用触发器。

- Arachnid

---

自PostgreSQL 9起，您需要指定要禁用的内容：ALTER TABLE mytable DISABLE TRIGGER USER; 这将仅禁用用户创建的约束条件，即：您的FK和PK约束条件。 - hydrogen

我需要执行 DISABLE TRIGGER ALL; 命令来禁用 PG9 上的 FK 约束。 - Xavier Shay

修改表 mytable 禁用所有触发器需要超级用户特权；SET CONSTRAINTS DEFERRED 必须在事务内执行。 - clapas

根据这个答案，另一个选项是使用SET session_replication_role = replica;来禁用会话的所有触发器。(还需要超级用户权限。) - Ajedi32

在9.x中，它是SET CONSTRAINTS ALL DEFERRED。 - Joseph Lust

Answer 2

我发现了这两个生成SQL来删除和重新创建约束的优秀脚本，它们如下：

用于删除约束：

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

为了重新创建它们。

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;

运行这些查询，输出将是你需要用于删除和创建约束的SQL脚本。

一旦你删除了约束，你可以随心所欲地操作表格。当你完成后再重新引入它们。

- Dimitris

---

我使用了对这些语句略微修改的方法来将我的约束条件重新创建为“DEFERRABLE”，并能够发出“SET CONSTRAINTS ALL DEFERRED”的命令。干杯！ - clapas

非常感谢。这救了我的一天。 - Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ

这真的很有趣。我编辑了你的答案，添加了引号来包围实体名称，以使其与非小写实体兼容。无论如何，我不明白为什么在重新创建约束时需要降序排序，这似乎根本不需要。 - Teejay

Answer 3

这似乎是不可能的。其他建议几乎总是指删除约束并在完成工作后重新创建它们。

然而，似乎您可以将约束设置为DEFERRABLE，这样它们在事务结束之前不会被检查。请参见PostgreSQL文档中的CREATE TABLE（搜索“deferrable”，它位于页面中央）。

- Joel B Fant

Answer 4

这是一个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

- zzzeek

---

这种方法会对性能产生什么影响？听起来对我来说非常昂贵。 - beberlei

这样的操作只适用于离线数据迁移，因此没有任何“性能”影响。 - zzzeek

Answer 5

我认为你需要列出外键约束的清单，然后将它们删除，进行更改，最后再添加这些约束。查看文档了解 alter table drop constraint 和 alter table add constraint。

- Liam

Answer 6

如果约束条件是DEFERRABLE，那么这就非常容易。只需使用事务块，并在事务开始时将FK约束设置为延迟即可。

来自http://www.postgresql.org/docs/9.4/static/sql-set-constraints.html：

SET CONSTRAINTS设置当前事务中约束检查的行为。立即约束在每个语句结束时进行检查。延迟约束直到事务提交才进行检查。

所以你可以这样做：

BEGIN;

SET CONSTRAINTS
    table_1_parent_id_foreign, 
    table_2_parent_id_foreign,
    -- etc
DEFERRED;

-- do all your renumbering

COMMIT;

不幸的是，似乎Postgres默认所有约束都为NOT DEFERRABLE，除非显式设置DEFERRABLE。（我猜这是出于性能原因，但我不确定。）从Postgres 9.4开始，如果需要，修改约束使其可延迟并不太困难：

ALTER TABLE table_1 ALTER CONSTRAINT table_1_parent_id_foreign DEFERRABLE;

（请参见http://www.postgresql.org/docs/9.4/static/sql-altertable.html。）

我认为这种方法比一些人描述的删除和重新创建约束更可取，或者禁用所有（或所有用户）触发器直到事务结束，这需要超级用户权限，如早期评论@clapas所述。

- Sean the Bean

Answer 7

我认为一个更简单的解决方案是创建“临时”列，将它们与您想要的位置关联。

使用外键更新新列中的值

删除初始列

将新的“临时”列重命名为与初始列相同的名称。

- Daniel F

---

-1，这不是对原帖问题的回答。而且，提出的解决方案似乎不是一个好主意（商标）。 - Christopher