To: pgsql-sql@postgresql.org
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Composite primary keys
Date: Tue, 23 Jun 2009 17:14:36 +0200
Lines: 30
Message-ID: <pu1vpb2d9f.fsf@srv.protecting.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
Cancel-Lock: sha1:GCp9hCyaTl1m2lJBRJnzuJVW8KI=

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.