主页 / dba / 问题 / 90902
Accepted
Nicolas Payart
Asked: 2015-02-04 02:18:37 +0800 CST

禁用/启用触发全部后的参照完整性

  • 772

我想知道在禁用和重新启用外键约束后如何检查表的引用完整性。

例如 ：

postgres=# CREATE TABLE main(id serial, id_delta integer, PRIMARY KEY (id));
CREATE TABLE
postgres=# CREATE TABLE delta (id serial, PRIMARY KEY (id));
CREATE TABLE
postgres=# ALTER TABLE main ADD CONSTRAINT fk_main_delta FOREIGN KEY (id_delta) REFERENCES delta (id);
ALTER TABLE

让我们尝试插入一个不尊重 fk_main_delta 的值：

postgres=# INSERT INTO main (id_delta) VALUES (1);
ERROR:  insert or update on table "main" violates foreign key constraint "fk_main_delta"
DÉTAIL : Key (id_delta)=(1) is not present in table "delta".

=> 它在逻辑上失败了，所以我禁用 FK 约束来强制插入：

postgres=# ALTER TABLE main DISABLE TRIGGER ALL;
postgres=# INSERT INTO main (id_delta) VALUES (1);
INSERT 0 1
postgres=# ALTER TABLE main ENABLE TRIGGER ALL;

但是现在，参照完整性不再受到尊重。

postgres=# SELECT * FROM main;
 id | id_delta 
----+----------
  2 |        1
(1 ligne)

那么有没有办法事后检查完整性？

postgresql

2 个回答

  1. Best Answer

    DISABLE TRIGGER ALL由于使用/后似乎无法检查引用完整性ENABLE TRIGGER ALL，我最终DROP/CREATE CONSTRAINT改用了。

    受Magnus Hagander 博客上这篇文章的启发，我使用此类查询来生成用于删除和创建 FK 的更改命令：

    -- Generate ALTER TABLE ... ADD CONSTRAINT
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
WHERE pg_constraint.contype = 'f'
ORDER BY nspname DESC, relname DESC, conname DESC

-- Generate ALTER TABLE ... DROP CONSTRAINT
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
WHERE pg_constraint.contype = 'f'
ORDER BY nspname, relname, conname

    这是我的过程：

    1. 开始交易
    2. 删除约束
    3. 做一些事情......（插入，更新，无论如何......）
    4. 创建约束
    5. 如果失败则回滚，否则提交事务
    • 3

  2. 使用ALTER TABLE ... VALIDATE CONSTRAINT ... 它做你想做的事。

    • 0

相关问题