MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 19 2003 3:46pm
Subject:Re: selecting PRIMARY KEY when there is no unique value
View as plain text  
At 11:15 -0400 6/19/03, Mojtaba Faridzad wrote:
>Hi,
>
>In a docuement such as Invoice Form, we have a header and a couple of
>records for the detail. In header table, Invoice# can be the PRIMARY KEY but
>in detail table, Invoice# is not unique. I think there are two solutions to
>choose a Primary Key (in MyISAM type) :
>
>1) Adding an id field ( auto_increment ) and choose it as PRIMARY KEY
>in this case we have to add another index on Invoice# for making relation
>with the header table
>
>2) There is another field in detail table with "timestamp" type for keeping
>the last change on the record. I want to select ( Invoice# + myTimestamp )
>for PRIMARY KEY. in this case I don't need to add a new fields ( id ) and
>another index ( on Invoice# ) to the table.
>
>which one do you prefer and usually use?

Don't use 2).  There is no guarantee a TIMESTAMP value will be unique
among records with the same invoice number.  That means invoice number +
timestamp wil not form a PRIMARY KEY.

I'd use 1), possibly combining the invoice number with the item id as
a composite key.


-- 
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified?  http://www.mysql.com/certification/

Thread
Odd thingDave Christensen19 Jun
  • Re: Odd thingDon Read19 Jun
RE: Odd thingJay Blanchard19 Jun
RE: Odd thingDave Christensen19 Jun
  • RE: Odd thingMatthew Smith19 Jun
RE: Odd thingJay Blanchard19 Jun
RE: Odd thingDallas Dickey19 Jun
  • selecting PRIMARY KEY when there is no unique valueMojtaba Faridzad19 Jun
    • Re: selecting PRIMARY KEY when there is no unique valueBruce Feist19 Jun
    • Re: selecting PRIMARY KEY when there is no unique valuePaul DuBois19 Jun
    • Re: selecting PRIMARY KEY when there is no unique valueDon Read19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valuePeterWR19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valueMichael Conlen19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valueMojtaba Faridzad19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valueKen Menzel19 Jun
  • how to limit COUNT(*)Mojtaba Faridzad22 Jul
  • Re: how to limit COUNT(*)gerald_clark22 Jul
  • Re: how to limit COUNT(*)Jerry22 Jul
  • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
    • Re: how to limit COUNT(*)Fred van Engen22 Jul
  • Re: how to limit COUNT(*)Viorel Dragomir22 Jul
  • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
  • Re: how to limit COUNT(*)Yves Goergen22 Jul
    • RE: how to limit COUNT(*)Mike Brum22 Jul
      • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
  • Re: how to limit COUNT(*)gerald_clark22 Jul
    • Re: how to limit COUNT(*)Keith C. Ivey22 Jul
  • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
    • Re: how to limit COUNT(*)Fred van Engen22 Jul
RE: Odd thingDallas Dickey19 Jun
RE: Odd thingJay Blanchard19 Jun
RE: Odd thingDave Christensen19 Jun
RE: selecting PRIMARY KEY when there is no unique valueMike Hillyer19 Jun
RE: Odd thingDave Christensen19 Jun
RE: Odd thingDave Christensen19 Jun
RE: Odd thingDave Christensen19 Jun
  • Re: Odd thinggerald_clark19 Jun
RE: Odd thingDave Christensen19 Jun