primary keys, any use?

nickholas

Allright for some time, while building a website's proven to be quite complex, i've come to the bitter conclusion that mysql doesn't support multiple primary keys. But the thing is, as i kind of tried to move over from this shock, i started to realise that this doesn't change a thing, practically speaking. Coz theorically you can filter everything after the "where" word in a query. But then, WHY is it good at all to mark one field as a primary at all?

cobalt60

I'm going out on a limb here (i.e. not checking my facts before I open my mouth) but I think a primary key should designate a unique value. I can think of a lot of reasons it would be good to have a unique field to select on for edits, deletes, table relationships, etc.

BUT: I've got a primary key in my table that's supposed to be auto_increment, and it's allowing me (actually, doing it for me) to insert zeros. I've got several records with 0 in the auto_increment field which the books tell me is against the rules. So to answer your question: heck, I don't know. Sounds to me like if you can design your stuff without primary keys and it works the way you want it to, never mind the bollocks and go for broke.

And if you have any idea why I could be getting this kind of behavior from my auto_increment column, I'd LOVE to hear it.
🙂

gmiyano

You're right, but if you MUST use multiple primary key in mySQL, I guess you can split the tables.

Also as you know primary key guarantees the uniquness within a table. Like you mentioned you can do this by writing WHERE conditions, but then, you will end up creating unnecessary indexes to speed up those WHERE conditions.

jbloom

MySQL may not allow calling multiple columns "primary key" but it does allow:

1) Having the primary key be based on multiple columns.

2) Creating multiple UNIQUE indexes. If you create a UNIQUE index on a NOT NULL field, it acts precisely like a PRIMARY KEY.

So it's pretty flexible in its ability to enforce uniqueness.

Some applications may distinguish between the primary key and other unique indices of a table, but if your application does not there is little to be gained by calling a field "primary key" rather than just creating a UNIQUE index on that field.

nickholas

CHEERS mates!