SQL Server 數據庫架構與對象命名規範、數據完整性約束深度實戰

五大聲明式完整性約束實戰

1. 主鍵約束

CREATE TABLE Blogs (BlogId INT NOT NULL, BlogName VARCHAR(MAX) NOT NULL)
ALTER TABLE dbo.Blogs ADD CONSTRAINT pk_constraint_blogId PRIMARY KEY(BlogId)

• 強制唯一性 + 非空。
• 底層自動建立唯一聚集索引（若表無聚集索引且未指定 NONCLUSTERED）。
• 違約報錯：訊息 2627，嚴重級 14。

2. 唯一約束

ALTER TABLE dbo.Blogs ADD CONSTRAINT uq_constraint_blogname UNIQUE(BlogName)

⚠️ 大字串類型限制： VARCHAR(MAX)/NVARCHAR(MAX)/TEXT/NTEXT 無法直接建立唯一約束（SQL 2008R2 可，2012+ 不可）。

3. 主鍵 vs 唯一約束深度對比

實戰驗證：同列能否同時加主鍵+唯一？

ALTER TABLE dbo.Blogs ADD CONSTRAINT uq_constraint_blogId UNIQUE(BlogId) -- 成功

✅ 同列可同時擁有主鍵與唯一約束（雖無實際意義）。

實戰驗證：唯一約束列是否可為 NULL？

CREATE TABLE test (Id INT NOT NULL, NAME VARCHAR(MAX) NOT NULL)
ALTER TABLE test ADD CONSTRAINT UNQ UNIQUE(Id)
INSERT INTO TEST VALUES(NULL,'B') -- 失敗：Id 定義為 NOT NULL

結論：唯一約束允許 NULL，但前提是列定義允許 NULL。

4. 唯一約束 vs 唯一索引：細微但關鍵差異

CREATE TABLE test (Id INT NOT NULL PRIMARY KEY, Code INT)
CREATE UNIQUE INDEX uq_ix ON dbo.test(Code)                    -- 唯一索引
ALTER TABLE test ADD CONSTRAINT uq_nonclster_ix UNIQUE(Code)   -- 唯一約束

差異點

1. 錯誤代碼不同： 唯一索引 → 2601；唯一約束 → 2627。應用層異常處理需區分。
2. 篩選索引支援： 唯一索引可加 WHERE Code IS NOT NULL 建立篩選唯一索引；唯一約束不支援。
3. 語意層面： 約束屬邏輯模型，索引屬物理實現。查詢優化器對兩者效能處理基本一致。

CREATE UNIQUE NONCLUSTERED INDEX uq_code_filter ON test(Code) WHERE Code IS NOT NULL -- 成功

5. 外鍵約束

CREATE TABLE [dbo].[Department] ([DepartmentID] INT NOT NULL IDENTITY, [DepartmentName] VARCHAR(50))
CREATE TABLE [dbo].[Employee] ([EmployeeID] INT NOT NULL IDENTITY, [FirstName] VARCHAR(50), [LastName] VARCHAR(50), [DepartmentID] INT)

-- 關鍵：被參考列必須有主鍵或唯一約束/索引
ALTER TABLE [dbo].[Department] ADD CONSTRAINT [PK_Department] PRIMARY KEY ([DepartmentID])
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentID]) REFERENCES [dbo].[Department] ([DepartmentID])

❌ 無主鍵時建 FK 失敗：被參考表無匹配主鍵或候選鍵。

✅ 解決：被參考列加主鍵，或加唯一索引/約束。

6. CHECK 約束

ALTER TABLE dbo.Employees ADD CONSTRAINT CHK_Employees_salary CHECK(salary > 0.00)

• 僅拒絕 FALSE；TRUE 或 UNKNOWN (NULL) 均通過。
• 可跨列驗證：CHECK (end_date >= start_date)。

7. DEFAULT 約束

ALTER TABLE dbo.Employees ADD CONSTRAINT DFT_Employees_updateTime DEFAULT(GETDATE()) FOR UpdateTime

• 隱式插入時自動填充，顯式指定值則覆蓋預設值。
• 可搭配 NEWID()、NEWSEQUENTIALID() 等函數。

總結

• 架構限定名稱（Schema.Object）是專業代碼基本修養。
• 主鍵 = 實體識別（唯一+非空+聚集索引）；唯一 = 屬性唯一（可單 NULL+非聚集索引）。
• 唯一索引支援篩選、錯誤碼 2601；唯一約束語意化、錯誤碼 2627。
• 外鍵前提：被參考列必須有主鍵/唯一約束/唯一索引。
• CHECK 僅阻擋 FALSE，NULL 會通過 —— 業務邏輯需配合 NOT NULL 使用。