Interesting thread on MySQL mailing list

I love databases. Database design in particular is something I find immensely interesting. Reading this thread on the mysql general mailing list was (to me) particularly interesting.

I too was taught in the “old school” method of defining primary keys as a unique value from the data itself. Yes I do use auto_increment for some of my tables PK – but I generally try to find something in the data to use as the PK before hand. As the author stated there seems to be a “trend” toward using auto_increments for all PK’s even when a suitable value is already available from the data.

From the responses to the writer’s question there seems to be many valid reasons to use the auto_increment. The response the particularly caught my attention was the “email” response by Rudy Lippan. Unfortuanately I can picture that happening all too often.

Comments (1)

Ben RamseyDecember 22nd, 2005 at 4:12 pm

Yeah, in that example, I would simply set part number as an INDEX to allow faster searching on the part number. The auto-number primary key can be trusted to always be unique; it’s a number that the database creates and, thus, not something prone to human error. While I understand that there’s already data out there that would be suitable as a primary key, I fail to see an argument against using an auto-number primary key. Does it take up much more space in the database? Does it make the database run slower? Probably not enough to really notice, and probably only on extremely large datasets.