MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Ken Menzel Date:June 19 2003 4:24pm
Subject:Re: selecting PRIMARY KEY when there is no unique value
View as plain text  
Hi Paul,   (good to hear from you!)
   We  use (invoice number+date) number as the primary key for the
header and (invoice number+body item number+date) in the body of the
invoice.  We do not allow the same invoice number to be reused on the
same day. However they can eventually rollover (ours is a multi
location systems with a certain sequence reserved for each location).
Body item number a generated sequence.

I agree with paul TIMESTAMP is not a cool thing to use.  Generating
the sequence (there are many methods of doing this, see the "MySQL
Cookbook" by Paul Dubois for some examples including SQL variables or
an autoincrement key, program variables,  etc) gives a natural and
consistant order to the invoice.

I hope this helps,
Ken

> 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/
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

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