Configuring the auto-increment property to a column is a simple SQL command. The complexities come in when trying to deal with either a) tables that have already been created, b) tables that already have data rows in them, and c) when you need to add auto-increment to an existing Primary Key column.
The complexities arise from the fact that adding the auto-increment requires a full table rebuild. If the table has already been created and/or has data rows then adding the auto-increment is more complex.
Adding to a New Table
In this example, we are creating a new table called Users.
In this table, there are four columns. The Id column is the important one. The keyword IDENTITY is what sets the column to auto-increment by one for every new row added to the table. Additionally, this SQL command sets the Id column as the Primary Key. This means that the column must also be a completely unique index on the table.
The IDENTITY constraint accepts two optional values (both defined as 1 in the example above). The first is the seed value which is essentially the starting number. This can be any valid INT to start from if 1 is not the desired starting point. The second value is the amount to increment each new row by. Setting it to 1 means the number will increase numerically as though it were counting. Setting it to 2 would skip a number each time -> 1,3,5,7,9 etc. Both these values default to 1 and so do not have to be included if you do not need to change them.
Adding to an Existing Table
There are two options when adding an auto-increment column to an existing table. The first is to create a new table with the IDENTITY column defined, copy all the data rows from the existing table into it, delete the existing table and rename the new table the same as the original:
The second option is to add the auto-increment column as a new column to the table, copy the column values across, delete the original column and then rename the new column:
One thing to be aware of in this method is that an extra command is required to drop the existing column if it has been defined as the primary key. The name of the PK constraint will depend on what it was called when it was originally created:
The first command will return the name of the constraint which must go in place of 'PK_Users_Id' in the second command above.
If you liked this post, please share it!