pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[SQL] composite primary keys.
8+ messages / 6 participants
[nested] [flat]
* [SQL] composite primary keys.
@ 1998-09-04 14:21 Stuart Rison <stuart@ludwig.ucl.ac.uk>
1998-09-04 14:49 ` Re: [SQL] composite primary keys. Dave <david@morgana.estadistica.unam.mx>
0 siblings, 1 reply; 8+ messages in thread
From: Stuart Rison @ 1998-09-04 14:21 UTC (permalink / raw)
To: pgsql-sql; +Cc: stuart@ludwig.ucl.ac.uk
Dear All,
Does postgreSQL 6.3.2 support composite primary keys? If so, how do you
implement these?
If postgreSQL does not support them (yet? ;) ), has anybody come up with a
'fudge' to 'immitate' them?
Could you please cc any postings to stuart@ludwig.ucl.ac.uk.
Best regards,
Stuart.
+-------------------------+--------------------------------------+
| Stuart Rison | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street |
| Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. |
| Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk |
+-------------------------+--------------------------------------+
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] composite primary keys.
1998-09-04 14:21 [SQL] composite primary keys. Stuart Rison <stuart@ludwig.ucl.ac.uk>
@ 1998-09-04 14:49 ` Dave <david@morgana.estadistica.unam.mx>
0 siblings, 0 replies; 8+ messages in thread
From: Dave @ 1998-09-04 14:49 UTC (permalink / raw)
To: Stuart Rison <stuart@ludwig.ucl.ac.uk>; +Cc: pgsql-sql
> Dear All,
>
> Does postgreSQL 6.3.2 support composite primary keys? If so, how do you
> implement these?
Let me know if I understand...
Do you want to build primary keys with more than one element ???
Try with:
create table t1 ( field1 int, field2 int, field3 int,
primary key (field1,field2));
The primary key can contain as many elements as you want. But if what you
want is to activate referential integrity constraints, read about SPI in
your documentation.
Buena Suerte Amigo !!
_______________________________________
David Martinez Cuevas
Direccion General de Estadistica, UNAM - Mexico
Tel. 622-60-80
E-mail david@estadistica.unam.mx
----------------------------------------
^ permalink raw reply [nested|flat] 8+ messages in thread
* Composite primary keys
@ 2009-06-23 15:14 Harald Fuchs <hari.fuchs@gmail.com>
2009-06-23 15:26 ` Re: Composite primary keys Tom Lane <tgl@sss.pgh.pa.us>
2009-06-23 15:32 ` Re: Composite primary keys Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-06-23 18:44 ` Re: Composite primary keys Joshua Tolley <eggyknap@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Harald Fuchs @ 2009-06-23 15:14 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Composite primary keys
2009-06-23 15:14 Composite primary keys Harald Fuchs <hari.fuchs@gmail.com>
@ 2009-06-23 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2009-06-23 15:26 UTC (permalink / raw)
To: Harald Fuchs <hari.fuchs@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Composite primary keys
2009-06-23 15:14 Composite primary keys Harald Fuchs <hari.fuchs@gmail.com>
@ 2009-06-23 15:32 ` Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2 siblings, 0 replies; 8+ messages in thread
From: Oliveiros Cristina @ 2009-06-23 15:32 UTC (permalink / raw)
To: pgsql-sql; Harald Fuchs <hari.fuchs@gmail.com>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Composite primary keys
2009-06-23 15:14 Composite primary keys Harald Fuchs <hari.fuchs@gmail.com>
@ 2009-06-23 18:44 ` Joshua Tolley <eggyknap@gmail.com>
2009-06-23 19:15 ` Re: Composite primary keys Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 8+ messages in thread
From: Joshua Tolley @ 2009-06-23 18:44 UTC (permalink / raw)
To: Harald Fuchs <hari.fuchs@gmail.com>; +Cc: 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
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Composite primary keys
2009-06-23 15:14 Composite primary keys Harald Fuchs <hari.fuchs@gmail.com>
2009-06-23 18:44 ` Re: Composite primary keys Joshua Tolley <eggyknap@gmail.com>
@ 2009-06-23 19:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-06-24 08:37 ` Re: Composite primary keys Harald Fuchs <hari.fuchs@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2009-06-23 19:15 UTC (permalink / raw)
To: Joshua Tolley <eggyknap@gmail.com>; +Cc: Harald Fuchs <hari.fuchs@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Composite primary keys
2009-06-23 15:14 Composite primary keys Harald Fuchs <hari.fuchs@gmail.com>
2009-06-23 18:44 ` Re: Composite primary keys Joshua Tolley <eggyknap@gmail.com>
2009-06-23 19:15 ` Re: Composite primary keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-06-24 08:37 ` Harald Fuchs <hari.fuchs@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Harald Fuchs @ 2009-06-24 08:37 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2009-06-24 08:37 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-09-04 14:21 [SQL] composite primary keys. Stuart Rison <stuart@ludwig.ucl.ac.uk>
1998-09-04 14:49 ` Dave <david@morgana.estadistica.unam.mx>
2009-06-23 15:14 Composite primary keys Harald Fuchs <hari.fuchs@gmail.com>
2009-06-23 15:26 ` Re: Composite primary keys Tom Lane <tgl@sss.pgh.pa.us>
2009-06-23 15:32 ` Re: Composite primary keys Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-06-23 18:44 ` Re: Composite primary keys Joshua Tolley <eggyknap@gmail.com>
2009-06-23 19:15 ` Re: Composite primary keys Tom Lane <tgl@sss.pgh.pa.us>
2009-06-24 08:37 ` Re: Composite primary keys Harald Fuchs <hari.fuchs@gmail.com>
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