Re: Is this a buggy behavior?

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