Foreign Key prblems

[monetdb-team]

Date: 2012-07-05 22:58:47 +0200
From: Benjie Chen <>
To: SQL devs <>
Version: 11.11.5 (Jul2012)
CC: @bartscheers, @njnes

Last updated: 2012-11-27 14:19:57 +0100

Comment 17415

Date: 2012-07-05 22:58:47 +0200
From: Benjie Chen <>

Consider the following model:

CREATE TABLE "sys"."first" (
"id" int NOT NULL,
CONSTRAINT "first_id_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "sys"."second" (
"id" int NOT NULL,
"ref" int,
CONSTRAINT "second_id_pkey" PRIMARY KEY ("id"),
CONSTRAINT "second_ref_fkey" FOREIGN KEY
("ref") REFERENCES "sys"."first" ("id")
);

What works, and should work:

insert into "second" values (100, null);

What does not work, and should not work:

sql>insert into "second" values (101, 1);
M0M29!INSERT INTO: FOREIGN KEY constraint 'second.second_ref_fkey' violated

What work, but should not work (note that I've never added anything to first):

sql>update "second" set ref = 1 WHERE id = 100;
1 affected row (2.020ms)

What does not work, but should work (note that this is executed after the last statement):

sql>update "second" set ref = null WHERE id = 100;
M0M29!UPDATE: FOREIGN KEY constraint 'second.second_ref_fkey' violated

Comment 17416

Date: 2012-07-05 22:59:35 +0200
From: Benjie Chen <>

Database: MonetDB v11.9.7 (Apr2012-SP2)

Comment 17423

Date: 2012-07-06 15:36:31 +0200
From: Benjie Chen <>

(In reply to comment 1)

Database: MonetDB v11.9.7 (Apr2012-SP2)

I want to add that if table "first" is not empty, then the constraints are checked correctly. I.e. here is the complete trace:

CREATE TABLE "sys"."first" (
"id" int NOT NULL,
CONSTRAINT "first_id_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "sys"."second" (
"id" int NOT NULL,
"ref" int,
CONSTRAINT "second_id_pkey" PRIMARY KEY ("id"),
CONSTRAINT "second_ref_fkey" FOREIGN KEY ("ref") REFERENCES "sys"."first" ("id")
);

What works, and should work:

insert into "second" values (100, null);

What does not work, and should not work:

sql>insert into "second" values (101, 1);
M0M29!INSERT INTO: FOREIGN KEY constraint 'second.second_ref_fkey' violated

What work, but should not work (note that I've never added anything to first):

sql>update "second" set ref = 1 WHERE id = 100;
1 affected row (2.020ms)

What does not work, but should work (note that this is executed after the last statement):

sql>update "second" set ref = null WHERE id = 100;
M0M29!UPDATE: FOREIGN KEY constraint 'second.second_ref_fkey' violated

Now insert something into "first"

insert into "first" values (200);

Try update again - this succeeded when it suppose to fail earlier, and now correctly fails.

sql>update "second" set ref = 1 WHERE id = 100;
M0M29!UPDATE: FOREIGN KEY constraint 'second.second_ref_fkey' violated

Comment 17427

Date: 2012-07-06 17:12:10 +0200
From: @njnes

Changeset 03516346b4d5 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=03516346b4d5

Changeset description:

fixed bugs 3113 and 3114

3113: single column indices aren't stored, ie don't have updates

3114: properly use the update 'column expression' instead of the original
column expression.

Comment 17429

Date: 2012-07-06 17:13:41 +0200
From: @njnes

use proper updated column expression

Comment 17430

Date: 2012-07-07 05:36:03 +0200
From: Benjie Chen <>

Applied and compiled the changeset, and confirming that the bug has been solved. Thanks. Eagerly awaiting for the official release.

(In reply to comment 3)

Changeset 03516346b4d5 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=03516346b4d5

Changeset description:

fixed bugs 3113 and 3114

3113: single column indices aren't stored, ie don't have updates

3114: properly use the update 'column expression' instead of the original
column expression.

Comment 17473

Date: 2012-07-17 13:46:39 +0200
From: @grobian

Fix delivered in Jul2012 release