Re: Foreign Key violated

Hi,

I'm not sure if I understand your issue, but could you output
SELECT
COUNT(*)
FROM rmas
WHERE
id = 1008122437
AND status = 'r';

HTH

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl

Convince Microsoft!
They need to add TRUNCATE PARTITION in SQL Server
https://connect.microsoft.com/SQLServer/feedback/details/417926/truncate-partition-of-partitioned-table

2013/5/23 Keith Fiske <keith(at)omniti(dot)com>

> 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
>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> http://www.keithf4.com
>