Bug 6131

Summary: FK constraint violated in self table references
Product: SQL Reporter: Dean De Leo <dleo>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: normal    
Priority: Normal    
Version: -- development   
Hardware: x86_64 (amd64/em64t)   
OS: Linux   
Attachments: repro.sql

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]
repro.sql
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. ***