Really neat use for multiple field keys in MySQL
I’ve been working on a project over the past few days and I came in to a situation where a table I had designed was purposefully created to NOT rely on auto_increment/numerical IDs so that I wouldn’t run in to any eventual limitations on the number of rows I could have (before the auto_increment field reached it’s max value and could no longer allow creation of new records). The table in question was made to use VARCHARs instead as my indexes so that I could link to other tables with those instead.
Then I noticed a slight issue when it came time to updating records in this table – if I was going to update a row, I had to compare almost all the fields in my table since I had no unique/primary keys defined (just 2 indexes on VARCHARs). I thought about how much simpler it would be if I had a unique ID field that would allow me to easily refer to a specific row of data – but at the same time I didn’t want to walk in to any data storage limits (though I know, over 4 billion or whatever the max INT is won’t likely ever be reached, it’s still a limitation I’d like to avoid by design).
I thought about it for a while and figured that it would be really nice if I could add an auto_increment field – but tie it in with another non-unique field (think of groups, and then in that group I count the records independently of other groups). It was an amazing idea but I had no clue how to make it work. All I knew is that I could define a UNIQUE KEY and constrain it to multiple fields like so “ALTER TABLE example ADD UNIQUE KEY id (field1, field2);”. The problem I foresaw was that if I made one of those fields auto_increment, MySQL would automatically create an INDEX for that same field to which it would store the increment count meaning that my auto_increment field would pertain to the entire table (meaning that the auto_increment field would increment irregardless of the other field specified in the UNIQUE KEY).
Read more…