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

UNIQUE Constraint is not enforced #2835

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

UNIQUE Constraint is not enforced #2835

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

Comments

@monetdb-team
Copy link

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

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.

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