Originally Posted by Old Pedant
Only minor change I would make: Leave the "slug" field as the PK and instead make the UNIQUE INDEX on the auto_increment field.
Yes, that's legal:
create table asdf (
id int auto_increment,
zam varchar(100) primary key,
unique key (id)
) engine innodb;
You're my hero!!!
How come when I tried that 6 months ago, it didn't work in phpMyAdmin?!
Is it maybe because I didn't put a "Unique Index" on the AutoIncrement field??
1.) Does this new approach meet your approval?
2.) If - for whatever reason - I wanted/needed to do a join between tables using a MySQL "Unique Index" field, is that acceptable? (Obviously it is better if it is a true "PK", but if you had to...)
If using the approach above doesn't violate any "Rules of Good DB Design", then I think it makes me more inclined to use "Natural Keys".
Because after some thought, I feel that "Every record in a given table should have a Numeric ID which can easily identify it."
And another benefit of what you showed me above is that if I ever wanted/needed to switch the PK from a "Natural Key" to a "Derived Key", then I have *both* right there!!
What do you think about all of that?!