Hi all,
I've discovered one of our databases has managed to get into a state where it is violating a foreign key constraint.
The facts are these:
Table B (row_id) references Table A (row_id).
Table B contains multiple row_id's that do not exist in Table A.
There also exists a BEFORE INSERT OR UPDATE trigger on Table B.
The entirety of the trigger procedure is:
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
A dump/restore of these tables on another instance fails to create the foreign key constraint (as you would expect).
I'm unable to recreate these conditions elsewhere, it simply is.
We are running Postgres 9.2.1
My questions are:
- Are there any known issues around foreign key integrity in this version of Postgres?
- Are there any health-checks that I can perform against a foreign-key index?
I presume the only reasonable fix for this is to drop the constraint, remove the offending row(s) and recreate the constraint?
Thanks
Tim
