Basic Logics · Real Scenario Logic's · Uncategorized

To Remove IDENTITY property from a column in a table by handling Foreign key relationships in both AZURE and On-premises

If we want to remove the IDENTITY property from a column in a Table, but at the same time if we want to keep the table data as it is. Please follow the below steps with examples(images also attached for ref).

Sample Table Creation and Data Population Script

--PARENT TABLE
CREATE TABLE dbo.[TestEmp] 
(
	TestEmp_Key INT IDENTITY(1,1) NOT NULL,
	EmpName VARCHAR(100) NOT NULL,
 CONSTRAINT [PK_TestEmp] PRIMARY KEY CLUSTERED 
 (
	TestEmp_Key ASC
 )
);
GO

INSERT INTO dbo.[TestEmp]
(EmpName)
VALUES
('Arul'),('Kiran'),('Karan'),
('Abhi'),('Mano'),('Kaushik'),
('Rahmoun'),('Subh'),('Sachin');
GO

SELECT * FROM dbo.[TestEmp];   
GO
--CHILD TABLE
CREATE TABLE dbo.[TestEmpDetails]  -- ONE TO MANY RELATIONSHIP
(
	TestEmpDetails_Key INT IDENTITY(1,1) NOT NULL, --PK
	TestEmp_Key INT NOT NULL, --Foreign key 
	PhoneNo VARCHAR(30) NOT NULL,
 CONSTRAINT [PK_TestEmpDetails] PRIMARY KEY CLUSTERED 
 (
	TestEmpDetails_Key ASC
 )
);
GO

INSERT INTO dbo.[TestEmpDetails]
(TestEmp_Key,PhoneNo)
VALUES
(1,'1234567890'),(1,'1231231235'),(2,'1234567891'),
(3,'1234567892'),(3,'1234567877'),(3,'1234567866'),
(4,'1234567893'),(5,'1234567894'),(6,'1234567895'),
(7,'1234567896'),(8,'1234567897'),(9,'1234567898');
GO

SELECT * FROM dbo.[TestEmpDetails];   
GO

--ADDING FOREIGN KEY WITH CHECK CONSTRAINT IN CHILD TABLE
ALTER TABLE [dbo].[TestEmpDetails] WITH CHECK ADD CONSTRAINT [FK_TestEmpDetails_TestEmp_Key] FOREIGN KEY(TestEmp_Key)
REFERENCES [dbo].[TestEmp] (TestEmp_Key)
GO

ALTER TABLE [dbo].[TestEmpDetails] CHECK CONSTRAINT [FK_TestEmpDetails_TestEmp_Key]
GO

Table Structure for checking IDENTITY property

SQL Query Syntax for Checking Table Structure of a table-

exec sp_help table_name;

SQL Query for Checking Table Structure of a ‘TestEmp’ and ‘TestEmpDetails’ tables-

exec sp_help TestEmp;
exec sp_help TestEmpDetails;

Table Structure of TestEmp and TestEmpDetails tables-

Table Structure – ‘TestEmp’ (keyboard shortcut – ‘ALT+F1’ )
Table Structure – ‘TestEmpDetails’ (keyboard shortcut – ‘ALT+F1’ )

How to Remove IDENTITY property?

STEP 1 – Create a New Column in the same table

ALTER TABLE dbo.[TestEmp]
ADD TestEmp_Key_demo INT;

STEP 2 – Transfer the column data from existing IDENTITY column to new column that we created in step 1

UPDATE dbo.[TestEmp] SET TestEmp_Key_demo=TestEmp_Key;

STEP 3 – Check whether the table already having any primary and foreign key constraints and remove the Foreign Key Relationship with the tables first and then primary key constraint.

SQL Query for Checking Foreign Key relationships of a table-

exec sp_fkeys TestEmp;
fkeys relationship lists

Remove Foreign key relationship for the child tables-

Syntax-

ALTER TABLE dbo.[FKTable_Name]
DROP CONSTRAINT FK_Name;

SQL Query-

ALTER TABLE dbo.[TestEmpDetails]
DROP CONSTRAINT FK_TestEmpDetails_TestEmp_Key;

SQL Query for Checking Primary Key Constraint of a table-

exec sp_pkeys TestEmp;
pkeys of a table

Remove Primary key constraint of the parent table-

Syntax-

ALTER TABLE dbo.[Table_Name]
DROP CONSTRAINT PK_Name;

SQL Query-

ALTER TABLE dbo.[TestEmp]
DROP CONSTRAINT PK_TestEmp;

STEP 4 – Drop the Existing IDENTITY column

Syntax-

ALTER TABLE dbo.[Table_name]
DROP COLUMN [Column_name];

SQL Query-

ALTER TABLE dbo.[TestEmp]
DROP COLUMN TestEmp_Key;

STEP 5 – Add the new column with the same column name with same datatype and transfer the column data from Staging column to main column that we created in step 5. Drop the Staging Column. Alter the main column for making it as NOT NULL. Then Recreate those PK and FK again.

SQL Query-

--Adding Column
ALTER TABLE dbo.[TestEmp]
ADD TestEmp_Key INT;
GO

--Tranfer data
UPDATE dbo.[TestEmp] SET TestEmp_Key=TestEmp_Key_demo;
GO

--Drop Staging Column
ALTER TABLE dbo.[TestEmp]
DROP COLUMN TestEmp_Key_demo;
GO

--Making the main column to NOT NULL again
ALTER TABLE dbo.[TestEmp]
ALTER COLUMN TestEmp_Key INT NOT NULL;
GO

--Re-create PK again
ALTER TABLE dbo.[TestEmp]
ADD CONSTRAINT PK_TestEmp PRIMARY KEY NONCLUSTERED (TestEmp_Key);
GO

--Re-create FK again
ALTER TABLE [dbo].[TestEmpDetails] WITH CHECK ADD CONSTRAINT [FK_TestEmpDetails_TestEmp_Key] FOREIGN KEY(TestEmp_Key)
REFERENCES [dbo].[TestEmp] (TestEmp_Key)
GO
--Check Constraint for supporting FK
ALTER TABLE [dbo].[TestEmpDetails] CHECK CONSTRAINT [FK_TestEmpDetails_TestEmp_Key]
GO

Table Structure for TestEmp and TestEmpDetails tables-

SQL Query-

exec sp_help TestEmp;
GO
exec sp_help TestEmpDetails;
GO
Table Structure of TestEmp table without IDENTITY
Table Structure of TestEmpDetails table

CONCLUSION

There is no direct query for removing IDENTITY yet. So we have to follow the steps like above to remove the IDENTITY property from the column in a table. We can also use Rename column name to the original column name method in between to avoid some minimum steps. Unit tested the above scripts in both Azure and on-premises.

Thanks for reading!

Advertisement

2 thoughts on “To Remove IDENTITY property from a column in a table by handling Foreign key relationships in both AZURE and On-premises

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s