MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Don Read Date:June 19 2003 6:31pm
Subject:Re: selecting PRIMARY KEY when there is no unique value
View as plain text  
On 19-Jun-2003 Mojtaba Faridzad wrote:

Please don't hi-jack threads.

> 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?

Number one (with a composite key).

table invoice_line (
  idinv int unsigned not null,             // invoice number
  line tinyint unsigned auto_increment,    // line number
  idprod, qty, uom, price, cogs,           // product shipped
  ...
  primary key(idinv, line)
)

Regards,
-- 
Don Read                                       dread@stripped
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.
                            (53kr33t w0rdz: sql table query)

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