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

Creation of a Foreign Key which partially maps to a primary key is accepted without a warning #3983

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

Comments

@monetdb-team
Copy link

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

Date: 2016-04-14 16:10:06 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.21.19 (Jul2015-SP4)
CC: @njnes

Last updated: 2016-06-23 10:24:16 +0200

Comment 22042

Date: 2016-04-14 16:10:06 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:45.0) Gecko/20100101 Firefox/45.0
Build Identifier:

The number of columns of a FK should match the number of columns of the PK or UNIQUE contraint definition.
If the FK has less columns than the PK or UNIQUE definition, than a warning should be given. Currently no warning is given.

Reproducible: Always

Steps to Reproduce:

  1. Start mserver5
  2. Start mclient
  3. Run SQL commands:
    CREATE TABLE parent2(a int, b int, PRIMARY KEY(a,b));
    -- normal correct FK definition:
    CREATE TABLE child1(x int, y int, FOREIGN KEY(x,y) REFERENCES parent2);

-- FK definition with more columns than the PK:
CREATE TABLE child3(x int,y int,z int, FOREIGN KEY(x,y,z) REFERENCES parent2);
-- it correctly returns an error

-- FK definition with less columns than the PK:
CREATE TABLE child2(x int REFERENCES parent2);
-- it is accepted but I prefer to get a warning

-- show PK columns
SELECT * FROM dependencies_columns_on_keys() WHERE usr LIKE 'parent%';

-- show FK columns
SELECT * FROM dependencies_keys_on_foreignkeys() WHERE usr LIKE 'child%';

Actual Results:

Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.23.0 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE parent2(a int, b int, PRIMARY KEY(a,b));
operation successful (34.092ms)
sql>-- normal correct FK definition:
sql>CREATE TABLE child1(x int, y int, FOREIGN KEY(x,y) REFERENCES parent2);
operation successful (14.854ms)
sql>
sql>-- FK definition with more columns than the PK:
sql>CREATE TABLE child3(x int,y int,z int, FOREIGN KEY(x,y,z) REFERENCES parent2);
CONSTRAINT FOREIGN KEY: not all columns are handled
sql>-- it correctly returns an error
sql>
sql>-- FK definition with less columns than the PK:
sql>CREATE TABLE child2(x int REFERENCES parent2);
operation successful (12.990ms)
sql>-- it is accepted but I prefer to get a warning
sql>SELECT * FROM dependencies_columns_on_keys() WHERE usr LIKE 'parent%';
+------+------------------+----------+
| sch | usr | dep_type |
+======+==================+==========+
| a | parent2_a_b_pkey | DEP_KEY |
| b | parent2_a_b_pkey | DEP_KEY |
+------+------------------+----------+
2 tuples (7.291ms)
sql>SELECT * FROM dependencies_keys_on_foreignkeys() WHERE usr LIKE 'child%';
+------------------+-----------------+----------+
| sch | usr | dep_type |
+==================+=================+==========+
| parent2_a_b_pkey | child1_x_y_fkey | DEP_FKEY |
| parent2_a_b_pkey | child2_x_fkey | DEP_FKEY |
+------------------+-----------------+----------+
2 tuples (2.848ms)
sql>

Expected Results:

return a warning after SQL command:
CREATE TABLE child2(x int REFERENCES parent2);

Comment 22043

Date: 2016-04-14 17:02:44 +0200
From: Martin van Dinther <<martin.van.dinther>>

Additional info:
Creation of the partial FK via:
ALTER TABLE "sys"."child2" ADD CONSTRAINT "child2_x_fkey" FOREIGN KEY ("x") REFERENCES "sys"."parent2" ("a");
fails with an error: CONSTRAINT FOREIGN KEY: could not find referenced PRIMARY KEY in table 'parent2'
So there is a difference in behavior when the partial FK is created as part of the CREATE TABLE versus using an ALTER TABLE.

in mclient run additional commands:
sql>\D
START TRANSACTION;
SET SCHEMA "sys";
CREATE TABLE "sys"."parent2" (
"a" INTEGER NOT NULL,
"b" INTEGER NOT NULL,
CONSTRAINT "parent2_a_b_pkey" PRIMARY KEY ("a", "b")
);
CREATE TABLE "sys"."child1" (
"x" INTEGER,
"y" INTEGER
);
CREATE TABLE "sys"."child2" (
"x" INTEGER
);
ALTER TABLE "sys"."child1" ADD CONSTRAINT "child1_x_y_fkey" FOREIGN KEY ("x", "y") REFERENCES "sys"."parent2" ("a", "b");
ALTER TABLE "sys"."child2" ADD CONSTRAINT "child2_x_fkey" FOREIGN KEY ("x") REFERENCES "sys"."parent2" ("a");
COMMIT;
sql>

-- remove the tables
sql>DROP TABLE parent2 CASCADE;
operation successful (26.447ms)
sql>DROP TABLE child1;
operation successful (8.040ms)
sql>DROP TABLE child2;
operation successful (7.883ms)
sql>\D
START TRANSACTION;
COMMIT;

-- use the SQL script output of \D to recreate the tables
sql>SET SCHEMA "sys";
auto commit mode: on
sql>CREATE TABLE "sys"."parent2" (
more>"a" INTEGER NOT NULL,
more>"b" INTEGER NOT NULL,
more>CONSTRAINT "parent2_a_b_pkey" PRIMARY KEY ("a", "b")
more>);
operation successful (35.310ms)
sql>CREATE TABLE "sys"."child1" (
more>"x" INTEGER,
more>"y" INTEGER
more>);
operation successful (14.637ms)
sql>CREATE TABLE "sys"."child2" (
more>"x" INTEGER
more>);
operation successful (15.743ms)
sql>ALTER TABLE "sys"."child1" ADD CONSTRAINT "child1_x_y_fkey" FOREIGN KEY ("x", "y") REFERENCES "sys"."parent2" ("a", "b");
operation successful (22.705ms)
sql>ALTER TABLE "sys"."child2" ADD CONSTRAINT "child2_x_fkey" FOREIGN KEY ("x") REFERENCES "sys"."parent2" ("a");
CONSTRAINT FOREIGN KEY: could not find referenced PRIMARY KEY in table 'parent2'

-- the last statement is not accepted where the FK definition as part of the CREATE TABLE is accepted!

sql>\D
START TRANSACTION;
SET SCHEMA "sys";
CREATE TABLE "sys"."parent2" (
"a" INTEGER NOT NULL,
"b" INTEGER NOT NULL,
CONSTRAINT "parent2_a_b_pkey" PRIMARY KEY ("a", "b")
);
CREATE TABLE "sys"."child1" (
"x" INTEGER,
"y" INTEGER
);
CREATE TABLE "sys"."child2" (
"x" INTEGER
);
ALTER TABLE "sys"."child1" ADD CONSTRAINT "child1_x_y_fkey" FOREIGN KEY ("x", "y") REFERENCES "sys"."parent2" ("a", "b");
COMMIT;
sql>

Comment 22052

Date: 2016-04-16 13:44:07 +0200
From: @njnes

also in single column constraint case check for the referenced side properly

Comment 22053

Date: 2016-04-16 15:33:12 +0200
From: MonetDB Mercurial Repository <>

Changeset 91ab24eed935 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=91ab24eed935

Changeset description:

add test for bug #3983
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