We're moving a legacy application from a non-relational database to PostgreSQL.


We'd like to enable foreign keys on a lot of fields for future data integrity and so that reporting tools can easily determine which tables are linked. Some of the current data won't pass the FK check and because of the legacy app connecting to the database we can't turn on the FKs yet.

Temporary Solution

Some of the programmers are suggesting that we disable triggers on the database as shown here so that the FKs aren't enforced but will still be there for reporting tools.


I'm afraid that this will cause a bunch of other un-intended consequences but I'm not sure what they are. Can anyone put my fears to rest or explain why this is a really bad idea? I'm assuming that one reason it is bad is that it would disable all triggers not just triggers that fire the FK constraints.

  • You can disable triggers by name as super. And yes "be vevy afvaid" with cleanup later on ;)
    – amacvar
    Jun 23, 2017 at 4:10
  • @amacvar Add an answer that works showing how to do it and I'll accept it. Jun 23, 2017 at 12:00

1 Answer 1


You can create the foreign key constraints with the option NOT VALID. Then they are declared, but are not enforced:

create table parent (id integer primary key);
create table child (id integer primary key, pid integer);

insert into parent values (1),(2),(3);
insert into child values (1,100),(2,200),(3,300); --<< invalid pid values!

alter table child 
     add constraint fk_child_parent 
     foreign key (pid) references p (id) NOT VALID; --<< succeeds
  • This solves our problem but it would still be interesting to know what all would break by disabling triggers. Jun 22, 2017 at 17:36
  • Actually no it doesn't quite solve the problem because the legacy app may still try to insert invalid data until it is replaced and its inserts are not allowed to fail. See the last sentence of the "Problem" Jun 22, 2017 at 17:46
  • @StillLearnin what? The question says that you want to disable FKs ("we can't turn on the FKs yet"). Is that not what you want? Jun 23, 2017 at 6:58
  • @ypercubeᵀᴹ we can't turn on FKs yet but we want then defined awhile. This answer shows how to define them without checking existing data but having done that we can no longer insert invalid data because the FK will reject it. Jun 23, 2017 at 11:57
  • So let me understand: you don't wan to check existing (invalid) data, you don't want to check inserted data for validity. Is that correct? Jun 23, 2017 at 12:53

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.