I have two tables,
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.
select count(*) from device where device.platform_id not in (select distinct p.id from platform p); count ------- 210
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?