Bug 6131 - FK constraint violated in self table references
Summary: FK constraint violated in self table references
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: x86_64 (amd64/em64t) Linux
: Normal normal
Assignee: SQL devs
: 6132 (view as bug list)
Depends on:
Reported: 2016-11-28 15:34 CET by Dean De Leo
Modified: 2020-09-21 20:32 CEST (History)
0 users

repro.sql (388 bytes, application/sql)
2016-11-28 15:35 CET, Dean De Leo

Note You need to log in before you can comment on or make changes to this bug.
Description Dean De Leo 2016-11-28 15:34:23 CET
User-Agent:       Mozilla/5.0 (X11; Fedora; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2840.100 Safari/537.36
Build Identifier: 

Performing an INSERT seems to violate the foreign key constraints that refer the same table, where the referred key is also the same inserted key. This is inconsistent with the behaviour of disabling the foreign key, inserting the data, and eventually re-enabling the foreign key constraint. In this latter case the INSERT statements succeed.

AFAIK any foreign key constraint should be checked after the update has taken place, so that the former statement should be allowed.

Reproducible: Always

Steps to Reproduce:
1. See attached repro
Actual Results:  
The INSERT statement is rejected due to the foreign key being violated

Expected Results:  
The INSERT statement should be allowed
Comment 1 Dean De Leo 2016-11-28 15:35:09 CET
Created attachment 516 [details]
Comment 2 MonetDB Mercurial Repository cwiconfidential 2017-03-09 17:18:20 CET
Changeset 73b379ba7970, made by Martin van Dinther <martin.van.dinther@monetdbsolutions.com> in the MonetDB repo, refers to this bug.

For complete details, see http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=73b379ba7970

Changeset description:

	Add test for bug 6131
Comment 3 Niels Nes cwiconfidential 2020-09-21 20:32:54 CEST
*** Bug 6132 has been marked as a duplicate of this bug. ***