1

I’m using Postgres 9.5. I have created a dump file of INSERTs for three of my tables. To avoid getting foreign key errors, I added the below to the top and bottom of my file (the “…” is where all the INSERTs are) …

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;
ALTER TABLE addresses DISABLE TRIGGER ALL;
ALTER TABLE table1 DISABLE TRIGGER ALL;
ALTER TABLE table2 DISABLE TRIGGER ALL;

…

ALTER TABLE addresses ENABLE TRIGGER ALL;
ALTER TABLE table1 ENABLE TRIGGER ALL;
ALTER TABLE table2 ENABLE TRIGGER ALL;

However, even when i run this file, I get errors like

ERROR:  insert or update on table "table2" violates foreign key constraint "fk_rails_ba656ceafa"
DETAIL:  Key (table1_id)=(f62c5fee-1031-4d5e-a084-9210f052a2d1) is not present in table "table1".

Why am I getting these errors despite disabling foreign keys and more importantly, how can I prevent them? I want to INSERT all the data and then re-enable foreign keys.

  • 1
    This is typically not needed as the dump file will contain the foreign key definition after the import of the data. How exactly did you create the SQL dump? Please edit your question and add the exact command you used – a_horse_with_no_name Dec 29 '16 at 7:42
6

I'm not sure what's happening, but you're certainly not giving us enough information. ALTER TABLE DISABLE TRIGGER ALL works fine on disabling FOREIGN KEYS.

test=# CREATE TABLE table1 ( id serial PRIMARY KEY );
CREATE TABLE

test=# CREATE TABLE table2 ( fkey int REFERENCES table1(id) );
CREATE TABLE

test=# INSERT INTO table2 VALUES (1);
ERROR:  insert or update on table "table2" violates foreign key constraint "table2_fkey_fkey"
DETAIL:  Key (fkey)=(1) is not present in table "table1".

test=# ALTER TABLE table2 DISABLE TRIGGER ALL;
ALTER TABLE

test=# INSERT INTO table2 VALUES (1);
INSERT 0 1

I would never suggest anyone DISABLE TRIGGERS though, or make them NOT VALID. They're there to ensure data integrity and disabling them for even brief periods is a recipe for disaster and an anti-pattern in my opinion.

Just pg_dumpall and trim out what you don't need.

  • When you say I'm "not giving us enough information", waht other information can I provide that will reveal why disabling foreign keys is still resulting in errors during an insert? – Dave Dec 29 '16 at 16:04
  • If you're refusing to do work (or innocently don't know how). Then dump what you've currently got.. entirely pg_dump. And, then dump the commands you're trying to execute. What you have isn't the problem. So you have to broaden what could be the problem up a bit. – Evan Carroll Dec 29 '16 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.