Thread: 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
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@postgresql.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.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.
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~