List:General Discussion« Previous MessageNext Message »
From:Josh Trutwin Date:December 21 2005 10:19pm
Subject:Re: Reason for Auto-increment primary keys?
View as plain text  
Kenneth Wagner wrote:

> Speed. Especially where related files are concerned. Foreign keys. Links 
> on integer
> fields are faster, smaller and more efficient. Keys remain smaller and 
> faster.

This in my mind is one of the biggest reasons to use an AUTO_INCREMENT 
column as a primary key when other columns would work.  If you have a 
table that will act as a parent in a parent/child relationship and 
you've identified a composite (more than one column) PK as:

col1 VARCHAR(25)
col2 VARCHAR(30)

Then the child table would need to have a copy of both columns posted 
to setup a composite foriegn key:

CREATE TABLE child (
    child_id INT AUTO_INCREMENT,
    col1 VARCHAR(25) NOT NULL,
    col2 VARCHAR(30) NOT NULL,
    INDEX fk_ind (col1, col2),
    FOREIGN KEY (col1, col2) REFERENCES parent(col1,col2) ON DELETE...
    PRIMARY KEY (child_id)
)

So not only are you making a more complex index on the parent table by 
using two character columns you are also posting two columns into the 
child table(s) whenever you want to use this as a parent table.  And 
with MySQL you generally have to make another INDEX on the FK columns 
as well as shown above.  (I've never understood why this isn't automatic)

In this case you have to decide whether or not it's good to maintain 
the uniqueness constraint on the parent table columns if you add an 
AUTO_INCREMENT column by doing something like:

CREATE TABLE parent (
    parent_id INT AUTO_INCREMENT,
    col1 VARCHAR(25) NOT NULL,
    col2 VARCHAR(30) NOT NULL,
    some_other_col VARCHAR(200) NULL,
    UNIQUE (col1, col2),
    PRIMARY KEY (parent_id)
);

The UNIQUE constraint will still create an index on the text columns 
so you will still need to consider space/performance issues but at 
least your child tables only need to post a copy of the INT column 
"parent_id".

In my mind it's always good to use UNIQUE in these cases so your real 
primary keys are in your table structure to prevent getting bad data.

My $0.02

Josh
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