On 2024-03-24 15:25 +0100, sud wrote:
> Create a table and composite primary key. But to my surprise it allowed me
> to have the composite primary key created even if one of the columns was
> defined as nullable. But then inserting the NULL into that column erroring
> out at the first record itself , stating "not null constraint" is violated.
>
> CREATE TABLE test1
> (
> c1 varchar(36) NULL ,
> c2 varchar(36) NOT NULL ,
> CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
> ) ;
>
> -- Table created without any error even one of the columns in the PK was
> defined as NULL.
>
> insert into test1 values(null,'123');
>
>
> *ERROR: null value in column "c1" of relation "test1" violates not-null
> constraintDETAIL: Failing row contains (null, 123).*
>
> insert into test1 values('123','123');
>
> --works fine as expected
This is required by the SQL standard: columns of a primary key must be
NOT NULL. Postgres automatically adds the missing NOT NULL constraints
when defining a primary key. You can verify that with \d test1 in psql.
Do you come from sqlite? That allows NULL in primary key columns
without an explicit NOT NULL constraint.
--
Erik