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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
