在關聯式資料庫中,**約束** 是確保資料正確性、一致性與完整性的核心機制。SQL Server 提供五大類約束,涵蓋實體完整性、參考完整性、網域完整性與使用者定義完整性。本文系統整理建立、修改、刪除各類約束的 T-SQL 語法與實戰範例。
CREATE DATABASE [test] ON
(NAME=N'test', FILENAME=N'd:\SQL2kt_Data\test.mdf', SIZE=3MB, MAXSIZE=UNLIMITED, FILEGROWTH=1MB)
LOG ON
(NAME=N'test_log', FILENAME=N'd:\SQL2kt_Data\test_log.ldf', SIZE=1MB, MAXSIZE=2048MB, FILEGROWTH=10%);
GO
| 約束類型 | 目的 | 關鍵字 | 允許 NULL |
|---|---|---|---|
| 主鍵 | 唯一識別每列,建立叢集索引 | PRIMARY KEY | 否 |
| 外鍵 | 建立資料表關聯,維護參考完整性 | FOREIGN KEY REFERENCES | 是 |
| 唯一 | 確保欄位值唯一 | UNIQUE | 是 (僅一筆) |
| 檢查 | 限定欄位值範圍/格式 | CHECK | 視定義 |
| 預設 | 插入時自動填入預設值 | DEFAULT | 視定義 |
CREATE TABLE dbo.test3 (
[tid] INT IDENTITY(100,1) NOT NULL,
[name] VARCHAR(100),
CONSTRAINT [pk_tid] PRIMARY KEY CLUSTERED ([tid] DESC)
) ON [PRIMARY];
說明:DESC 僅影響索引鍵排序順序,資料插入仍為遞增,查詢時最新資料最先回傳。
ALTER TABLE dbo.test5 WITH CHECK ADD CONSTRAINT pk_id PRIMARY KEY (id);
前提:id 欄位必須為 NOT NULL 且值唯一。
ALTER TABLE test5 DROP CONSTRAINT pk_id;
ALTER TABLE dbo.test4
ADD CONSTRAINT fkt
FOREIGN KEY (tid) REFERENCES dbo.test3([tid])
ON UPDATE CASCADE
ON DELETE CASCADE;
ON UPDATE CASCADE:父表主鍵更新時,子表同步更新ON DELETE CASCADE:父表刪除時,子表關聯列同步刪除NO ACTION (預設, 拒絕)、SET NULL、SET DEFAULTALTER TABLE test4 DROP CONSTRAINT fkt;
CREATE TABLE dbo.test6 (
id INT NOT NULL,
vname VARCHAR(20) CONSTRAINT test6_unique UNIQUE NONCLUSTERED (vname ASC)
);
CREATE UNIQUE INDEX idx_test6_vname ON dbo.test6(vname);
前提:現有資料不可重複。
ALTER TABLE test6 WITH NOCHECK
ADD CONSTRAINT test6_check CHECK (vname != 'shit');
ALTER TABLE test3 WITH NOCHECK
ADD CONSTRAINT test3_check2 CHECK (tname NOT IN ('shit','fuck','ohyeah'));
WITH NOCHECK:略過現有資料驗證,僅約束新資料 (大量資料表效能考量)WITH CHECK (預設):驗證現有資料,違規則失敗ALTER TABLE test6 DROP CONSTRAINT test6_check;
CREATE TABLE test4 (
tid INT,
pwd VARCHAR(20) DEFAULT '000000' NOT NULL
);
ALTER TABLE test3 ADD DEFAULT 0 FOR tname1;
-- 建立預設物件
CREATE DEFAULT td AS '預設值';
-- 繫結
EXEC sp_bindefault 'td', 'test2.vname';
-- 解除繫結
EXEC sp_unbindefault 'test2.vname';
-- 建立視圖
CREATE VIEW dbo.view2 AS
SELECT * FROM dbo.test6 WHERE id <= 3;
-- 查詢視圖
SELECT * FROM dbo.view2;
-- 刪除視圖
DROP VIEW dbo.view2;
| 約束類型 | 命名建議 | 範例 |
|---|---|---|
| 主鍵 | PK_資料表_欄位 | PK_Orders_OrderID |
| 外鍵 | FK_子表_父表 | FK_OrderDetails_Orders |
| 唯一 | UQ_資料表_欄位 | UQ_Users_Email |
| 檢查 | CK_資料表_條件描述 | CK_Products_Price_Positive |
| 預設 | DF_資料表_欄位 | DF_Orders_OrderDate |
熟練掌握五大約束的建立、修改、刪除語法,配合合理的命名慣例與分層驗證架構,是打造高品質、可維護資料庫結構的基石。約束雖屬宣告式且簡單,但其背後的資料完整性保證卻是資料庫系統最核心的價值所在。