前言
CREATE TABLE 是資料庫設計的起點。正確運用約束條件能在資料庫層面保證資料完整性，減少應用程式防禦性程式碼。本文系統整理六大約束語法、複合鍵設計、臨時表與表變數選型指南。
一、主鍵約束 - 唯一識別每一列
- 特性：不允許 NULL、不允許重複、自動建立叢集索引（預設）。
- 單欄主鍵：
PRIMARY KEY (col1)
- 複合主鍵：
PRIMARY KEY (col1, col2, col3) —— 適用於多對多關聯表、記錄唯一性由多欄位決定的場景。
IF OBJECT_ID('testtable', 'U') IS NOT NULL DROP TABLE testtable;
CREATE TABLE testtable (
col1 VARCHAR(10),
col2 INT,
col3 DATETIME,
col4 NUMERIC(10,2),
col5 XML,
col6 IMAGE,
PRIMARY KEY (col1, col2, col3)
);
二、唯一約束 - 業務鍵防重
- 特性：允許單一 NULL（SQL Server 行為）、可建立多個、自動建立非叢集索引。
- 單欄：
UNIQUE (col1)
- 複合：
UNIQUE (col1, col2)
CREATE TABLE testtable (
col1 CHAR(10) UNIQUE,
col2 INT,
col3 FLOAT
);
三、主鍵 vs 唯一約束 差異速查表
| 特性 | 主鍵 | 唯一約束 |
|---|
| 每張表數量 | 只能 1 個 | 可多個 |
| 允許 NULL | 完全不允許 | 允許單一 NULL |
| 預設索引類型 | 叢集 | 非叢集 |
| 典型用途 | 實體識別鍵 (Surrogate Key) | 業務鍵 |
四、非空約束 - 強制資料存在
CREATE TABLE testtable (
col1 CHAR(10) NOT NULL UNIQUE, -- 組合：非空 + 唯一
col2 INT NOT NULL,
col3 FLOAT
);
五、預設約束 - 降低應用層邏輯
- 常見：
GETDATE()、SYSTEM_USER、常值字串。
CREATE TABLE usertable (
username VARCHAR(20),
loginuser VARCHAR(10) DEFAULT SYSTEM_USER,
logintime DATETIME DEFAULT GETDATE(),
uservocation VARCHAR(50) DEFAULT N'計算機及其相關'
);
六、檢查約束 - 資料域驗證
CREATE TABLE testtable (
userid VARCHAR(10) CHECK (userid LIKE '[a-z]%[1-4]' AND DATALENGTH(userid) = 5),
age INT CHECK (age > 0 AND age <= 150)
);
七、臨時表 vs 表變數：選型決策樹
| 特性 | 區域臨時表 (#temp) | 全域臨時表 (##temp) | 表變數 (@table) |
|---|
| 作用域 | 目前 Session | 所有 Session | 目前 Batch/Proc |
| 儲存位置 | tempdb (磁碟) | tempdb (磁碟) | 記憶體優先，溢寫 tempdb |
| 統計資料 | 有（可重編譯） | 有 | 無（固定 1 行估計） |
| 索引支援 | 完整支援 | 完整支援 | 僅隱含唯一/主鍵索引 |
| 交易記錄 | 完整記錄 | 完整記錄 | 最小記錄 |
| 平行查詢 | 支援 | 支援 | 不支援 |
| 建議資料量 | > 1 萬筆 / 需索引 / 需統計資料 | 跨 Session 共享 | < 1 萬筆 / 簡單暫存 / 避免重編譯 |
八、建表最佳實踐清單
- 命名規範：表 PascalCase、欄位 camelCase、PK 名
PK_TableName、FK 名 FK_TableName_RefTable、CK 名 CK_TableName_Column、DF 名 DF_TableName_Column。
- 資料型別原則：定長用
CHAR/NCHAR，不定長用 VARCHAR/NVARCHAR，金額用 DECIMAL(19,4)，避免 FLOAT/REAL。
- 預設值下推：
CREATE_DATE DEFAULT SYSUTCDATETIME()、IS_DELETED BIT DEFAULT 0。
- 檢查約束替代觸發器：單欄域驗證優先用 CHECK，效能優於 Trigger。
- 大表分區鍵：若預期超億筆，建表即規劃分區欄位（通常為日期）。
