As Jeremy says - it depends totally on what you want to do.
If you have tables where there is no logical, unique way to identify that
column (or the only way to do so is via a column you do not want to use for
this purpose), then assigning a separate ID column as a PK makes sense.
E.g: If you have a lookup table 'ItemDescription' which contains a list of
description fields for items, it would make sense to make the table (ItemID,
Description) with ItemID being an autoincrement primary key.
However, in some other cases, a compound key will make more sense - for
instance if you have a 'glue table' such as 'Item_Shop' which lists the
items that are available in each shop: (ItemID, ShopID), then clearly, you
cannot have a PK on either column alone (since there is a many to many
relationship), so a compound PK is the only way to actually put a PK on the
table (and uniquely identify a given row).
One rule of thumb is: If there are two or more columns within a given table
which together are the logical way to identify that row (and the way you
would always join to the table), then use those as a compound key, otherwise
assign a separate autoincrement column as a PK.
Cheers,
Matt
> -Original Message-
> From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
> Sent: 23 April 2004 23:51
> To: Emmett Bishop
> Cc: [EMAIL PROTECTED]
> Subject: Re: Compound Primary Key question
>
> On Fri, Apr 23, 2004 at 03:40:43PM -0700, Emmett Bishop wrote:
> > Quick question. In general, is it better to create
> > compound primary keys or use an auto increment field
> > to uniquely identify each record?
>
> Yes.
>
> It depends on your application and your data.
>
> Jeremy
> --
> Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/
>
> [book] High Performance MySQL -- http://highperformancemysql.com/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
