Oracle 遷移至 SQL Server：主鍵約束自動化腳本實作（T-SQL 版）

遷移痛點：DTS/SSIS 僅搬結構與資料，約束遺漏

使用 SQL Server 匯入匯出精靈或 SSIS 遷移 Oracle 時，主鍵、外鍵、唯一約束常因中繼資料不相容而遺失。手工補齊極易出錯，需自動化。

核心思路：查詢 Oracle 系統視圖 → 產生 SQL Server DDL

Oracle 主鍵資訊存於 `USER_CONSTRAINTS`（約束定義）與 `USER_CONS_COLUMNS`（欄位對應）。SQL Server 需對應 `sys.key_constraints` + `sys.index_columns`。

步驟 1：從 Oracle 擷取主鍵定義（透過 Linked Server 或匯出 CSV）

-- Oracle SQL
SELECT 
    uc.TABLE_NAME,
    uc.CONSTRAINT_NAME,
    LISTAGG(ucc.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY ucc.POSITION) AS PK_COLUMNS
FROM USER_CONSTRAINTS uc
JOIN USER_CONS_COLUMNS ucc ON uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME
WHERE uc.CONSTRAINT_TYPE = 'P'
GROUP BY uc.TABLE_NAME, uc.CONSTRAINT_NAME
ORDER BY uc.TABLE_NAME;

將結果匯出為 CSV 或寫入 SQL Server 暫存表 `##OraclePKs`。

步驟 2：在 SQL Server 產生 ALTER TABLE 腳本

-- SQL Server 動態 SQL 產生器
SET NOCOUNT ON;
DECLARE @Sql nvarchar(max) = N'';

SELECT @Sql += N'
IF NOT EXISTS (SELECT 1 FROM sys.key_constraints 
               WHERE parent_object_id = OBJECT_ID(' + QUOTENAME(QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(TABLE_NAME)) + N') 
                 AND type = ''PK'' AND name = ' + QUOTENAME(CONSTRAINT_NAME) + N')
BEGIN
    ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(TABLE_NAME) + N'
    ADD CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME) + N' PRIMARY KEY (' + PK_COLUMNS + N');
END
'
FROM ##OraclePKs; -- 假設已匯入此暫存表

PRINT @Sql; -- 檢查後執行
-- EXEC sp_executesql @Sql;

步驟 3：驗證與補漏

-- 比對雙方主鍵欄位順序與名稱
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    k.name AS PK_Name,
    STUFF((SELECT ',' + c.name 
           FROM sys.index_columns ic
           JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
           WHERE ic.object_id = k.parent_object_id AND ic.index_id = k.unique_index_id
           ORDER BY ic.key_ordinal
           FOR XML PATH('')), 1, 1, '') AS SQLServer_PK_Columns,
    o.PK_COLUMNS AS Oracle_PK_Columns
FROM sys.key_constraints k
JOIN sys.tables t ON k.parent_object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN ##OraclePKs o ON o.TABLE_NAME = t.name AND o.CONSTRAINT_NAME = k.name
WHERE s.name = 'dbo'; -- 依實際 schema 調整

進階：外鍵、唯一、檢查約束同理可循

Oracle `USER_CONSTRAINTS.CONSTRAINT_TYPE`：`P`=PK、`R`=FK、`U`=Unique、`C`=Check。對應產生 `FOREIGN KEY`、`UNIQUE`、`CHECK` DDL。注意 FK 參考表須先存在，須依相依順序執行（拓撲排序）。

工具替代方案

• SQL Server Migration Assistant (SSMA) for Oracle：微軟官方免費工具，可完整轉換 Schema、資料、約束、PL/SQL → T-SQL，支援命令列自動化，強烈建議優先評估。
• 第三方工具：Redgate SQL Compare、Quest Toad、Ispirer MnMTK。

結語

若環境限制無法使用 SSMA，上述「查系統視圖 → 產生 DDL → 動態執行 → 驗證」流程可有效自動化主鍵遷移，原則同樣適用於其他約束類型。關鍵在於中繼資料對齊與執行順序控制。