Adding the IDENTITY Property to an Existing Column

I can't find the exact syntax for adding the IDENTITY property to an existing column. I checked the ALTER TABLE syntax, but it describes only adding a new column that includes the IDENTITY property. How can I add this property to an existing column?

Neither SQL Server 2000 nor SQL Server 7.0 supports adding the IDENTITY property to an existing column by using a single T-SQL command such as ALTER TABLE ALTER COLUMN . However, you can add the IDENTITY property to an existing column by using Enterprise Manager. You might not be aware of all the T-SQL activity that this action generates behind the scenes, especially if relationships exist between the table you want to modify and other tables.

To demonstrate this T-SQL activity, run the code that Listing 1 shows to create and populate the Orders2 and OrderDetails2 tables in the Northwind database. Notice that the Orders2 table's foreign keys point to Customers and Products, and the OrderDetails2 table's foreign keys point to Orders2 and Products. Suppose you want to add the IDENTITY property to the OrderID column in the Orders2 table so that SQL Server generates future order IDs automatically. However, you want to keep the current order IDs unmodified. In Enterprise Manager, you can right-click the Orders2 table, select Design Table, and add the IDENTITY property to the Orders2 table. Then, to see the T-SQL code that Enterprise Manager generates, click the Save Change Script icon in the Design Table window.

Listing 2 shows the T-SQL code that Enterprise Manager 2000 produces. I've divided the script into sections and added explanations. At callout A in Listing 2, the code drops all foreign keys from the Orders2 table. Then, at callout B, the script creates a table called Tmp_Orders2, which has the same structure as the Orders2 table. Notice that the OrderID column carries the IDENTITY property. At callout C, the code turns on IDENTITY_INSERT for the Tmp_Orders2 table before copying data from the Orders2 table so that it copies the original order IDs instead of generating the new order IDs. The code copies the orders from the Orders2 table into the Tmp_Orders2 table at callout D, then turns off IDENTITY_INSERT for the Tmp_Orders2 table at callout E.

At callout F, the script drops from the OrderDetails2 table the foreign key that points to the Orders2 table, then drops the Orders2 table and renames the Tmp_Orders2 table to Orders2. At callout G, the code adds all primary and foreign key constraints to the Orders2 table. Notice that you add the foreign-key constraints WITH NOCHECK, meaning that SQL Server doesn't check existing data when you add the constraint. This stricture is perfectly acceptable because active foreign keys on the original Orders2 table enforce referential integrity. Last, the code adds to the OrderDetails2 table a foreign key that points to the OrderID column in the Orders2 table, as callout H shows.

As you can see, when you add the IDENTITY property to an existing column through Enterprise Manager, you generate a lot of T-SQL activity behind the scenes—even though the change seems simple. If you prefer to write your own T-SQL script to add this property, you need to follow a similar coding path. That is, you need to create another table, copy the data from the original table to the new table, drop the original table, and rename the new table with the original table's name. And as you take these steps, don't forget the foreign key relationships that the original table had with other tables.