two primairy key in one table ?

Hello,
I have a database model where a many to many relation is used, so i need
to create a table with two primary key. Don't know how to solve this in
postgress, can't find it in the tutorials, so can anybody help me with
this ?

A. Luyf

On Thu, 04 Jul 2002 15:33:11 +0200, Angela Luyf <a(dot)c(dot)luyf(at)amc(dot)uva(dot)nl>
wrote:
>Hello,
>I have a database model where a many to many relation is used, so i need
>to create a table with two primary key. Don't know how to solve this in
>postgress, can't find it in the tutorials, so can anybody help me with
>this ?
>
Angela,

you most probably need a primary key with two columns. Does this come
near to what you want?

CREATE TABLE a (
id INT NOT NULL PRIMARY KEY
/* , anything else ... */
);

CREATE TABLE b (
id INT NOT NULL PRIMARY KEY
/* , anything else ... */
);

CREATE TABLE atob (
a_id INT NOT NULL REFERENCES a,
b_id INT NOT NULL REFERENCES b,
PRIMARY KEY (a_id, b_id)
);

For performance reasons you might want to
CREATE UNIQUE INDEX idx_atob ON atob (b_id, a_id);

HTH.
Servus
Manfred

Oh sure, that's easy. Make the primary key be a table constraint instead
of just a single column.

CREATE TABLE foo (
a int,
b int,
PRIMARY KEY (a,b)
);

Joshua b. Jore ; http://www.greentechnologist.org

On Thu, 4 Jul 2002, Angela Luyf wrote:

> Hello,
> I have a database model where a many to many relation is used, so i need
> to create a table with two primary key. Don't know how to solve this in
> postgress, can't find it in the tutorials, so can anybody help me with
> this ?
>
> A. Luyf
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>
>

A tabe defined as
CREATE TABLE foobar (
foo int,
bar int,
PRIMARY KEY (foo, bar) );

will allow many elements of type foo to be related to many elements of
type bar and vice versa. Each combiniation of foo and bar will be forced
unique.

On Thu, 4 Jul 2002, Angela Luyf wrote:

> Hello,
> I have a database model where a many to many relation is used, so i need
> to create a table with two primary key. Don't know how to solve this in
> postgress, can't find it in the tutorials, so can anybody help me with
> this ?
>
> A. Luyf

--
David A Dickson
david(dot)dickson(at)mail(dot)mcgill(dot)ca

CREATE TABLE foo (
id1 INTEGER NOT NULL,
id2 INTEGER NOT NULL,
CONSTRAINT foo_pk PRIMARY KEY(id1,id2)
);

HTH

On Thu, 4 Jul 2002, Angela Luyf wrote:

> Hello,
> I have a database model where a many to many relation is used, so i need
> to create a table with two primary key. Don't know how to solve this in
> postgress, can't find it in the tutorials, so can anybody help me with
> this ?
>
> A. Luyf
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

--
Darren Ferguson

Angela Luyf dijo:

> Hello,
> I have a database model where a many to many relation is used, so i need
> to create a table with two primary key. Don't know how to solve this in
> postgress, can't find it in the tutorials, so can anybody help me with
> this ?

CREATE TABLE twos (one INT, two INT, PRIMARY KEY (one, two));

But I don't think that's the solution for many-to-many relations.
Should be more like:

CREATE TABLE first (one SERIAL PRIMARY KEY);
CREATE TABLE second (two SERIAL PRIMARY KEY);

and the table with foreign constraints should be:

CREATE TABLE third (one INT REFERENCES first NOT NULL,
two INT REFERENCES first NOT NULL,
PRIMARY KEY (one, two)
);

BTW, this is in the "table_constraint" part of the grammar.

HTH,

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"The ability to monopolize a planet is insignificant
next to the power of the source"

On Thu, 4 Jul 2002, Angela Luyf wrote:

> Hello,
> I have a database model where a many to many relation is used, so i need
> to create a table with two primary key. Don't know how to solve this in
> postgress, can't find it in the tutorials, so can anybody help me with
> this ?

You can't have multiple primary keys in a table (per SQL spec) but are
you sure that's what you want as opposed to a single key made of two
columns?

On Fri, 5 Jul 2002, Stephan Szabo wrote:

> On Thu, 4 Jul 2002, Angela Luyf wrote:
>
> > I have a database model where a many to many relation is used, so i need
> > to create a table with two primary key.
>
> You can't have multiple primary keys in a table (per SQL spec) but are
> you sure that's what you want as opposed to a single key made of two
> columns?

I certainly read this as "two primary keys" in the sense of "two
candidate keys," and I was quite suprised that everybody else
interpreted this as "one primary key consisting of data from two
columns."

However, I don't see any problem here at all. That's because,
relationally speaking, I am of the opinion that the concept of a PRIMARY
KEY is entirely useless. If you've got two candidate keys on a table,
and unique constraints on both of them, there's nothing at all that
makes one better than the other.

Given that, you don't need to worry about having two primary, one or any
primary keys for a table; just make sure you have appropriate unique and
not null constraints for all of your candidate keys and you're set.

However, if you're using a lot of REFERENCES constraints, you might
declare the most frequent candidate key to be a PRIMARY KEY solely
becuase you then need declare only the table being referenced, not the
columns, in integrity constraints you're using in other tables. In other
words, PRIMARY KEY is a bit of syntatic sugar that can save you a bit of
typing.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

On Mon, 8 Jul 2002, Curt Sampson wrote:

> On Fri, 5 Jul 2002, Stephan Szabo wrote:
>
> > On Thu, 4 Jul 2002, Angela Luyf wrote:
> >
> > > I have a database model where a many to many relation is used, so i need
> > > to create a table with two primary key.
> >
> > You can't have multiple primary keys in a table (per SQL spec) but are
> > you sure that's what you want as opposed to a single key made of two
> > columns?
>
> I certainly read this as "two primary keys" in the sense of "two
> candidate keys," and I was quite suprised that everybody else
> interpreted this as "one primary key consisting of data from two
> columns."
>
> However, I don't see any problem here at all. That's because,
> relationally speaking, I am of the opinion that the concept of a PRIMARY
> KEY is entirely useless. If you've got two candidate keys on a table,
> and unique constraints on both of them, there's nothing at all that
> makes one better than the other.

True, but, trying to use two separate candidate keys on each of the
linking columns alone won't really make a many to many relationship.
I'm assuming that the linkage is what is supposed to be unique here
not the individual parts.