Обсуждение: Composite primary keys

I tried to throw some invalid SQL to PostgreSQL and found its reaction
confusing:
 $ psql test            psql (8.4beta2) Type "help" for help.
 test=# CREATE TABLE t1 ( test(#   id serial NOT NULL, test(#   name text NOT NULL, test(#   PRIMARY KEY (id) test(# );
CREATETABLE test=# CREATE TABLE t2 ( test(#   id int NOT NULL REFERENCES t1, test(#   language char(3) NULL, test(#
txttext NOT NULL, test(#   PRIMARY KEY (id, language) test(# ); CREATE TABLE
 

Here's my first gripe: PostgreSQL accepts this silently instead of complaining.
 test=# INSERT INTO t1 (id, name) VALUES (1, 'text 1'); INSERT 0 1 test=# INSERT INTO t2 (id, language, txt) VALUES (2,
NULL,'text 1 no language'); ERROR:  null value in column "language" violates not-null constraint
 

And here's my second gripe: although PostgreSQL correctly rejects the
INSERT it just has ignored my NULL specification.

Harald Fuchs <hari.fuchs@gmail.com> writes:
> I tried to throw some invalid SQL to PostgreSQL and found its reaction
> confusing:

>   test(#   language char(3) NULL,

This is documented as being a no-op specification.
        regards, tom lane

Howdy!

When you say that pg accepts "this" silently instead of complaining what are 
you referring to exactly?

First Insert? Why wouldn't it work after all ?

What will happen is that when you try to insert a new record without 
specifying the id column you'll get an error informing that
primary key constraint is being violated. But IMHO the first INSERT is legal 
SQL....

Best,
Oliveiros

----- Original Message ----- 
From: "Harald Fuchs" <hari.fuchs@gmail.com>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, June 23, 2009 4:14 PM
Subject: [SQL] Composite primary keys


>I tried to throw some invalid SQL to PostgreSQL and found its reaction
> confusing:
>
>  $ psql test
>  psql (8.4beta2)
>  Type "help" for help.
>
>  test=# CREATE TABLE t1 (
>  test(#   id serial NOT NULL,
>  test(#   name text NOT NULL,
>  test(#   PRIMARY KEY (id)
>  test(# );
>  CREATE TABLE
>  test=# CREATE TABLE t2 (
>  test(#   id int NOT NULL REFERENCES t1,
>  test(#   language char(3) NULL,
>  test(#   txt text NOT NULL,
>  test(#   PRIMARY KEY (id, language)
>  test(# );
>  CREATE TABLE
>
> Here's my first gripe: PostgreSQL accepts this silently instead of 
> complaining.
>
>  test=# INSERT INTO t1 (id, name) VALUES (1, 'text 1');
>  INSERT 0 1
>  test=# INSERT INTO t2 (id, language, txt) VALUES (2, NULL, 'text 1 no 
> language');
>  ERROR:  null value in column "language" violates not-null constraint
>
> And here's my second gripe: although PostgreSQL correctly rejects the
> INSERT it just has ignored my NULL specification.
>
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql 

On Tue, Jun 23, 2009 at 05:14:36PM +0200, Harald Fuchs wrote:
>   test=# CREATE TABLE t2 (
>   test(#   id int NOT NULL REFERENCES t1,
>   test(#   language char(3) NULL,
>   test(#   txt text NOT NULL,
>   test(#   PRIMARY KEY (id, language)
>   test(# );
>   CREATE TABLE

<snip>

>   test=# INSERT INTO t2 (id, language, txt) VALUES (2, NULL, 'text 1 no language');
>   ERROR:  null value in column "language" violates not-null constraint

Primary keys are NOT NULL and UNIQUE. You can't have null values in a primary
key.

- Josh / eggyknap

Joshua Tolley <eggyknap@gmail.com> writes:
> Primary keys are NOT NULL and UNIQUE. You can't have null values in a primary
> key.

On reflection I think the OP's beef is that we complain about this:

regression=# create table t (f1 int null not null);
ERROR:  conflicting NULL/NOT NULL declarations for column "f1" of table "t"

but not this:

regression=# create table t (f1 int null primary key);    
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
CREATE TABLE

even though the implied NOT NULL is really a conflict.  I think we could
fix that case if we cared to.  However, since the NULL clause is
forgotten about after parsing, there isn't anything we could do to raise
a complaint about doing it in two steps:

regression=# create table t (f1 int null);            
CREATE TABLE
regression=# alter table t add primary key(f1);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "t_pkey" for table "t"
ALTER TABLE

(barring remembering the NULL clause in the catalogs, which seems
entirely silly).  So I'm not sure how interesting it is to complain
about the single-command case.
        regards, tom lane

In article <24680.1245784517@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:

> Joshua Tolley <eggyknap@gmail.com> writes:
>> Primary keys are NOT NULL and UNIQUE. You can't have null values in a primary
>> key.

> On reflection I think the OP's beef is that we complain about this:

> regression=# create table t (f1 int null not null);
> ERROR:  conflicting NULL/NOT NULL declarations for column "f1" of table "t"

> but not this:

> regression=# create table t (f1 int null primary key);    
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
> CREATE TABLE

> even though the implied NOT NULL is really a conflict.

Yes, that's exactly what I found strange.

> I think we could
> fix that case if we cared to.  However, since the NULL clause is
> forgotten about after parsing, there isn't anything we could do to raise
> a complaint about doing it in two steps:

> regression=# create table t (f1 int null);            
> CREATE TABLE
> regression=# alter table t add primary key(f1);
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "t_pkey" for table "t"
> ALTER TABLE

> (barring remembering the NULL clause in the catalogs, which seems
> entirely silly).

I thought nullability is the default anyway, so indeed no need to
remember it.  My gripe was really the first case, where you contradict
yourself in a single DDL statement.