Re: two primairy key in one table ?

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