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]
