Thread: 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.