SQL 語法全面進階 (六)：資料庫物件建立與維護 — CREATE TABLE、INDEX、CONSTRAINT

從查詢走向建模：SQL DDL 核心指令實戰指南

前五篇聚焦查詢 (DML)，本文轉向資料定義語言 (DDL)，系統講解 CREATE TABLE、CREATE INDEX、CONSTRAINT 完整語法、選項與命名慣例，奠定資料庫結構設計基礎。

一、CREATE TABLE — 建立資料表

標準語法架構

CREATE TABLE table_name (
    column_name data_type [ (precision [, scale]) ] [ NULL | NOT NULL ]
    [ DEFAULT default_expr ]
    [ IDENTITY (seed, increment) ]
    [ CONSTRAINT constraint_name constraint_type ... ]
    [ , ...n ]
    [ , table_constraint ] [ , ...n ]
);

核心選項詳解

表層級約束 (Table Constraints)

-- 複合主鍵
CONSTRAINT PK_OrderDetail PRIMARY KEY (OrderID, LineNo)

-- 複合唯一
CONSTRAINT UQ_User_Email UNIQUE (Email)

-- 複合外鍵
CONSTRAINT FK_Order_Customer FOREIGN KEY (CustID) REFERENCES Customer(CustID)
    ON DELETE NO ACTION ON UPDATE CASCADE

-- 條件約束
CONSTRAINT CK_Order_Total CHECK (TotalAmount >= 0)

實戰範例：完整訂單表定義

CREATE TABLE Sales.OrderHeader (
    OrderID        INT             IDENTITY(1,1) NOT NULL,
    OrderNumber    NVARCHAR(20)    NOT NULL,
    CustomerID     INT             NOT NULL,
    OrderDate      DATETIME2(3)    NOT NULL DEFAULT SYSUTCDATETIME(),
    Status         TINYINT         NOT NULL DEFAULT 1,
    TotalAmount    DECIMAL(12,2)   NOT NULL DEFAULT 0 CHECK (TotalAmount >= 0),
    Remark         NVARCHAR(500)   NULL,
    RowVer         ROWVERSION      NOT NULL,
    CONSTRAINT PK_OrderHeader PRIMARY KEY CLUSTERED (OrderID),
    CONSTRAINT UQ_OrderNumber UNIQUE NONCLUSTERED (OrderNumber),
    CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerID) 
        REFERENCES Customer(CustomerID) ON UPDATE CASCADE,
    CONSTRAINT CK_Status CHECK (Status IN (1,2,3,4,9))
);
CREATE INDEX IX_OrderHeader_CustomerDate ON Sales.OrderHeader (CustomerID, OrderDate DESC);

二、CREATE INDEX — 建立索引

語法與關鍵選項

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name (column [ASC | DESC] [ ,...n ] )
[ INCLUDE (column [ ,...n ]) ]          -- 覆蓋索引關鍵
[ WHERE filter_predicate ]              -- 篩選索引 (SQL 2008+)
[ WITH ( 
    FILLFACTOR = 80,                    -- 預留空間減少分頁
    ONLINE = ON,                        -- 企業版：線上建立不鎖表
    DATA_COMPRESSION = PAGE             -- 壓縮減少 I/O
) ];

索引類型選擇指南

• CLUSTERED：資料實體排序，一表一個，適合範圍查詢、排序、分組欄位
• NONCLUSTERED：邏輯指標結構，最多 999 個，適合精確查找、覆蓋查詢
• UNIQUE：強制唯一，優化器可產生更優計劃
• INCLUDE：非鍵欄位只存葉層，不計入鍵長度限制 (900 bytes)，解決覆蓋查詢
• FILTERED：WHERE Status = 1 只索引熱門資料，極大縮小索引體積

三、CONSTRAINT — 約束的完整管理

新增/刪除/停用/啟用

-- 新增 (表已存在)
ALTER TABLE OrderHeader 
ADD CONSTRAINT CK_OrderDate CHECK (OrderDate <= SYSUTCDATETIME());

-- 刪除
ALTER TABLE OrderHeader DROP CONSTRAINT CK_OrderDate;

-- 停用檢查 (大量匯入加速)
ALTER TABLE OrderHeader NOCHECK CONSTRAINT FK_Order_Customer;
-- 匯入完成後重新啟用並驗證現有資料
ALTER TABLE OrderHeader WITH CHECK CHECK CONSTRAINT FK_Order_Customer;

命名慣例建議 (採用 sp_helpconstraint 可讀性)

四、維護與演進策略

1. 版本控管：所有 DDL 納入 Git，採用遷移腳本 (如 Flyway, DbUp, EF Migrations)
2. 向後相容：新增欄位預設 NULL 或 DEFAULT，避免破壞既有 INSERT
3. 線上變更：大表加欄位/索引用 ONLINE=ON、FILLFACTOR、分批次
4. 文件化：sp_help 'TableName'、sys.tables/columns/indexes/constraints 定期匯出資料字典

結語：DDL 是資料庫的骨架。嚴謹的命名、約束、索引設計，能讓後續 DML 查詢自然高效，減少 90% 的效能調校成本。