Remove identity from a table having FK reference.

Question

Remove identity from a table having FK reference.

SSCrazy Eights

Points: 8146

June 23, 2020 at 4:00 am

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If the foreign key constraint is stopping you from dropping the table, you'll need to drop the constraint before you start and re-create it afterwards.

John