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.