This project is read-only.

Dropping Autoincrement from an Identity Field

Mar 10, 2011 at 6:27 AM

I have a need to drop the autoincrement "attribute" of a couple of primary key identity fields. From googling I've learned that to do this I need to:

1) Add a new, nullable column ("NewCol")
2) Copy the data from the current identity column to NewCol
3) Drop the current identity column
4) Rename NewCol to the name of the old identity column

Hmm, now that I think about it, I suspect I also need to drop the primary index and then recreate it at the end.

In addition to correcting whatever mistakes exist in this outline, I'm a bit confused as to where to insert the SQL statements to accomplish them. Can you advise me?

- Mark

Mar 10, 2011 at 1:09 PM

Each time your generate, the database tracking project creates another version SQL file. Please add your custom SQL code to one of these files. Each time you upgrade all scripts from the last upgrade to the newest version are run and your database it is versioned. The next time you upgrade this is the starting point for the next upgrade. So look at your database tracking project under "Upgrade SCripts/Generated". You will see files with version numbers. Just add your custom script to the latest version file.

Mar 10, 2011 at 5:47 PM

Thanks. I realized after I posted my question that I could study the code in UpgradeInstaller to see how it works. It's a good thing I did, too, because it turns out I'd neglected to check versions when running the upgrade scripts in the revamped installer framework that I built :).

For those who may be interested, dropping the autoincrement attribute of a column is highly non-trivial, at least in SqlServer. There's no simple ALTER TABLE x DROP IDENTITY command. Instead, recommended practice is to create a new column, copy the existing identity values over, delete any constraints related to the old identity column, delete the original identity column, rename the new column to the old name, and regenerate all the constraints. Finding all the constraints is tedious. 

A tip: recreating the constraints and primary key index correctly is facilitated by doing a Script Table As -> Create To-> New Editor Window command in SqlServer Management Studio. You can then copy and paste the code to create the constraints and primary key index over to your upgrade script. Generating a create script is also useful to ensure you get the correct names of the various constraints and indices.

As an example, here's what I ended up having to do for a single table in my database (there were two others to process as well!):

--Upgrade For Version 0.0.0.5

-- remove autoincrement from county table primary keyALTER TABLE county ADD col_temp_id int nullGO
UPDATE county SET col_temp_id = county_id
GO

ALTER TABLE county ALTER COLUMN col_temp_id int not null
GO

ALTER TABLE district DROP CONSTRAINT [FK__DISTRICT_COUNTY]
GO

ALTER TABLE district DROP CONSTRAINT [FK_COUNTYLOOKUP_DISTRICT_COUNTY]
GO

ALTER TABLE county DROP CONSTRAINT PK_county
GO

ALTER TABLE county DROP COLUMN county_id
GO

EXEC sp_rename 'county.col_temp_id', 'county_id', 'COLUMN'

ALTER TABLE county WITH NOCHECK ADD CONSTRAINT [PK_county] PRIMARY KEY CLUSTERED ([county_id] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[district]  WITH NOCHECK ADD  CONSTRAINT [FK__DISTRICT_COUNTY] FOREIGN KEY([county_id])REFERENCES [dbo].[county] ([county_id])
GO

ALTER TABLE [dbo].[district] CHECK CONSTRAINT [FK__DISTRICT_COUNTY]
GO

ALTER TABLE [dbo].[district]  WITH NOCHECK ADD  CONSTRAINT [FK_COUNTYLOOKUP_DISTRICT_COUNTY] FOREIGN KEY([county_id])REFERENCES [dbo].[county] ([county_id])
GO

ALTER TABLE [dbo].[district] CHECK CONSTRAINT [FK_COUNTYLOOKUP_DISTRICT_COUNTY]
GO