建立資料表時定義完整的約束條件,是確保資料完整性的關鍵。本文詳細說明主鍵、唯一性、非空、預設、檢查等五大約束的語法與差異,並介紹暫存表與資料表變數的特性比較。
主鍵不允許重複值與 NULL,每張表僅能有一個主鍵(可為單欄或複合欄位)。
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)
);
CREATE TABLE testtable (
col1 VARCHAR(10),
col2 INT,
col3 DATETIME,
col4 NUMERIC(10,2),
col5 XML,
col6 IMAGE,
PRIMARY KEY(col1, col2, col3)
);
強制非主鍵欄位值唯一,允許 NULL(但僅限一筆),可建立多個唯一約束。
CREATE TABLE testtable (
col1 CHAR(10),
col2 INT,
col3 FLOAT,
UNIQUE(col1)
);
CREATE TABLE testtable (
col1 CHAR(10),
col2 INT,
col3 FLOAT,
UNIQUE(col1, col2)
);
| 比較項目 | 主鍵 | 唯一約束 |
|---|---|---|
| 允許 NULL | 不允許 | 允許(單一筆) |
| 每張表數量 | 僅一個 | 可多個 |
| 自動建立索引 | 叢集索引 | 非叢集索引 |
| 外來鍵參考 | 可被參考 | 可被參考 |
CREATE TABLE testtable (
col1 CHAR(10) NOT NULL UNIQUE,
col2 INT NOT NULL,
col3 FLOAT
);
為欄位指定預設值,插入資料時若未提供值則自動套用。
IF OBJECT_ID('usertable', 'U') IS NOT NULL DROP TABLE usertable;
CREATE TABLE usertable (
username VARCHAR(20),
loginuser VARCHAR(10) DEFAULT USER,
logintime DATETIME DEFAULT GETDATE(),
uservocation VARCHAR(50) DEFAULT '計算機及其相關'
);
限制欄位輸入值的範圍或格式,防止人為錯誤資料。
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)
);
userid:首字母為小寫、長度 5、尾碼為 1-4age:1-150 之間| 特性 | 暫存表 (#temp / ##temp) | 資料表變數 (@table) |
|---|---|---|
| 儲存位置 | tempdb (磁碟) | 記憶體 (優先) |
| 交易日誌 | 產生日誌 | 不產生日誌 |
| 索引支援 | 可建立索引 | 僅隱含主鍵/唯一索引 |
| 鎖定機制 | 有 | 無 |
| 存活週期 | 工作階段/全域 | 批次/程序結束即銷毀 |
| DDL 修改 | 支援 ALTER TABLE | 不支援 |
| 統計資料 | 完整可靠 | 無統計資料 |
-- 本地暫存表
CREATE TABLE #News (
News_id INT NOT NULL,
NewsTitle VARCHAR(100),
NewsContent VARCHAR(2000),
NewsDateTime DATETIME
);
-- 加上預設值與主鍵
ALTER TABLE #News ADD
CONSTRAINT DF_NewsDateTime DEFAULT (GETDATE()) FOR NewsDateTime,
PRIMARY KEY CLUSTERED (News_id);
DECLARE @News TABLE (
News_id INT NOT NULL,
NewsTitle VARCHAR(100),
NewsContent VARCHAR(2000),
NewsDateTime DATETIME
);
約束是資料品質的守門員,暫存表與表變數則是暫存運算的兩大工具。依據資料量、查詢複雜度與存活需求選擇適當物件,能讓 T-SQL 程式更健壯、高效。