How to Remove IDENTITY property from a primary key column in SQL Server
When there’s a table with a primary key that is an IDENTITY and you want to remove the IDENTITY property, there’s no simple SQL code to do it. It is not a trivial task. In fact, there’re many steps to be done and it’s better to do all of them inside a single transaction, to avoid to leave the database in a *inconsistent way *(without FK, or PKs).
Follow these steps
- Add a new temporary column.
- Update the new column with the same values.
- Set the new column as NOT NULL.
- Drop Foreign Keys Constraints.
- Drop Primary Key.
- Drop IDENTITY column.
- Rename the new column with the name of the old one.
- Add new Primary Key.
- Create again constraints (without rechecking them).
An example
In AdventureWorks database, there’s a master table called [Person].[AddressType] that has only 6 Rows.
All master tables in my databases cannot have IDENTITYs because the values can be inserted in different order depending on the server. So, we’re going to remove it.
To compare the differences between the original table definition and the result after the process explained, I have copied the tables’ schema, where we can see that two things have changed:
- There’s no IDENTITY.
- The order of the column in the definition has changed.
001.The original table:
002.
003. CREATE
004.
005. TABLE
006. [Person].[AddressType](
007.
008. [AddressTypeID]
009. [int]
010. IDENTITY(1,1) NOT
011.
012. NULL,
013.
014. [Name]
015. [dbo].[Name]
016. NOT
017.
018. NULL,
019.
020. [rowguid]
021. [uniqueidentifier] ROWGUIDCOL NOT
022.
023. NULL
024. CONSTRAINT
025. [DF_AddressType_rowguid] DEFAULT
026.
027. (newid()),
028.
029. [ModifiedDate]
030. [datetime] NOT
031.
032. NULL
033. CONSTRAINT
034. [DF_AddressType_ModifiedDate] DEFAULT
035.
036. (getdate()),
037.
038. CONSTRAINT
039.
040. [PK_AddressType_AddressTypeID] PRIMARY
041.
042. KEY
043. CLUSTERED
044.
045. (
046.
047. [AddressTypeID]
048. ASC
049.
050. ))
051. ON
052.
053. [PRIMARY]
054.
055.
056.
057. --
058. The result after removing the IDENTITY property:
059.
060. CREATE
061.
062. TABLE
063. [Person].[AddressType](
064.
065. [Name]
066. [dbo].[Name]
067. NOT
068.
069. NULL,
070.
071. [rowguid]
072. [uniqueidentifier] ROWGUIDCOL NOT
073.
074. NULL
075. CONSTRAINT
076. [DF_AddressType_rowguid] DEFAULT
077.
078. (newid()),
079.
080. [ModifiedDate]
081. [datetime] NOT
082.
083. NULL
084. CONSTRAINT
085. [DF_AddressType_ModifiedDate] DEFAULT
086.
087. (getdate()),
088.
089. [AddressTypeID]
090. [int]
091. NOT
092.
093. NULL,
094.
095. CONSTRAINT
096.
097. [PK_AddressType_AddressTypeID] PRIMARY
098.
099. KEY
100. CLUSTERED
101.
102. (
103.
104. [AddressTypeID]
105. ASC
106.
107. ))
108. ON
109.
110. [PRIMARY]
In our example, it’s important to note that the Person.AdressType has references to two tables:
- [Purchasing].[VendorAddress].[AddressTypeID] named [FK_VendorAddress_AddressType_AddressTypeID]
- [Sales].[CustomerAddress].[AddressTypeID] named [FK_CustomerAddress_AddressType_AddressTypeID]
The script is, as explained before, inside a transactions and the looks like this:
001.SET
002.
003. NOCOUNT ON;
004.
005.
006.
007. BEGIN
008.
009. TRANSACTION
010.
011. BEGIN
012.
013. TRY
014.
015. --
016. Adding a new temporary column
017.
018. ALTER
019.
020. TABLE
021. Person.AddressType
022.
023. ADD
024.
025. [new_AddressTypeID] INT
026.
027. NULL;
028.
029.
030.
031. --
032. Updating the new column with the values sorted as we want
033.
034. EXEC
035.
036. sp_executesql N'UPDATE
037. Person.AddressType SET [new_AddressTypeID] = AddressTypeID'
038.
039.
040.
041. --
042. Setting the new column as NOT NULL
043.
044. ALTER
045.
046. TABLE
047. Person.AddressType
048.
049. ALTER
050.
051. COLUMN
052. [new_AddressTypeID] INT
053.
054. NOT
055. NULL;
056.
057.
058.
059. --
060. Disable Foreign Keys Constraints
061.
062. ALTER
063.
064. TABLE
065. [Purchasing].[VendorAddress]
066.
067. DROP
068.
069. CONSTRAINT
070. [FK_VendorAddress_AddressType_AddressTypeID]
071.
072. ALTER
073.
074. TABLE
075. [Sales].[CustomerAddress]
076.
077. DROP
078.
079. CONSTRAINT
080. [FK_CustomerAddress_AddressType_AddressTypeID]
081.
082.
083.
084. --
085. Drop Primary Key
086.
087. ALTER
088.
089. TABLE
090. Person.AddressType
091.
092. DROP
093.
094. CONSTRAINT
095. [PK_AddressType_AddressTypeID]
096.
097.
098.
099. --
100. Drop IDENTITY column
101.
102. ALTER
103.
104. TABLE
105. Person.AddressType
106.
107. DROP
108.
109. COLUMN
110. [AddressTypeID]
111.
112.
113.
114. --
115. Rename column ID_EXENCION_NEW --> ID_EXENCION
116.
117. EXEC
118.
119. sp_rename 'Person.AddressType.new_AddressTypeID',
120. 'AddressTypeID',
121. 'COLUMN';
122.
123.
124.
125. --
126. Add new Primary Key
127.
128. ALTER
129.
130. TABLE
131. Person.AddressType
132.
133. ADD
134.
135. CONSTRAINT
136. [PK_AddressType_AddressTypeID] PRIMARY
137.
138. KEY
139. CLUSTERED
140.
141. (
142.
143. [AddressTypeID]
144. ASC
145.
146. )
147.
148.
149.
150. --
151. Enable constraints (without rechecking them)
152.
153. ALTER
154.
155. TABLE
156. [Purchasing].[VendorAddress] WITH
157.
158. NOCHECK
159.
160. ADD
161.
162. CONSTRAINT
163. [FK_VendorAddress_AddressType_AddressTypeID]
164.
165. FOREIGN
166.
167. KEY([AddressTypeID])
168. REFERENCES
169.
170. [Person].[AddressType] ([AddressTypeID])
171.
172. ALTER
173.
174. TABLE
175. [Sales].[CustomerAddress] WITH
176.
177. NOCHECK
178.
179. ADD
180.
181. CONSTRAINT
182. [FK_CustomerAddress_AddressType_AddressTypeID]
183.
184. FOREIGN
185.
186. KEY([AddressTypeID])
187. REFERENCES
188.
189. [Person].[AddressType] ([AddressTypeID])
190.
191.
192.
193. PRINT
194. 'IDENTITY
195. removed successfully'
196.
197. END
198.
199. TRY
200.
201.
202.
203. BEGIN
204.
205. CATCH
206.
207. IF
208. @@TRANCOUNT > 0
209.
210. ROLLBACK
211.
212. TRANSACTION;
213.
214. PRINT
215. 'ERROR:'
216.
217. + ERROR_MESSAGE()
218.
219. END
220.
221. CATCH;
222.
223. IF
224. @@TRANCOUNT > 0
225.
226. COMMIT
227.
228. TRANSACTION;
The UPDATE of the new column must be done with dynamic SQL because we’re adding the column in the very same script and SQL Server r tries to validate script before the execution and it doesn’t find the column and gives an error.
Credits: dbastuff