Início / dba / Perguntas / 90902
Accepted
Nicolas Payart
Asked: 2015-02-04 02:18:37 +0800 CST

Integridade referencial após desabilitar/habilitar todos os gatilhos

  • 772

Gostaria de saber como verificar a integridade referencial em uma tabela depois de desabilitar e reabilitar as restrições de chave estrangeira.

Por exemplo :

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

Vamos tentar inserir um valor que não respeite 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".

=> falha logicamente, então desativo as restrições FK para forçar a inserção:

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;

Mas agora, a integridade referencial não é mais respeitada.

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

Então, há uma maneira de verificar a integridade depois?

postgresql

2 respostas

  1. Best Answer

    Como parece que não há como verificar a integridade referencial depois de usar DISABLE TRIGGER ALL/ ENABLE TRIGGER ALL, finalmente usei DROP/CREATE CONSTRAINT.

    Inspirado por esta postagem no blog de Magnus Hagander, usei essas consultas para gerar comandos alter para remover e criar 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

    Aqui está o meu processo:

    1. Iniciar Transação
    2. Eliminar restrições
    3. Faça alguma coisa... (inserir, atualizar, o que for...)
    4. Criar restrições
    5. se falhar, reverta, caso contrário, confirme a transação
    • 3

  2. usá ALTER TABLE ... VALIDATE CONSTRAINT ... -lo faz o que você quer.

    • 0

relate perguntas