Way back in the old days, when I was just starting to puzzle out php and SQL, I created a database that didn't rely on auto increment for the record ID. It hasn't been a problem over the years, but I decided to rewrite some of the equally old modules and was vaguely thinking about making my life easier through the miracle of auto increment.

I could migrate the old records to a new table, that uses auto increment, but I was wondering if there was a way to preserve the old, non auto increment ID numbers so I wouldn't have to hunt down the fall out from an ID change.

This is more a curiosity question than a problem. The old way works (and the new way might mean way to much module browsing to look for unintended consequences.)

robkir

    Assuming the record ID field is an integer, I believe you'd want:

    alter table FOO modify recordID int auto_increment primary key;
alter table FOO auto_increment = (("select MAX(recordID) from FOO")+1);

    Untested. Ymmv.

      I'm interested to know more about why you think AUTO_INCREMENT is some magical solution or alternative. Rather, I'd be curious if you could more explicitly describe the schema before and after you incorporate an AUTO_INCREMENT column.

      For example, let's say we're talking about a membership/registration database. Previously, let's say you used the "username" column as your PRIMARY KEY. After all, no two users should ever have the same username - that doesn't fit with the basic idea behind the membership system. In this case, using an AUTO_INCREMENT column would most likely not make sense. Abstracting a user's data behind some meaningless number doesn't help you, and you'd still want to create a separate UNIQUE constraint on the "username" column. Using AUTO_INCREMENT here added more data and created more work for you... and the only benefit is that you've got this nifty number that doesn't really mean anything to anyone (except the DBMS, of course).

        I'd tend to agree, and would also be curious about the schema. However there's a sense in which I'd prefer a numeric ID for this sort of thing ... a number that's guaranteed to be an integer is often easier to work with than a username with potentially arbitrary characters IMHO...

          This database runs a sports organizations game management system. Each participant has a unique ID number that is referenced for updating, deletion or participation in specific events. The usernames are unique, but the simplicity of referencing an participant by ID seems much easier than by username. I like auto increment because I don't have to open the source table and retrieve the last highest ID number, then add one to it. But, since I have only one table using this methodology, I've decided not to convert it to auto increment.

          I really appreciate the thought that goes into your responses. It helps greatly in my understanding.

          Thanks,
          robkir

            I wanted to wait until we knew a little more about a specific schema as an example, however I'll now say this...

            I do agree that sometimes adding an otherwise-meaningless ID number saves some work for the programmer as well as reduces bandwidth/data requirements (especially when you start getting into cases where the UNIQUE constraint actually spans multiple columns). It's a (usually) minor tradeoff that, in a majority of cases, seems to be simply written off as an acceptable cost of convenience/familiarity.

              Write a Reply...