1

Background

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

Problem

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.

Question

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 '17 at 4:10
  • @amacvar Add an answer that works showing how to do it and I'll accept it. – StillLearnin Jun 23 '17 at 12:00
5

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
|improve this answer|||||
  • This solves our problem but it would still be interesting to know what all would break by disabling triggers. – StillLearnin Jun 22 '17 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" – StillLearnin Jun 22 '17 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? – ypercubeᵀᴹ Jun 23 '17 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. – StillLearnin Jun 23 '17 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? – ypercubeᵀᴹ Jun 23 '17 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.