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