Re: Reason for Auto-increment primary keys?

[SGreen]

<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