Message-ID: <070201c9f417$c0e29630$ec5a3d0a@marktestcr.marktest.pt>
From: "Oliveiros Cristina" <oliveiros.cristina@marktest.pt>
To: <pgsql-sql@postgresql.org>,
	"Harald Fuchs" <hari.fuchs@gmail.com>
References: <pu1vpb2d9f.fsf@srv.protecting.net>
Subject: Re: Composite primary keys
Date: Tue, 23 Jun 2009 16:32:01 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit

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