[thelist] mysql primary key multiple columns

[rudy limeback]

> (I'm very much not a database design person, but I understand you
> shouldn't make this your PRIMARY key if you might ever need to use
> it as a foreign key in another table. If this could be a possibility,
> maybe make the column combination a unique, non-primary key
> and add an auto-increment column as your primary key.)

hi liz

hey, don't apologize

(being a database design person is a cruel affliction, given only to those
who the creator feels can carry the burden -- no offence to anyone whose
favourite creator i've just dissed)

the reason it's called a primary key is *because* it will be used as a
foreign key in another table

there are no "secondary" keys in the relational model, unless you are
thinking of "candidate" keys, all the possible keys that you might
want to use as the primary key

but your advice is excellent anyway -- autonumbers make fabulous primary
keys

by "non-primary key" i assume you mean index, which is another type of
thing altogether, and has to do with efficiency of operations rather than
ability to declare relational constraints

also, when you said "if you might ever need to use it as a foreign key in
another table" i think you meant "if you want the database to enforce
relational integrity"

if you don't care whether the database enforces relational integrity, you
typically wouldn't bother to declare a primary key, much less all the
foreign keys that relate to it

also, note that you don't have to declare a table's autonumber as the
primary key

you wouldn't bother to declare an autonumber unique, now, would you?

;o)


rudy
r937.com

p.s.  this business with the autonumbers is worth an article some day but i
keep putting it off...  can't get it into my head that people might
actually be interested...