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

Removing a NOT NULL constraint from a PKey column should NOT be allowed #6189

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

Comments

@monetdb-team
Copy link

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

Date: 2017-01-19 14:01:57 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.25.3 (Dec2016)
CC: @njnes

Last updated: 2017-03-03 10:24:52 +0100

Comment 24895

Date: 2017-01-19 14:01:57 +0100
From: Martin van Dinther <<martin.van.dinther>>

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

​​​​​Removing a NOT NULL constraint from a column which is part of the Pkey should NOT be allowed.
By definition all Primary Key columns in SQL databases are always NOT NULLable.
Even if columns are specified as NULL but included in a PRIMARY KEY clause, they are automatically created as NOT NULL during CREATE TABLE, see below example of PKeyImplicitNotNull.

The removal of a NOT NULL constraint is done via: ALTER TABLE tbl_nm ALTER col_nm SET NULL;

Reproducible: Always

Steps to Reproduce:

  1. Start mserver5 ()
  2. Start mclient
  3. Run SQL commands:
    CREATE TABLE "PKeyNotNull" (
    "PK_COL1" varchar(4) NOT NULL,
    "PK_COL2" varchar(4) NOT NULL,
    "DataCol" int DEFAULT NULL,
    PRIMARY KEY ("PK_COL1","PK_COL2") );

\d "PKeyNotNull"

INSERT INTO "PKeyNotNull" values ('C12', NULL, 1);
-- Error: INSERT INTO: NOT NULL constraint violated for column PKeyNotNull.PK_COL2

ALTER TABLE "PKeyNotNull" ALTER "PK_COL2" SET NULL;
-- this is allowed but should NOT be allowed
INSERT INTO "PKeyNotNull" values ('C12', NULL, 2);

SELECT * FROM "PKeyNotNull";

DROP TABLE "PKeyNotNull";

CREATE TABLE "PKeyImplicitNotNull" (
"PK_COL1" varchar(4) NULL,
"PK_COL2" varchar(4) NULL,
"DataCol" int DEFAULT NULL,
PRIMARY KEY ("PK_COL1","PK_COL2") );

\d "PKeyImplicitNotNull"

INSERT INTO "PKeyImplicitNotNull" values ('C12', NULL, 1);
-- Error: INSERT INTO: NOT NULL constraint violated for column PKeyImplicitNotNull.PK_COL2

ALTER TABLE "PKeyImplicitNotNull" ALTER "PK_COL2" SET NULL;
-- this is allowed but should NOT be allowed
INSERT INTO "PKeyImplicitNotNull" values ('C12', NULL, 2);

SELECT * FROM "PKeyImplicitNotNull";

DROP TABLE "PKeyImplicitNotNull";

Actual Results:

sql>CREATE TABLE "PKeyNotNull" (
more>"PK_COL1" varchar(4) NOT NULL,
more>"PK_COL2" varchar(4) NOT NULL,
more>"DataCol" int DEFAULT NULL,
more>PRIMARY KEY ("PK_COL1","PK_COL2") );
operation successful (28.923ms)
sql>
sql>\d "PKeyNotNull"
CREATE TABLE "sys"."PKeyNotNull" (
"PK_COL1" VARCHAR(4) NOT NULL,
"PK_COL2" VARCHAR(4) NOT NULL,
"DataCol" INTEGER,
CONSTRAINT "PKeyNotNull_PK_COL1_PK_COL2_pkey" PRIMARY KEY ("PK_COL1", "PK_COL2")
);
sql>
sql>INSERT INTO "PKeyNotNull" values ('C12', NULL, 1);
INSERT INTO: NOT NULL constraint violated for column PKeyNotNull.PK_COL2
sql>-- Error: INSERT INTO: NOT NULL constraint violated for column PKeyNotNull.PK_COL2
sql>
sql>ALTER TABLE "PKeyNotNull" ALTER "PK_COL2" SET NULL;
operation successful (5.891ms)
sql>-- this is allowed but should NOT be allowed
sql>INSERT INTO "PKeyNotNull" values ('C12', NULL, 2);
1 affected row (7.429ms)
sql>
sql>SELECT * FROM "PKeyNotNull";
+---------+---------+---------+
| PK_COL1 | PK_COL2 | DataCol |
+=========+=========+=========+
| C12 | null | 2 |
+---------+---------+---------+
1 tuple (1.759ms)
sql>
sql>DROP TABLE "PKeyNotNull";
operation successful (6.041ms)
sql>
sql>
sql>
sql>CREATE TABLE "PKeyImplicitNotNull" (
more>"PK_COL1" varchar(4) NULL,
more>"PK_COL2" varchar(4) NULL,
more>"DataCol" int DEFAULT NULL,
more>PRIMARY KEY ("PK_COL1","PK_COL2") );
operation successful (15.253ms)
sql>
sql>\d "PKeyImplicitNotNull"
CREATE TABLE "sys"."PKeyImplicitNotNull" (
"PK_COL1" VARCHAR(4) NOT NULL,
"PK_COL2" VARCHAR(4) NOT NULL,
"DataCol" INTEGER,
CONSTRAINT "PKeyImplicitNotNull_PK_COL1_PK_COL2_pkey" PRIMARY KEY ("PK_COL1", "PK_COL2")
);
sql>
sql>INSERT INTO "PKeyImplicitNotNull" values ('C12', NULL, 1);
INSERT INTO: NOT NULL constraint violated for column PKeyImplicitNotNull.PK_COL2
sql>-- Error: INSERT INTO: NOT NULL constraint violated for column PKeyImplicitNotNull.PK_COL2
sql>
sql>ALTER TABLE "PKeyImplicitNotNull" ALTER "PK_COL2" SET NULL;
operation successful (10.473ms)
sql>-- this is allowed but should NOT be allowed
sql>INSERT INTO "PKeyImplicitNotNull" values ('C12', NULL, 2);
1 affected row (7.306ms)
sql>
sql>SELECT * FROM "PKeyImplicitNotNull";
+---------+---------+---------+
| PK_COL1 | PK_COL2 | DataCol |
+=========+=========+=========+
| C12 | null | 2 |
+---------+---------+---------+
1 tuple (1.764ms)
sql>
sql>DROP TABLE "PKeyImplicitNotNull";
operation successful (5.862ms)
sql>

Expected Results:

sql>CREATE TABLE "PKeyNotNull" (
more>"PK_COL1" varchar(4) NOT NULL,
more>"PK_COL2" varchar(4) NOT NULL,
more>"DataCol" int DEFAULT NULL,
more>PRIMARY KEY ("PK_COL1","PK_COL2") );
operation successful (28.923ms)
sql>
sql>\d "PKeyNotNull"
CREATE TABLE "sys"."PKeyNotNull" (
"PK_COL1" VARCHAR(4) NOT NULL,
"PK_COL2" VARCHAR(4) NOT NULL,
"DataCol" INTEGER,
CONSTRAINT "PKeyNotNull_PK_COL1_PK_COL2_pkey" PRIMARY KEY ("PK_COL1", "PK_COL2")
);
sql>
sql>INSERT INTO "PKeyNotNull" values ('C12', NULL, 1);
INSERT INTO: NOT NULL constraint violated for column PKeyNotNull.PK_COL2
sql>-- Error: INSERT INTO: NOT NULL constraint violated for column PKeyNotNull.PK_COL2
sql>
sql>ALTER TABLE "PKeyNotNull" ALTER "PK_COL2" SET NULL;
ALTER TABLE: Not allowed to make column PKeyNotNull.PK_COL2 NULLable as it is part of the Primary Key.
sql>-- this is allowed but should NOT be allowed
sql>INSERT INTO "PKeyNotNull" values ('C12', NULL, 2);
INSERT INTO: NOT NULL constraint violated for column PKeyNotNull.PK_COL2
sql>
sql>SELECT * FROM "PKeyNotNull";
+---------+---------+---------+
| PK_COL1 | PK_COL2 | DataCol |
+=========+=========+=========+
+---------+---------+---------+
0 tuples (1.759ms)
sql>
sql>DROP TABLE "PKeyNotNull";
operation successful (6.041ms)
sql>
sql>
sql>
sql>CREATE TABLE "PKeyImplicitNotNull" (
more>"PK_COL1" varchar(4) NULL,
more>"PK_COL2" varchar(4) NULL,
more>"DataCol" int DEFAULT NULL,
more>PRIMARY KEY ("PK_COL1","PK_COL2") );
operation successful (15.253ms)
sql>
sql>\d "PKeyImplicitNotNull"
CREATE TABLE "sys"."PKeyImplicitNotNull" (
"PK_COL1" VARCHAR(4) NOT NULL,
"PK_COL2" VARCHAR(4) NOT NULL,
"DataCol" INTEGER,
CONSTRAINT "PKeyImplicitNotNull_PK_COL1_PK_COL2_pkey" PRIMARY KEY ("PK_COL1", "PK_COL2")
);
sql>
sql>INSERT INTO "PKeyImplicitNotNull" values ('C12', NULL, 1);
INSERT INTO: NOT NULL constraint violated for column PKeyImplicitNotNull.PK_COL2
sql>-- Error: INSERT INTO: NOT NULL constraint violated for column PKeyImplicitNotNull.PK_COL2
sql>
sql>ALTER TABLE "PKeyImplicitNotNull" ALTER "PK_COL2" SET NULL;
ALTER TABLE: Not allowed to make column PKeyImplicitNotNull.PK_COL2 NULLable as it is part of the Primary Key.
sql>-- this is allowed but should NOT be allowed
sql>INSERT INTO "PKeyImplicitNotNull" values ('C12', NULL, 2);
INSERT INTO: NOT NULL constraint violated for column PKeyImplicitNotNull.PK_COL2
sql>
sql>SELECT * FROM "PKeyImplicitNotNull";
+---------+---------+---------+
| PK_COL1 | PK_COL2 | DataCol |
+=========+=========+=========+
+---------+---------+---------+
0 tuples (1.764ms)
sql>
sql>DROP TABLE "PKeyImplicitNotNull";
operation successful (5.862ms)
sql>

Primary Key constraints may not include NULLable columns, but Unique constraints may. So if a NULLable column needs to be part of a uniqueness constraint use UNIQUE clause.

Comment 24938

Date: 2017-02-01 14:01:22 +0100
From: @njnes

added checks

Comment 25129

Date: 2017-03-03 10:24:52 +0100
From: @sjoerdmullender

Dec2016-SP2 has been released, incorporating the fix.

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