Cast the first stone he who never made a mistake modeling a database! Every now and then in your career you will be face to face with a problem like this: due to the nature of a table’s data you created a table without a primary key, or using a composed key. So far so good, but due to an upgrade you see the need to have a unique key identifying all the registers in your table, in my case it was due to a AJAX interface.

So what now? You have a table full of data, and of course, as Murphy’s law will tell you, that data cannot be erased. MySQL will prevent you from turning a filled to a primary key if it finds duplicated values in the table. Quite a brain twister, but I did a little research and found a rather simple solution to the matter.

UPDATE: So it actually came to my atention that a query I had already tried does the job in an even simpler form, but my modelling tool executed the commands out of sync and that why i had problems. So this article stays on as a good example of how to use mysql variables.

First up I created an “id” filed in my table to be my future primary key, initially it was configured to be only an INT field with no auto-increment attribute, or primary key qualification. The default value for the field became 0 (zero), and now all I needed to do was populate that field with a sequence from 1 to XX (10.300 in my case). Doing this manually would be rather troublesome so I needed to find an automatic solution.

My first idea was to use sub-queries, where the update for each register would seek the highest value for ID and increment it by one. But soon I found a flaw to my idea, as sub-queries do not accept the update and select targets as the same table. So I went back to my drawing boars to find a new solution, and soon I found a possible solution, MySQL variables.

User defined MySQL variables (MySQL Reference) have been around for quite a while and allow you to transfer values form query to query (of the same connection) with out the use of temporary tables. So I figured out I could use a variable to store the value of the highest ID and keep incrementing it in each update query.

@var_name := value

The recommended syntax shown above uses := to set the value of variables because this syntax can be used inside ou outside of a SET command. So now I needed to define a query to use this feature and complete my count, so I built this query:

SET @maxId= 0;
UPDATE mytable SET id = ( SELECT @maxId := @maxId+1 ) WHERE 1

When the query begins it sets maxId to zero and with every execution it increments that variable coming to a full count at the end of its execution

Now all I have to do is set the auto-increment attribute and define the column as my primary key.

User defined variables have innumerous other possibilities and uses, and should be considered a useful tool for every programmer. So I hope I showed you a bit of their capacity and how they can solve a simple problem we will all face someday.

To add auto-increment primary key values used this code below: (contribution by balluche)

alter table mytable drop column id;
alter table mytable add id INT NOT NULL PRIMARY KEY auto_increment;

This post is also available in: Portuguese (Brazil)