Remove IDENTITY property from a primary key column in SQL Server
When there’s a table with a primary key that is an IDENTITY and you want to remove the IDENTITY property, there’s no simple SQL code to do it. It is not a trivial task. In fact, there’re many steps to be done and it’s better to do all of them inside a single transaction, to avoid to leave the database in a inconsistent way (without FK, or PKs).
Steps
Steps to be done:
- Add a new temporary column.
- Update the new column with the same values.
- Set the new column as NOT NULL.
- Drop Foreign Keys Constraints.
- Drop Primary Key.
- Drop IDENTITY column.
- Rename the new column with the name of the old one.
- Add new Primary Key.
- Create again constraints (without rechecking them).
An example
In AdventureWorks database, there’s a master table called [Person].[AddressType] that has only 6 Rows.
All master tables in my databases cannot have IDENTITYs because the values can be inserted in different order depending on the server. So, we’re going to remove it.
To compare the differences between the original table definition and the result after the process explained, I have copied the tables’ schema, where we can see that two things have changed:
- There’s no IDENTITY.
- The order of the column in the definition has changed.
-- The original table:
CREATE TABLE [Person].[AddressType](
[AddressTypeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL
CONSTRAINT [DF_AddressType_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [DF_AddressType_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED
([AddressTypeID] ASC)) ON [PRIMARY]
-- The result after removing the IDENTITY property:
CREATE TABLE [Person].[AddressType](
[Name] [dbo].[Name] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL
CONSTRAINT [DF_AddressType_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [DF_AddressType_ModifiedDate] DEFAULT (getdate()),
[AddressTypeID] [int] NOT NULL,
CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED
([AddressTypeID] ASC)) ON [PRIMARY]
In our example, it’s important to note that the Person.AdressType has references to two tables:
- [Purchasing].[VendorAddress].[AddressTypeID] named [FK_VendorAddress_AddressType_AddressTypeID]
- [Sales].[CustomerAddress].[AddressTypeID] named [FK_CustomerAddress_AddressType_AddressTypeID]
The script is, as explained before, inside a transactions and the looks like this:
SET NOCOUNT ON;
BEGIN TRANSACTION
BEGIN TRY
-- Adding a new temporary column
ALTER TABLE Person.AddressType
ADD [new_AddressTypeID] INT NULL;
-- Updating the new column with the values sorted as we want
EXEC sp_executesql N'UPDATE Person.AddressType SET [new_AddressTypeID] = AddressTypeID'
-- Setting the new column as NOT NULL
ALTER TABLE Person.AddressType
ALTER COLUMN [new_AddressTypeID] INT NOT NULL;
-- Disable Foreign Keys Constraints
ALTER TABLE [Purchasing].[VendorAddress]
DROP CONSTRAINT [FK_VendorAddress_AddressType_AddressTypeID]
ALTER TABLE [Sales].[CustomerAddress]
DROP CONSTRAINT [FK_CustomerAddress_AddressType_AddressTypeID]
-- Drop Primary Key
ALTER TABLE Person.AddressType
DROP CONSTRAINT [PK_AddressType_AddressTypeID]
-- Drop IDENTITY column
ALTER TABLE Person.AddressType
DROP COLUMN [AddressTypeID]
-- Rename column ID_EXENCION_NEW --> ID_EXENCION
EXEC sp_rename 'Person.AddressType.new_AddressTypeID', 'AddressTypeID', 'COLUMN';
-- Add new Primary Key
ALTER TABLE Person.AddressType
ADD CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED
(
[AddressTypeID] ASC
)
-- Enable constraints (without rechecking them)
ALTER TABLE [Purchasing].[VendorAddress] WITH NOCHECK
ADD CONSTRAINT [FK_VendorAddress_AddressType_AddressTypeID]
FOREIGN KEY([AddressTypeID]) REFERENCES [Person].[AddressType] ([AddressTypeID])
ALTER TABLE [Sales].[CustomerAddress] WITH NOCHECK
ADD CONSTRAINT [FK_CustomerAddress_AddressType_AddressTypeID]
FOREIGN KEY([AddressTypeID]) REFERENCES [Person].[AddressType] ([AddressTypeID])
PRINT 'IDENTITY removed successfully'
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT 'ERROR:' + ERROR_MESSAGE()
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
The UPDATE of the new column must be done with dynamic SQL because we’re adding the column in the very same script and SQL Server tries to validate script before the execution and it doesn’t find the column and gives an error.
