萬盛學電腦網

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

SQL Server 資料庫與資料表約束完整實作指南:主鍵、外鍵、唯一、檢查、預設

前言:約束是資料完整性的守門員

在關聯式資料庫中,**約束** 是確保資料正確性、一致性與完整性的核心機制。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視定義

實戰範例:完整 DDL 腳本

1. 主鍵約束

建表時定義 (含叢集索引方向)

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;

2. 外鍵約束

建立外鍵 (含級聯動作)

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 DEFAULT

刪除外鍵

ALTER TABLE test4 DROP CONSTRAINT fkt;

3. 唯一約束

建表時定義 (可指定非叢集索引排序)

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);
前提:現有資料不可重複。

4. 檢查約束

建立 (支援多條件邏輯)

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;

5. 預設約束

建表時指定

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

分層驗證策略

  1. 前端 (JavaScript):使用者體驗、即時回饋
  2. 邏輯層 (C#/Java):商業規則、唯一性檢查 (減輕 DB 負載)
  3. 資料庫層 (約束):最終防線,確保資料絕對完整性,防止繞過應用程式直接寫入髒資料

常見陷阱與避坑指南

  • 外鍵參考欄位必須有索引:父表主鍵/唯一鍵自動有索引,子表外鍵欄位**強烈建議手動建立非叢集索引**,提升 JOIN 與刪除父記錄時的檢查效能
  • CHECK 約束不可參考其他資料列/資料表:僅能檢查當前列,跨列/跨表驗證需用觸發器或應用層
  • 預設值不會觸發 CHECK 約束重新評估:若預設值違反 CHECK,插入仍會失敗
  • TRUNCATE TABLE 無法用於有外鍵參考的資料表:需先刪除外鍵或改用 DELETE

結語

熟練掌握五大約束的建立、修改、刪除語法,配合合理的命名慣例與分層驗證架構,是打造高品質、可維護資料庫結構的基石。約束雖屬宣告式且簡單,但其背後的資料完整性保證卻是資料庫系統最核心的價值所在。

copyright © 萬盛學電腦網 all rights reserved