From: Oliveiros Cristina <oliveiros.cristina@marktest.pt>
To: pgsql-sql@postgresql.org
To: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Re: Composite primary keys
Date: Tue, 23 Jun 2009 16:32:01 +0100
Message-ID: <070201c9f417$c0e29630$ec5a3d0a@marktestcr.marktest.pt> (raw)
References: <pu1vpb2d9f.fsf@srv.protecting.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@marktest.pt, hari.fuchs@gmail.com
Subject: Re: Composite primary keys
In-Reply-To: <070201c9f417$c0e29630$ec5a3d0a@marktestcr.marktest.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox