Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

foreign key self references cause violation upon alter table add constraint... #2862

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2011-08-17 14:26:12 +0200
From: @grobian
To: SQL devs <>
Version: 11.3.7 (Apr2011-SP2) [obsolete]
CC: @njnes, @skinkie

Last updated: 2012-02-29 10:34:23 +0100

Comment 16109

Date: 2011-08-17 14:26:12 +0200
From: @grobian

% cat xsight-dump-device-table.sql
START TRANSACTION;

CREATE TABLE "device" (
"deviceid" int,
"parentdeviceid" int,
CONSTRAINT "device_pk" PRIMARY KEY ("deviceid")
);
COPY 2 RECORDS INTO "device" FROM stdin USING DELIMITERS '\t','\n','"';
2 NULL
23 2
ALTER TABLE "device" ADD CONSTRAINT "device_device_fk" FOREIGN KEY ("parentdeviceid") REFERENCES "device" ("deviceid");
--COMMIT;

% mclient xsight xsight-dump-device-table.sql
auto commit mode: off
operation successful
2 affected rows
SQLException:assert:UPDATE: FOREIGN KEY constraint 'device.device_device_fk' violated

This used to work, and it works fine if the constraint is there from the start and inserts are being used.
A dump/restore of such table hence fails.

Comment 16110

Date: 2011-08-18 12:46:31 +0200
From: @grobian

copy-n-npasta

START TRANSACTION;

CREATE TABLE "device" (
"deviceid" int,
"parentdeviceid" int,
CONSTRAINT "device_pk" PRIMARY KEY ("deviceid")
);
COPY 2 RECORDS INTO "device" FROM stdin USING DELIMITERS ',','\n','"';
2,NULL
23,2
ALTER TABLE "device" ADD CONSTRAINT "device_device_fk" FOREIGN KEY ("parentdeviceid") REFERENCES "device" ("deviceid");
--COMMIT;

Comment 16124

Date: 2011-08-22 00:01:52 +0200
From: @njnes

Changeset 98fdeb1daa49 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=98fdeb1daa49

Changeset description:

fixed bug #2862
was a name resolution problem. Now we use unique 'table' name for
the refered table.

Comment 16125

Date: 2011-08-22 00:05:07 +0200
From: @njnes

fixed. The join between non-null values and full referenced columns was done
incorrect. This is solve by using unique names for this join.

Comment 16129

Date: 2011-08-22 08:17:22 +0200
From: @drstmane

We should consider adding a test (also) for this one.

Comment 16136

Date: 2011-08-22 16:41:13 +0200
From: @drstmane

Changeset fa813932a753 made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=fa813932a753

Changeset description:

added tests for bug #2852 & bug #2862

Comment 16242

Date: 2011-09-16 15:04:35 +0200
From: @sjoerdmullender

The Aug2011 version has been released.

Comment 16841

Date: 2012-01-29 00:46:56 +0100
From: @skinkie

It seems to be back in HEAD.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant