Tiffany B. Brown

a mish-mosh of stuff

Question re: MySQL and PRIMARY versus UNIQUE indexes

Any MySQL gurus in the house? I need your help. I’ve got a question: If I have an auto_incrementing id field that is a primary key, and I plan to use that key in a related table, is the primary key enough, should I make it a primary key and a unique key (thereby creating a larger index size and a larger overall DB), or should I just make it a unique key? I’m using InnoDB tables.

What I’ve done in the past is make the id field a PRIMARY key and didn’t bother with a unique key. Now I’m wondering if making the ID a unique key makes more sense because I’ll be using it as a key in another table.

Thoughts? Help?

  • Yak

    Making it a primary key means it is also unique. You can’t have a primary key that is not unique or it will not be a primary key. This is required for any db that conforms to the SQL standard.

  • Yak

    Making it a primary key means it is also unique. You can’t have a primary key that is not unique or it will not be a primary key. This is required for any db that conforms to the SQL standard.

  • http://www.rashidmuhammad.com/ Rashid Z. Muhammad

    Seconded.

    Though many times when you use artificial surrogate keys (e.g. auto-incrementing numbers) you are doing it despite the fact that you have many natural keys in your dataset like, say, an email address in a user table.

    I have found that it can be useful to make these unique in addition to having the primary key. There is a cost of overhead, but it adds a higher degree of sanitization to your database operation. Just be sure to properly handle this in your business logic.

  • http://www.rashidmuhammad.com Rashid Z. Muhammad

    Seconded.

    Though many times when you use artificial surrogate keys (e.g. auto-incrementing numbers) you are doing it despite the fact that you have many natural keys in your dataset like, say, an email address in a user table.

    I have found that it can be useful to make these unique in addition to having the primary key. There is a cost of overhead, but it adds a higher degree of sanitization to your database operation. Just be sure to properly handle this in your business logic.

  • http://www.tiffanybbrown.com/ tiffany

    In related tables where I have a natural unique key, I’m not bothering with another index. I’m just using the primary key in one table as a unique key in another.

    For example, I have the user log-ins in one table with a primary, auto incrementing key (let’s call it uid). Then I have the less-frequently accessed information (user first and last names, e-mail addresses, phone numbers, and departments) in a separate table with the uid as a unique key.

    I just wanted to be clear about what the difference is between a primary and a unique key. Turns out I had it right the first time :-) .

  • http://www.tiffanybbrown.com/ tiffany

    In related tables where I have a natural unique key, I’m not bothering with another index. I’m just using the primary key in one table as a unique key in another.

    For example, I have the user log-ins in one table with a primary, auto incrementing key (let’s call it uid). Then I have the less-frequently accessed information (user first and last names, e-mail addresses, phone numbers, and departments) in a separate table with the uid as a unique key.

    I just wanted to be clear about what the difference is between a primary and a unique key. Turns out I had it right the first time :-) .

  • http://www.rashidmuhammad.com/ Rashid Z. Muhammad

    I’m of the thinking that one should always have surrogate keys despite having natural ones. Mainly because computer systems (RDBMS systems and language interpreters) can work with numbers faster than strings. They also take up less space when you use many-to-many joining tables.

    This obviously comes at the disadvantage of unnaturally binding your data and potentially increasing your recovery overhead by an order of magnitude if you somehow lose a table. You also are forced to perform more joins than you might orninarily need to make.

    But the flip side is that the size of your databases will be smaller and you’re not committed to any business attribute for identification. You also don’t have to worry too much about cascading updates on said data.

    Just my .02.

  • http://www.rashidmuhammad.com Rashid Z. Muhammad

    I’m of the thinking that one should always have surrogate keys despite having natural ones. Mainly because computer systems (RDBMS systems and language interpreters) can work with numbers faster than strings. They also take up less space when you use many-to-many joining tables.

    This obviously comes at the disadvantage of unnaturally binding your data and potentially increasing your recovery overhead by an order of magnitude if you somehow lose a table. You also are forced to perform more joins than you might orninarily need to make.

    But the flip side is that the size of your databases will be smaller and you’re not committed to any business attribute for identification. You also don’t have to worry too much about cascading updates on said data.

    Just my .02.