Re: Foreign Key violated

On 23 May 2013 15:33, Thom Brown <thom(at)linux(dot)com> wrote:
> On 23 May 2013 10:15, Keith Fiske <keith(at)omniti(dot)com> wrote:
>> Client reported an issue where it appears a foreign key has been violated
>>
>> prod=#\d rma_items
>> [snip]
>> rma_items_rma_id_status_fk" FOREIGN KEY (rma_id, rma_status) REFERENCES
>> rmas(id, status) ON UPDATE CASCADE ON DELETE CASCADE
>>
>> prod=# select i.rma_id, i.rma_status, r.id, r.status from rmas r join
>> rma_items i on i.rma_id = r.id and i.rma_status != r.status;
>> rma_id | rma_status | id | status
>> ------------+------------+------------+--------
>> 1008122437 | r | 1008122437 | c
>> (1 row)
>>
>>
>> Attempting to reinsert this data again causes a violation error, so it
>> doesn't appear to be broken
>>
>> prod=# begin;
>> BEGIN
>> prod=# insert into rma_items (rma_id, order_item_id, return_reason_id,
>> rma_status) values (1008122437, 1007674099, 9797623, 'r');
>> ERROR: insert or update on table "rma_items" violates foreign key
>> constraint "rma_items_rma_id_status_fk"
>> DETAIL: Key (rma_id, rma_status)=(1008122437, r) is not present in table
>> "rmas".
>> prod=# rollback;
>> ROLLBACK
>>
>> This is running 9.2.4 on CentOS. If anyone can suggest how I can look into
>> this deeper and find what the problem may be, I'd appreciate it. I'm here at
>> PGCon if anyone is available to help IRL as well
>
> What do you get with:
>
> SELECT conname
> FROM pg_constraint
> WHERE NOT convalidated;

Did you resolve this?

--
Thom