Why can I create a table with PRIMARY KEY on a nullable column?
- by AlexKuznetsov
The following code creates a table without raising any errors:
CREATE TABLE test(
ID INTEGER NULL,
CONSTRAINT PK_test PRIMARY KEY(ID)
)
Note that I cannot insert a NULL, as expected:
INSERT INTO test
VALUES(1),(NULL)
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null).
********** Error **********
ERROR: null value in column "id" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null).
Why can I create a table with a self-contradictory definition? ID column is explicitly declared as NULLable, and it is implicitly not nullable, as a part of the PRIMARY KEY. Does it make sense?
Edit: would it not be better if this self-contradictory CREATE TABLE just failed right there?
