將既有資料表 ID 欄位改為 IDENTITY 自增欄位

問題背景

• 表已建立 PK，但忘記設定 IDENTITY(1,1)
• 數百張表需修改，手工不可行
• 限制： ALTER TABLE ... ALTER COLUMN 不支援加入 IDENTITY

解決策略：Drop Column → Add Identity → Rebuild PK

⚠ 前提假設：

• 單表處理 (全庫擴充請自行包裝游標)
• [ID] 上無其他約束 (除 PK 外)
• PK 名稱固定為 PK_#Table#
• 原 ID 資料可捨棄 (否則需 Move→Rebuild→MoveBack)

預存程式碼

CREATE PROCEDURE [TOOL].[sp_Table_SetIdentity]
    @Table VARCHAR(100),
    @Schema VARCHAR(100)
AS
BEGIN
    DECLARE @vSQL NVARCHAR(MAX);
    
    -- 1. 移除 PK 約束
    SET @vSQL = N'ALTER TABLE #Schema#.#Table# DROP CONSTRAINT PK_#Table#;';
    SET @vSQL = REPLACE(@vSQL, '#Table#', @Table);
    SET @vSQL = REPLACE(@vSQL, '#Schema#', @Schema);
    PRINT @vSQL;
    EXEC (@vSQL);
    
    -- 2. 重建 IDENTITY 欄位 + 重建 PK
    SET @vSQL = N'
        ALTER TABLE #Schema#.#Table# DROP COLUMN [ID];
        ALTER TABLE #Schema#.#Table# ADD [ID] INT IDENTITY(1,1) NOT NULL;
        ALTER TABLE [#Schema#].[#Table#] 
        ADD CONSTRAINT [PK_#Table#] PRIMARY KEY CLUSTERED 
        ( [ID] ASC )
        WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF) 
        ON [PRIMARY];';
    
    SET @vSQL = REPLACE(@vSQL, '#Table#', @Table);
    SET @vSQL = REPLACE(@vSQL, '#Schema#', @Schema);
    PRINT @vSQL;
    EXEC (@vSQL);
END

執行流程圖解

原始表結構          步驟 1: DROP PK        步驟 2: DROP COLUMN ID
[ID] INT PK   ───►   [ID] INT (無約束)   ───►   (無 ID 欄位)
                                                      │
                                                      ▼
                                              步驟 3: ADD ID INT IDENTITY
                                              步驟 4: ADD PK
                                                      │
                                                      ▼
最終表結構: [ID] INT IDENTITY(1,1) PK CLUSTERED

風險與替代方案

保留資料的進階做法

-- 1. 新增暫存身分識別欄
ALTER TABLE dbo.MyTable ADD NewID INT IDENTITY(1,1);

-- 2. 更新外來鍵參考表 (若有)
-- UPDATE ChildTable SET ParentID = p.NewID FROM ParentTable p WHERE ChildTable.ParentID = p.OldID;

-- 3. 移除舊 PK、舊 ID
ALTER TABLE dbo.MyTable DROP CONSTRAINT PK_MyTable;
ALTER TABLE dbo.MyTable DROP COLUMN OldID;

-- 4. 重命名 NewID → ID
EXEC sp_rename 'dbo.MyTable.NewID', 'ID', 'COLUMN';

-- 5. 重建 PK
ALTER TABLE dbo.MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (ID);