MS SQL Server - How to change an existing column to auto-increment column
浏览数：14 /
时间：2015年06月12日
/* BY Dylan SUN*/
In SQL server when you want to make an existing column to be auto-incremented.
The following code is not working:
ALTER TABLE [dwh].[ExchangeRate]
ALTER COLUMN [ExchangeRateId] Int Identity(1, 1)What you could do is :
- Create a new column with auto-increment
- Delete existing column constraint
- Delete the existing column
- Rename the new column back
- Add deleted constraint
Step 1: Create a new column
ALTER TABLE [dwh].[ExchangeRate]
ADD [ExchangeRateId2] Int Identity(1, 1)Step 2: Delete existing constraint
ALTER TABLE [dwh].[ExchangeRate]
DROP CONSTRAINT [IxExchangeRate_ExchangeRateId_U_NC_]Step 3: Delete existing column
ALTER TABLE [dwh].[ExchangeRate]
DROP COLUMN [ExchangeRateId]Step 4: Rename new column
Exec sp_rename ‘dwh.ExchangeRate.ExchangeRateId2‘, ‘ExchangeRateId‘,‘COLUMN‘Step 5: Add deleted contraint
ALTER TABLE [dwh].[ExchangeRate] ADD CONSTRAINT [IxExchangeRate_ExchangeRateId_U_NC_] PRIMARY KEY CLUSTERED
(
[ExchangeRateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
