Drop identity property from an existing column

If you are planning to change your db columns from identity property to non-identity property by script these codes will help you.

Disable identity

Disabling the identity can be little bit tricky using sql script. Here is a step by step process on how to achieve this without effecting the records. 

 

Code That Drops the IDENTITY Property from an Existing Column 

		

-If other tables’ foreign keys point to the IDENTITY column, drop them. Here, the script drops the foreign key pointing to Orders2.OrderID from OrderDetails2.

ALTER TABLE OrderDetails2

DROP CONSTRAINT FK_OrderDetails2_Orders2

--If a primary key exists on the IDENTITY column, drop the primary key constraint the way this script drops the primary key from Orders2.

ALTER TABLE Orders2

DROP Constraint PK_Orders2

--Add another column with the same data type as the IDENTITY column to Orders 2 and allow NULLs.

ALTER TABLE Orders2

ADD new_OrderID int NULL

--Update the new column with the values of the IDENTITY column.

UPDATE Orders2

SET new_OrderID = OrderID

--If the new column doesn’t permit NULLs, alter the column to NOT NULL.

ALTER TABLE Orders2

ALTER COLUMN new_OrderID int NOT NULL

--Drop the IDENTITY column.

ALTER TABLE Orders2

DROP COLUMN OrderID

--Rename the new column to the dropped IDENTITY column’s name.

EXEC sp_rename 'Orders2.new_OrderID', 'OrderID', 'COLUMN'

--If a primary key exists on the new column, recreate the key. In this case you recreate the primary key on Orders2.

ALTER TABLE Orders2

ADD CONSTRAINT PK_Orders2 PRIMARY KEY(OrderID)

--On other tables, recreate any foreign keys that originally pointed to the old IDENTITY column and point them to the new column. Here, you recreate the foreign key on OrderDetails2.

ALTER TABLE OrderDetails2 WITH NOCHECK

ADD CONSTRAINT FK_OrderDetails2_Orders2

FOREIGN KEY(OrderID)

REFERENCES Orders2(OrderID)

What script actually does?

First it removes foriegn key and primary key. 

Adds another columns of the same datatype with Allow Null property.

Copy data from the old column to new column.

Alter the new column to NOT NULL.

Drop the Old column. 

Rename the new column to the name of the old column that you just dorpped. 

Recreate primary key and foriegn keys. 

 

 

blog comments powered by Disqus