Foreign Key constraint violation

Поиск
Список
Период
Сортировка
От Tim Kane
Тема Foreign Key constraint violation
Дата
Msg-id CADVWZZ+RbfoYHfkU3DWLStPhobx2WaRdbP=hDNaZM2VGG29MaA@mail.gmail.com
обсуждение исходный текст
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Arun P.L
Дата:
Сообщение: Type cast errors in version 9.2 while upgrade
Следующее
От: Alban Hertroys
Дата:
Сообщение: Re: Type cast errors in version 9.2 while upgrade