I have two tables, device and platform where device.platform_id enforces the following constraint:

Foreign-key constraints:
    "device_platform_id_fkey" FOREIGN KEY (platform_id) REFERENCES platform(id)

It has been this way since the table was first created.

And yet:

select count(*) from device 
where device.platform_id 
not in (select distinct p.id from platform p);

I discovered the inconsistency while testing a backup (insert or update failed because it violates foreign key constraint).

We don't have any application logic that does something like ALTER TABLE … DISABLE TRIGGER while deferring constraint validation within a transaction.

How could my database enter this state?

The only thing that comes to mind is a bad dump/restore that may have temporarily disabled triggers and imported constraint-violating data at some point in the past.

What am I missing?

  • Maybe they were added before the FKEY was created and the FKEY was created in a NOT VALID state? – Joishi Bodio May 9 '16 at 20:26
  • @mustaccio Indeed, but I linked the only thread I could find for "How to temporarily globally ignore constraints" for good measure. – msanford May 9 '16 at 20:50
  • Does device have an id column as well? If yes use a fully qualified column name in the sub-select not in (select p.id from platform p) (the distinct is useless in the sub-select) – a_horse_with_no_name May 9 '16 at 21:16
  • @a_horse_with_no_name It does: the fully-qualified schema names were used to produce the example output, but then simplified when pasting into the question. I'll edit it to avoid confusion. – msanford May 10 '16 at 13:59
  • 2
    @mustaccio that's technically not true, there are triggers behind FKs. One can find them using pg_trigger and pg_constraint (some clients list them, too). – dezso May 10 '16 at 14:52

Your Answer

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

Browse other questions tagged or ask your own question.