12 Easy steps to remove identity column within SQL server table.
Hello friends, In previous article we were learn.
How to replace SQL Server multiple spaces into string with single space?
How to Get table(s) - Database size in SQL Server?
Today we are going to explain how to remove identity from a column in a table in SQL Server database, which have important data, without making any change in data.
Sometimes we face a problem with primary key column in database with uncertain sequence generated due to deleting some data from table or SQL server sequence jump when SQL server restarts. To avoid this problem we plans to turn of auto sequence generated by identity column. It seems very easy in case of only one table. what happen if more than one table is present with in database with foreign keys dependency.
Lets take an example first, here we will create three tables tbCountry, tbState and tbCity with foreign keys relation as follows
tbCountry <----------------- tbState
tbState <------------------- tbCity
1). tbCountry have one to many relation with tbState i.e a country may have one or more states.
2). tbState have one to many relation with tbCity i.e a state may have one or more Cities.
Following T-Sql will create a database, a schema and tables with foreign key relations.
CREATE DATABASE SWCLASSDB
Go
Use SWCLASSDB
Go
CREATE SCHEMA SWC
Go
CREATE TABLE SWC.tbCountry
(
CountryId int Constraint PK_CountryId PRIMARY KEY IDENTITY(1,1),
CountryName nvarchar(200) Not Null
)
Go
CREATE TABLE SWC.tbState
(
StateId int Constraint PK_StateId PRIMARY KEY IDENTITY(1,1),
CountryId int Constraint FK_tbState_tbCountry REFERENCES SWC.tbCountry(CountryId),
StateName nvarchar(200) Not Null
)
Go
CREATE TABLE SWC.tbCity
(
CityId int Constraint PK_CityId PRIMARY KEY IDENTITY(1,1),
StateId int Constraint FK_tbCity_tbState REFERENCES SWC.tbState(StateId),
CityName nvarchar(200) Not Null
)
Go
INSERT INTO SWC.tbCountry VALUES ('India'),('Australia'),('England'),('Poland'),('Egypt'),('Japan')
Go
INSERT INTO SWC.tbState VALUES (1,'Haryana'),(1,'Punjab'),(1,'Himachal')
Go
INSERT INTO SWC.tbCity VALUES (1,'Ambala'),(1,'Karnal'),(1,'Sonipat')
Go
INSERT INTO SWC.tbCity VALUES (2,'Amritsar'),(2,'Patiala'),(2,'Jalandher')
Go
INSERT INTO SWC.tbCity VALUES (3,'Hmirpur'),(3,'Mandi'),(3,'Kangra')
Go
DELETE FROM SWC.tbCountry WHERE CountryId>1
Go
INSERT INTO SWC.tbCountry VALUES ('Poland'),('Egypt'),('Japan')
Go
SELECT * FROM SWC.tbCountry
Go
SELECT * FROM SWC.tbState
Go
SELECT * FROM SWC.tbCity
Go
OUTPUT: CountryId CountryName ----------- -------------- 1 India 2 Australia 3 England 7 Poland 8 Egypt 9 Japan (6 row(s) affected) StateId CountryId StateName ----------- ----------- -------------- 1 1 Haryana 2 1 Punjab 3 1 Himachal (3 row(s) affected) CityId StateId CityName ----------- ----------- ------------- 1 1 Ambala 2 1 Karnal 3 1 Sonipat 4 2 Amritsar 5 2 Patiala 6 2 Jalandher 7 3 Hmirpur 8 3 Mandi 9 3 Kangra (9 row(s) affected)
Now We Will Remove identity from primary key column from table tbCountry.
Step 1: Create a duplicate table for backup tbdup_tbCountry.
Step 2: Check data in both tables SWC.tbdup_tbCountry and SWC.tbCountry using count
Step 3: Add a new column for primary key column dup.
Step 4: Copy data from primary key column to new column dup.
Step 5: Match data of both primary key column and dup column.
Step 6: Remove all constraints from table.
Step 7: DELETE primary key column.
Step 8: Add new column with same name as primary key column without any constraints.
Step 9: Copy data from dup column to new created column (on which we will apply constraints).
Step 10: Create all possible constraints with primary key.
Step 11: If every this is good drop dup column.
Step 12: Drop duplicate table.
You can copy and execute following T-Sql to remove identity from column CountryId from table tbCountry.
--Step 1: Create a duplicate table for backup.
SELECT * INTO SWC.tbdup_tbCountry FROM SWC.tbCountry
Go
--Step 2: Check data in both tables SWC.tbdup_tbCountry and SWC.tbCountry using count
SELECT count(1) as T1,(SELECT COUNT(1) FROM SWC.tbdup_tbCountry) AS T2 FROM SWC.tbCountry
Go
--Step 3: Add a new column for primary key column dup.
ALTER TABLE SWC.tbCountry ADD dup int
Go
--Step 4: Copy data from primary key column to new column dup.
UPDATE SWC.tbCountry SET dup = CountryId
Go
--step 5: Match data of both primary key column and dup column.
SELECT * FROM SWC.tbCountry WHERE CountryId<>dup
Go
--Step 6: Remove all foreign key constraints from table.
ALTER TABLE SWC.tbCity DROP CONSTRAINT FK_tbCity_tbState
Go
ALTER TABLE SWC.tbState DROP CONSTRAINT FK_tbState_tbCountry
Go
ALTER TABLE SWC.tbCountry DROP CONSTRAINT PK_CountryId
Go
--Step 7: DELETE primary key column.
ALTER TABLE SWC.tbCountry DROP COLUMN CountryId
Go
--Step 8: Add new column with same name as primary key column without any constraints.
ALTER TABLE SWC.tbCountry ADD CountryId int
Go
--Step 9: Copy data from dup column to new created column (on which we will apply constraints).
UPDATE SWC.tbCountry SET CountryId = dup
Go
--Step 10: Create all possible constraints with primary key.
ALTER TABLE SWC.tbCountry ALTER COLUMN CountryId int NOT NULL
Go
ALTER TABLE SWC.tbCountry ADD CONSTRAINT PK_CountryId PRIMARY KEY(CountryId)
Go
ALTER TABLE SWC.tbState ADD CONSTRAINT FK_tbState_tbCountry FOREIGN KEY(CountryId) REFERENCES SWC.tbCountry(CountryId)
Go
ALTER TABLE SWC.tbCity ADD CONSTRAINT FK_tbCity_tbState FOREIGN KEY(StateId) REFERENCES SWC.tbState(StateId)
Go
--step 11: If every this is good drop dup column.
ALTER TABLE SWC.tbCountry DROP COLUMN dup
Go
--step 12: Drop duplicate table
DROP TABLE SWC.tbdup_tbCountry
Go
-- Drop Dummy Example Tables
DROP TABLE SWC.tbCountry
Go
DROP TABLE SWC.tbState
Go
DROP TABLE SWC.tbCity
Go
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.