Bug 6132 - Wrong identifier reported when a FK constraint is violated
Summary: Wrong identifier reported when a FK constraint is violated
Status: RESOLVED DUPLICATE of bug 6131
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: x86_64 (amd64/em64t) Linux
: Normal normal
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-11-28 15:40 CET by Dean De Leo
Modified: 2020-09-21 20:32 CEST (History)
1 user (show)



Attachments
repro.sql (509 bytes, application/sql)
2016-11-28 15:41 CET, Dean De Leo
Details

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: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 ***