two primairy key in one table ?

On Thu, 04 Jul 2002 15:33:11 +0200, Angela Luyf <a.c.luyf@amc.uva.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

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 ?

I always create a separate primary key field in the
intermediate table and do a unique index on the
other two fields.

p1.p1Id p2.p2Id
\ /
\ /
\ /
\ t3.t3Id /
t3.p1Id
t3.p2Id

besides of the normal primary and foreign keys I create
an unique index:

create unique index on t3 (p1Id, p2Id);

Regards Herbie
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti http://www.thinx.ch
The content management company. Visit http://www.contentx.ch
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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:

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.dickson@mail.mcgill.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@postgresql.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@cynic.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.