There's no way to remove an Identity Constraint from a column in a table, except through adding a new column, reassigning the Identity Column data to that new column then recreating the Identity Column (by drop and add) then reassigning back the data to it.
In the following code, I have a PrimaryKey ID that is also set as an Identity Column. What I want to do is to remove the Identity property by creating a new column name 'tempID' then temporarily storing the ID data into it while I recreate ID column so as to remove the Identity property.
ALTER TABLE [TableName]
ADD tempID int NULL
GO
UPDATE [TableName]
SET tempID=[IdentityColumnName]
GO
-- drop all foreign key constraints related to a table
-- see TSQL Script to drop all constraints
GO
ALTER TABLE [TableName]
DROP CONSTRAINT PK_[TableName]
GO
ALTER TABLE [TableName]
DROP COLUMN [IdentityColumnName]
GO
ALTER TABLE [TableName]
ADD [IdentityColumnName] int NULL
GO
UPDATE [TableName]
SET [IdentityColumnName]=tempID
GO
ALTER TABLE [TableName]
ALTER COLUMN [IdentityColumnName] int NOT NULL
GO
ALTER TABLE [TableName]
ADD CONSTRAINT PK_[TableName] PRIMARY KEY CLUSTERED ([IdentityColumnName]) ON [PRIMARY]
--or ADD CONSTRAINT [PrimaryKeyConstraintName]
GO
-- drop all foreign key constraints related to a table
-- see TSQL Script to drop all constraints
GO
ALTER TABLE [TableName]
DROP COLUMN tempID
GO
Posts
4 comments:
If there are foreign keys to the modified column, you need to drop and recreate them too.
Yes you do have to delete the Foreign Key constraints right before dropping the Primary Key constraint.
Then when you re-create your ID column, you have to re-create the Primary Key constraint before applying the previously existing Forein Key constraints.
I would edit the above post to make that clear :)
I wrote a script that would SELECT (recreate and drop) all existing constraints of a given table that would help accomplish the task: TSQL Script to drop all constraints on a table
Nice posted !
Thank you very much !
Thuong mai dien tu
Post a Comment