萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

T-SQL 建立資料表完整教學:主鍵、唯一、非空、預設、檢查約束與暫存表

前言

建立資料表時定義完整的約束條件,是確保資料完整性的關鍵。本文詳細說明主鍵、唯一性、非空、預設、檢查等五大約束的語法與差異,並介紹暫存表與資料表變數的特性比較。

一、主鍵約束

主鍵不允許重複值與 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)
);

三、主鍵 vs 唯一約束 比較

比較項目主鍵唯一約束
允許 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-4
  • age:1-150 之間

七、暫存表 vs 資料表變數

特性暫存表 (#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 程式更健壯、高效。

copyright © 萬盛學電腦網 all rights reserved