Removing IDENTITY from a table having data

Transact-SQL
,
1

What is the best approach to remove IDENTITY from a SQL Server table which already has data in it? Should I drop the table & create it again?

2

hi

hope this link helps :slight_smile:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/adc78696-9b31-4ffd-b881-5a2f87249cfd/how-to-drop-identity-property-of-column?forum=sqlsmoanddmo

3

You can just drop the identity column from the table:

ALTER TABLE dbo.table_name DROP COLUMN id;

1 Like
4

There are a lot of questions around this seemingly simple question...

  1. Is the IDENTITY column the PK of the table?
  2. If the answer to #1 is "No", do you need to keep the numeric values of that column for ANY reason?
  3. If the answer to #1 is "Yes", you need to find all the FK's and other references that point to this column so that you can rebuild all of those after you build a new column, which ultimately should be named the same, which causes other complications that have some pretty easy work arounds.
1 Like
5

Yes, IDENTITY column is the PK of the table for every table in my database.
I don't want PK to be removed, only IDENTITY to be removed.
Since these tables have some existing data in it & some tables have FK with other tables, it is causing some difficulty.
I can remove the IDENTITY in Dev server DB, just by going to design in SSMS & remove IDENTITY.
I want to remove it from Production server DB, through some scripts, which I don't have access to do it through design in SSMS.
I need a help on this part.

6

I am using the following query to remove the IDENTITY in tables where data already exists. This query works when there is no FK reference. When there is a FK reference it does not work. For example, the DeptD is referenced by another table having data, the following query does not work. Please let me know what I can do on this so that it will work for table having FK reference also.

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

USE RegTracInt

CREATE TABLE [dbo].[TMP_DepartmentMaster](
	[DEPTID] [bigint] NOT NULL,
	[DEPTCode] [nvarchar](50) NULL,
	[Name] [nvarchar](125) NULL,
	[Description] [nvarchar](500) NULL,
	[IsActive] [bit] NULL,
	[IsDeleted] [bit] NULL,
	[CreatedDate] [datetime] NULL,
	[CreatedBy] [nvarchar](256) NULL,
	[LastUpdatedDate] [datetime] NULL,
	[LastUpdatedBy] [nvarchar](256) NULL
) 
GO

----------------------------------
IF EXISTS(SELECT * FROM dbo.DepartmentMaster)
    EXEC('INSERT INTO dbo.TMP_DepartmentMaster ([DEPTID]
      ,[DEPTCode]
      ,[Name]
	  ,[Description]
	  ,[IsActive]
	  ,[IsDeleted]
      ,[CreatedDate]
      ,[CreatedBy]
      ,[LastUpdatedDate]
      ,[LastUpdatedBy])
      SELECT [DEPTID]
      ,[DEPTCode]
      ,[Name]
	  ,[Description]
	  ,[IsActive]
	  ,[IsDeleted]
      ,[CreatedDate]
      ,[CreatedBy]
      ,[LastUpdatedDate]
      ,[LastUpdatedBy]
  FROM [dbo].[DepartmentMaster] WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.DepartmentMaster
GO
EXECUTE sp_rename N'dbo.TMP_DepartmentMaster', N'DepartmentMaster', 'OBJECT' 
GO
COMMIT



ALTER TABLE [dbo].[DepartmentMaster] ADD CONSTRAINT [PK_DepartmentMaster] PRIMARY KEY CLUSTERED 
(
	[DEPTID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]




ALTER TABLE [dbo].[DepartmentMaster] ADD  CONSTRAINT [DF_DepartmentMaster_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO
ALTER TABLE [dbo].[DepartmentMaster] ADD  CONSTRAINT [DF_DepartmentMaster_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO
ALTER TABLE [dbo].[DepartmentMaster] ADD  CONSTRAINT [DF_DepartmentMaster_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [dbo].[DepartmentMaster] ADD  CONSTRAINT [DF_DepartmentMaster_LastUpdatedDate]  DEFAULT (getdate()) FOR [LastUpdatedDate]
GO
7

To do this - you need to drop/remove all of the foreign keys on all tables that reference this table, then you can remove the IDENTITY - or rather, you can build a new table without the identity, copy the data - rename the table and rebuild the foreign keys.

Is there a specific reason you need to remove the identity? Has all the code that inserts data into this table been updated to generate the 'identity' value manually?

8

Just to be a bit pedantic, I don't believe you need to actually drop them or remove them (The FK constraints).. They can "just" be disabled and then re-enable when done.

1 Like