Search Postgresql Archives

Foreign Key constraint violation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux