Postgres Pro
Downloads
Home   >   mailing lists

Thread: Foreign Key violated

Foreign Key violated

From
Keith Fiske
Date:
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

Re: Foreign Key violated

From
Thom Brown
Date:
On 23 May 2013 10:15, Keith Fiske <keith@omniti.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;

--
Thom

Re: Foreign Key violated

From
Serge Fonville
Date:
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


2013/5/23 Keith Fiske <keith@omniti.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

Re: Foreign Key violated

From
Thom Brown
Date:
On 23 May 2013 15:33, Thom Brown <thom@linux.com> wrote:
> On 23 May 2013 10:15, Keith Fiske <keith@omniti.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

Re: Foreign Key violated

From
Keith Fiske
Date:
Apologies for not replying sooner. After a few days, we actually found out the cause was a user turning off all triggers on the table, forcing some data into it to try and solve an RMA issue manually, then turning the triggers back on. This hadn't showed up on any logs, and after finding zero signs of corruption or other FKs being violated, we asked the for more information about what had been done recently and they fessed up.

So, relief on one hand that there was no data corruption. But a bit troubling that the user did that :p

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com


On Wed, May 29, 2013 at 10:52 AM, Thom Brown <thom@linux.com> wrote:
On 23 May 2013 15:33, Thom Brown <thom@linux.com> wrote:
> On 23 May 2013 10:15, Keith Fiske <keith@omniti.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