Bug 6132

Summary: Wrong identifier reported when a FK constraint is violated
Product: SQL Reporter: Dean De Leo <dleo>
Component: allAssignee: SQL devs <bugs-sql>
Status: RESOLVED DUPLICATE    
Severity: normal CC: niels
Priority: Normal    
Version: -- development   
Hardware: x86_64 (amd64/em64t)   
OS: Linux   
Attachments: repro.sql

Description Dean De Leo 2016-11-28 15:40:43 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: 

In case a FK constraint is violated during an INSERT, MonetDB prints the wrong identifier of the constraint being violated.

The repro is also related to bug #6131

Reproducible: Always

Steps to Reproduce:
1. See attached repro script
Comment 1 Dean De Leo 2016-11-28 15:41:26 CET
Created attachment 517 [details]
repro.sql
Comment 2 Sjoerd Mullender cwiconfidential 2019-01-18 11:39:55 CET
The problem is actually that any constraint is being violated.  There are no constraints being violated, but the serever reports that one is.
If you were to add the constraints *after* the insert (using ALTER TABLE), no constraints will be reported as being violated.

The real problem is that you have a constraint on one column of a table that references another column of that same table, and in addition that you're adding a tuple where the referenced value is added at the same time as the referencing value (i.e. both the primary and foreign key are in the same tuple).

This works:
CREATE TABLE test102(
  T INT NOT NULL PRIMARY KEY
);

CREATE TABLE test101(
  A INT NOT NULL PRIMARY KEY,
  B INT NOT NULL,
  C INT NOT NULL
);

INSERT INTO test102 VALUES (102); --ok

INSERT INTO test101 VALUES (101, 102, 101);

ALTER TABLE test101 ADD CONSTRAINT "fB" FOREIGN KEY (B) REFERENCES test102(T);
ALTER TABLE test101 ADD CONSTRAINT "fC" FOREIGN KEY (C) REFERENCES test101(A);
Comment 3 Niels Nes cwiconfidential 2019-01-23 13:40:20 CET
similar problem as bug 6131 then
Comment 4 Niels Nes cwiconfidential 2020-09-21 20:32:54 CEST

*** This bug has been marked as a duplicate of bug 6131 ***