List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 22 2005 3:55am
Subject:Re: Reason for Auto-increment primary keys?
View as plain text  
<snipped>
> 
> Shawn, I'm not quite clear what you are saying in your second last 
> paragraph. When you have this situation:
> 
> ID (autogenerated)     PART_NO    PART_DESCRIPTION
> 1                              A01             Widget
> 2                              B03            Grapple Grommet
> 3                              A02            Snow Shovel
> 4                              D11            Whisk
> 5                              C04            Duct Tape
> 
> Do you put the PK on ID alone, PART_NO alone, or the concatentation of 
ID 
> and PART_NO? I _think_ you mean that the PK is on PART_NO alone and that 
ID 
> is simply defined unique so that it can be the target for FKs that refer 
to 
> it but I want to be sure I'm not misreading you....
> 
> Rhino
> 
> 

You figured me out. There would be a UNIQUE on ID and the PK on just 
PART_NO. With InnoDB, the PK is also a clustering index so it has the 
effect of storing data in PART_NO order. Since in a table like this you 
probably search on PART_NO quite frequently that works out just fine as an 
additional optimization.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
Reason for Auto-increment primary keys?Rhino21 Dec
  • Re: Reason for Auto-increment primary keys?Kenneth Wagner21 Dec
    • Re: Reason for Auto-increment primary keys?SGreen21 Dec
      • Re: Reason for Auto-increment primary keys?Rhino21 Dec
        • Re: Reason for Auto-increment primary keys?SGreen22 Dec
      • Re: Reason for Auto-increment primary keys?Kenneth Wagner22 Dec
        • Re: Reason for Auto-increment primary keys?David Griffiths22 Dec
        • Are primary keys essential?James Harvard22 Dec
      • Re: Are primary keys essential?Kenneth Wagner22 Dec
        • Re: Are primary keys essential?James Harvard22 Dec
          • Re: Are primary keys essential?James Harvard22 Dec
      • Re: Are primary keys essential?Kenneth Wagner22 Dec
      • Re: Are primary keys essential?Rhino22 Dec
      • Reporting tools for summary dataC.R.Vegelin22 Dec
        • Re: Reporting tools for summary dataJames Harvard22 Dec
    • Re: Reason for Auto-increment primary keys?Josh Trutwin21 Dec
  • Re: Reason for Auto-increment primary keys?Peter Brawley21 Dec
    • Re: Reason for Auto-increment primary keys?James Harvard22 Dec
  • Re: Reason for Auto-increment primary keys?Rudy Lippan21 Dec
RE: Are primary keys essential?SST - Adelaide)22 Dec