Date: 2011-07-08 11:53:57 +0200
From: Simon Brodt <<simon.brodt>>
To: SQL devs <>
Version: 11.3.3 (Apr2011-SP1) [obsolete]
CC: @njnes

Last updated: 2011-07-29 10:52:46 +0200

Comment 15923

Date: 2011-07-08 11:53:57 +0200
From: Simon Brodt <<simon.brodt>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:5.0) Gecko/20100101 Firefox/5.0
Build Identifier:

A UNIQUE constraint on an attribute that references another table is not enforced.

Reproducible: Always

Steps to Reproduce:

CREATE TABLE "test"."test" ("id" INT PRIMARY KEY );

CREATE TABLE "test"."test2" ("ref" INT REFERENCES "test"."test" , CONSTRAINT "unique" UNIQUE ("ref") );

INSERT INTO "test"."test" VALUES ('0');

INSERT INTO "test"."test2" VALUES ('0');

INSERT INTO "test"."test2" VALUES ('0');

Actual Results:

Two equal tuples in the database.

Expected Results:

Insertion error when attempting to insert the second tuple.

Comment 15925

Date: 2011-07-08 12:40:23 +0200
From: @grobian

confirmed, most likely the unique isn't respected because the foreign key

Comment 15927

Date: 2011-07-08 14:21:43 +0200
From: @grobian

It works when created as follows:

create table test (id int primary key);
create table test2 (id int unique references sys.test);

insert into test values (1);
insert into test2 values (1);

insert into test2 values (1);
SQLException:assert:INSERT INTO: UNIQUE constraint 'test2.test2_id_unique' violated
insert into test2 values (2);
SQLException:assert:INSERT INTO: FOREIGN KEY constraint 'test2.test2_id_fkey' violated

the following create statement also works correctly:

CREATE TABLE "test3" (
"id" INTEGER,
CONSTRAINT "test3_id_unique" UNIQUE ("id"),
CONSTRAINT "test3_id_fkey" FOREIGN KEY ("id") REFERENCES "sys"."test" ("id")
);

If the two constraints are swapped in order (like the original bug) then the unique check appears to be skipped.

Comment 15940

Date: 2011-07-13 19:26:37 +0200
From: @njnes

Changeset 6cc71eb539a4 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=6cc71eb539a4

Changeset description:

fix bug #2835 in checking ukeys

Comment 15941

Date: 2011-07-13 21:37:10 +0200
From: @njnes

bug consisted of 2 problems 1) single column keys (with columns equal to some other key constrained), weren't created

inserting index values for multiple indices failed .

both are resolved

Comment 15942

Date: 2011-07-13 21:54:43 +0200
From: @njnes

Changeset aaf264c72b6e 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=aaf264c72b6e

Changeset description:

fix bug #2835 in checking ukeys
(transplanted from 6cc71eb539a4d8e5fa3b7bb442d68ab647d1f7ad)

Comment 15952

Date: 2011-07-17 14:16:56 +0200
From: @drstmane

Changeset a4f1b7cdc6e9 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=a4f1b7cdc6e9

Changeset description:

added test for bug #2835

Comment 16012

Date: 2011-07-29 10:52:46 +0200
From: @sjoerdmullender

The Apr2011-SP2 bugfix release is out.