Existing schema without auto_increment id's?

[eluser]Unknown[/eluser]
Hi devs,

I'm considering migrating an existing application to CI. I'm wondering if someone can advise me on a concern I have.

Our application schema (mysql) has about 50 tables. Most of the primary keys are _not_ auto_increment. They are 32 character strings. Our existing framework generates an md5 hash when inserting a new record, rather than relying on mysql to generate a new id.

What kind of headaches can I expect when moving my application? Are there any tips? Is there some sort of "get next id" method that I can override in the db class? Or, is there anything elegant I can do in a model? I'd really appreciate any advice.

Fwiw, changing our existing schema from char(32) to int is not an option.

Thanks in advance!
Matt

[eluser]bretticus[/eluser]
As far as I know, using CI Active Record just builds vanilla insert queries. In other words, if you have a table with an auto-incrementing id column and you exclude that column from your query, MySQL automatically generates the next number in sequence. If you specify the value for that id, you run the risk of MySQL complaining about the key already being used. Since your primary keys aren't sequential, just generate a hash based on the current time stamp or something more random. You can just write this code in your CI model method that handles inserts. Doesn't need to be too elegant. I suppose, for convenience, you could extend the Model class with a nice little private method for generating a hash for you.

[eluser]gyo[/eluser]
I understand it may be not an idea to switch from char(32) to int, but that's what I'd do.
Using an auto_increment ID allows you to have a lighter database, and it reduces the number of queries because you don't need to check if that value is already used.

As bretticus said, you don't need to check the next available id: you only have to leave that field empty, mysql will take care of it.

[eluser]NogDog[/eluser]
Something else to consider is that there is a very small but non-zero chance that any two random strings could generate the same hash; which could be another reason for migrating to an auto-increment integer primary key. However that chance is so small that it might be a better allocation of your time to stick with the current scheme and possibly just add a routine to the insert method whereby if a duplicate key error occurs, it regenerates a new hash (maybe based on microtime()?) and tries again.

[eluser]gyo[/eluser]
If you have tons of records in the db, then checking for the hash becomes very intensive, and you dramatically increase the possibility for collisions.