詳解SQL Server資料庫結構描述和對象、定義資料完整性_MsSql

前言

本節我們繼續SQL之旅，本節我們如題來講講一些基本知識以及需要注意的地方，若有不妥之處，還望指出，簡短的內容，深入的理解。

資料庫結構描述和對象

資料庫包含架構，而架構又包含對象，架構可以看做是表、視圖、預存程序等對象的容器。架構是一個命名空間，它被用做對象名稱的首碼，比如在Cnblogs的架構中有一個名稱為Blogs的表，此時我們用架構式限定式名稱（即兩部分式對象名稱）所以Blogs表示為Cnblogs.Blogs。如果我們引用對象時省略了架構名稱，SQL Server將會檢查對象是否存在使用者的預設架構中，如果不是則檢查是否存在dbo架構中，當我們建立資料庫時，在使用者沒有顯式地指定一個其他架構時，資料庫會自動dbo架構作為我們預設的架構。微軟也建議在代碼中引用對象時始終用【兩部分式】對象名稱，基於此我們推薦的建議時在引用對象時建議：在代碼中始終使用架構限定式的對象名稱即兩部分式名稱。

定義資料完整性

關聯式模式最大好處則是我們能夠自訂資料完整性，同時資料完整性是關聯式模式不可或缺的一部分，什麼是資料完整性，說的通透一點則是對資料進行聲明式約束，在SQL Server中聲明式約束包括：主鍵約束、唯一鍵約束、外鍵約束、檢查約束、預設約束。下面我們一一來介紹這幾個約束。

主鍵約束

下面首先來建立一個表：

CREATE TABLE Blogs( BlogId INT NOT NULL, BlogName VARCHAR(max) NOT NULL);

主鍵約束用來強制行的唯一性，上述我們無法表示行的唯一性，現在我們添加約束來強制行的唯一性，用PRIMARY KEY約束如下。

ALTER TABLE dbo.BlogsADD CONSTRAINT pk_constraint_blogId PRIMARY KEY(BlogId)

在鍵檔案夾中則產生對列BlogId的約束即升級為主鍵，如下：

當對主鍵插入重複資料時會提示插入重複鍵失敗，違反約束。為了強制邏輯主鍵約束的唯一性，SQL Server會在後台建立一個唯一索引，唯一索引是SQL Server為了強制唯一性而使用的一種物理機制，索引（不一定是唯一索引）是為了加速查詢，避免不必要的全表掃描。

唯一約束

唯一約束強制行的唯一性，允許我們在自己的資料庫中實現關聯式模式的備用鍵概念。它與主鍵不同，可以在同一個表中定義多個唯一約束同時允許多個NULL標記（類似NULL標記彼此不同），但是SQL Server拒絕重複NULL標記（類似兩個NULL標記彼此相等）通過UNIQUE來約束。如下所示對BlogName進行唯一約束。

ALTER TABLE dbo.BlogsADD CONSTRAINT uq_constraint_blogname UNIQUE(BlogName)

此時添加唯一約束結果如下

一個個嘗試發現居然對字串和文本類型無法添加唯一約束，漲知識了，不知道為何不能添加唯一約束（補充：在sql 2008R2卻可以建立，真納悶）。

通過上述對主鍵約束和唯一約束的講解，我們就搞清楚主鍵約束和唯一約束了呢？博主看的是SQL Server2012基礎教程，教程就講到這裡結束，至此我是還沒弄清楚，主鍵約束和唯一約束到底應該怎樣用以及主鍵約束和唯一約束有什麼區別？

（1）對鍵添加主鍵約束，那麼能不能在此基礎上添加唯一約束呢？

我們在上述已經添加BlogId為主鍵約束的基礎上來添加唯一約束，如下

ALTER TABLE dbo.BlogsADD CONSTRAINT uq_constraint_blogId UNIQUE(BlogId)

通過上述我們知道對同一列既可以添加主鍵約束也可以添加唯一約束。

（2）上述基礎教程中也講到唯一性限制式的列可以允許多個NULL標記，真的是這樣？我們看看另外一種情況

我們建立如下表

create table test (Id INT NOT NULL,NAME VARCHAR(max) NOT NULL)

接下來對Id約束為唯一約束。

ALTER TABLE testADD CONSTRAINT UNQ UNIQUE(Id)

此時我們對Id添加一個NULL試試看，結果可以插入還是不可以呢？

INSERT INTO TEST VALUES(NULL,'B')

不是唯一約束的列可以為NULL麼，難道教程出錯了或者sb翻譯出錯了麼，這事我們應該看看定義表時列Id是不能為NULL的，所以到這裡我們的疑問算是結束了，唯一約束的列是可以為NULL的。

（3）主鍵約束和唯一約束的區別？

主鍵約束：通過對列強制唯一性，此時主鍵在列上建立一個叢集索引且主鍵不可為空。

唯一約束：通過對列強制唯一性，此時在列上建立的唯一鍵為非叢集索引，唯一約束僅僅允許一個NULL值。

二者最大區別在於：主鍵約束強調的是行的唯一性來標識行，不允許重複，而唯一約束強調的是列的唯一性不允許重複。

（4）主鍵約束和唯一約束都可以建立唯一索引

【1】唯一索引通過主鍵約束和唯一約束都可以建立。

　　如果表中不存在叢集索引的話並且我們沒有明確指定一個非叢集索引的話，通過主鍵約束將自動建立一個唯一叢集索引。

　　當建立唯一約束時，預設情況下一個非叢集索引會被建立來強制一個唯一約束，如果在表中叢集索引不存在的話，我們可以指定一個叢集索引。

【2】唯一約束和唯一索引區別

我們接下來建立一個表，如下：

CREATE TABLE test( Id INT NOT NULL PRIMARY KEY, Code INT)

首先我只對Code建立唯一索引

CREATE UNIQUE INDEX uq_ix ON dbo.test(Code)

此時我們再在Code列上添加唯一約束：

ALTER TABLE StudyTest.dbo.testADD CONSTRAINT uq_nonclster_ix UNIQUE(Code)

此時我們在索引檔案夾下可以看到所建立的唯一索引和唯一約束所建立的唯一非叢集索引

看起來二者都是唯一非叢集索引，只是表徵圖不一樣而已，二者應該是一樣的吧

（5）唯一索引和唯一約束的區別在哪裡？唯一約束能替代唯一索引嗎？

【1】返回錯誤碼不同

當我們插入資料時，唯一索引返回錯誤碼為2601

唯一約束返回的錯誤碼為2627

【2】唯一約束不能篩選，而唯一索引能進行篩選，如下

CREATE UNIQUE NONCLUSTERED INDEX uq_code_filterON test(Code) WHERE Code is not null;

總結：上述只是表示二者在使用上的不同，對於唯一約束和唯一索引並沒有什麼很大的差異，同時對於唯一約束和唯一索引在查詢效能上也沒有很大的不同，對於唯一約束我們一直強調的是資料完整性，對列進行唯一約束保證其值不能重複，這同時對於建立索引查詢時效能會有顯著的提升。

外鍵約束

外鍵約束也用來強制資料完整性，外鍵的目的是限制在外鍵列中允許的值主要存在於那些被引用列中。下面我們來示範外鍵約束，我們建立如下僱員表和部門表：

USE SQLStudy;IF OBJECT_ID('dbo.Department','U') IS NOT NULL DROP TABLE dbo.DepartmentCREATE TABLE [dbo].[Department] ( [DepartmentID] INT NOT NULL IDENTITY, [DepartmentName] VARCHAR(50))GOCREATE TABLE [dbo].[Employee] ( [EmployeeID] INT NOT NULL IDENTITY, [FirstName] VARCHAR(50), [LastName] VARCHAR(50), [DepartmentID] INT)

由上我們知道僱員表是依賴於部門表，一個僱員到底是在哪個部門呢？所以此時僱員表中的部門Id應該是部門表中部門Id的外鍵，接下來我們進行外鍵約束，如下：

ALTER TABLE [dbo].[Employee]ADD CONSTRAINT [FK_Employee_Department]FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )

此時執行完你會發現如下錯誤：

現在我們知道外鍵可不是隨便就能建立的，為什麼會出現我們引用部門表並將其僱員表中部門Id作為外鍵約束的錯誤呢？通過上述錯誤我們知道在參考資料表即部門表中沒有其匹配的主鍵或候選索引鍵，這是指的什麼，它的意思是參考資料表中的外鍵必須是被參考資料表中的完整主鍵，而不是作為被參考資料表的一部分，說的更加明確一點則是被參考資料表即部門表中的部門Id應該是主鍵，在這裡我們未對部門表中部門Id進行主鍵約束而導致如上錯誤。我們添加主鍵約束即可

ALTER TABLE [dbo].[Department]ADD CONSTRAINT [PK_Department] PRIMARY KEY ( [DepartmentID] )GO

此時外鍵約束才算建立完成。到這裡其實還存在一種可能，當我們需要引用的表中已經存在一個主鍵，而不是由外鍵引用的列，此時部門表中的Id不是作為主鍵，而我們僱員表中的部門Id又需要將部門Id作為外鍵約束，這個時候我們只需要在部門表中部門Id上建立唯一或者唯一約束即可。

CREATE UNIQUE INDEX [IX_DepartmentID]ON [dbo].[Department] ( [DepartmentID] )GOALTER TABLE [dbo].[Employee]ADD CONSTRAINT [FK_Employee_Department]FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )GO

或者唯一約束

CREATE UNIQUE INDEX [IX_DepartmentID]ON [dbo].[Department] ( [DepartmentID] )GOALTER TABLE [dbo].[Employee]ADD CONSTRAINT [FK_Employee_Department]FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )GO

Check約束

Check約束定義一個謂詞，要插入到表中的行或者被修改的行必須滿足此要求。

比如在僱員表中再添加一個薪水欄位，很顯然薪水必須為正值，此時我們則可以像如下進行Check約束

ALTER TABLE dbo.EmployeesADD CONSTRAINT CHK_Employees_salaryCHECK(salary > 0.00)

如果試圖插入非正值，將會被資料庫所拒絕。我們需要注意的是Check約束只是對於結果為false才會拒絕，如果結果為True或者UNKNOWN是會被接受，即當結果為NULL時也會插入或者修改成功。

預設約束

預設約束無非就是當建立表時給定一個預設值，常見的是在表中存在添加資料的日期這一列，此時我們完全給定一個預設值，取當前的日期。預設約束用DEFAULT關鍵字表示。例如如下：

ALTER TABLE dbo.EmployeesADD CONSTRAINT DFT_Employees_updateTimeDEFAULT(GETDATE()) FOR UpdateTime

總結

本節我們詳細講解了主鍵約束和唯一約束這一塊，其餘相對比較簡單，算是略過，到此結束，下節再會。

以上就是本文的全部內容，希望本文的內容對大家的學習或者工作能帶來一定的協助，如果有疑問大家可以留言交流，同時也希望多多支援雲棲社區！