From: | Tim Kane <tim(dot)kane(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Foreign Key constraint violation |
Date: | 2013-06-18 13:08:21 |
Message-ID: | CADVWZZ+RbfoYHfkU3DWLStPhobx2WaRdbP=hDNaZM2VGG29MaA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2013-06-18 13:29:18 | Re: Type cast errors in version 9.2 while upgrade |
Previous Message | Arun P.L | 2013-06-18 12:38:06 | Type cast errors in version 9.2 while upgrade |