詳解SQL Server數(shù)據(jù)庫架構(gòu)和對(duì)象、定義數(shù)據(jù)完整性

前言

本節(jié)我們繼續(xù)SQL之旅，本節(jié)我們?nèi)珙}來講講一些基本知識(shí)以及需要注意的地方，若有不妥之處，還望指出，簡短的內(nèi)容，深入的理解。

數(shù)據(jù)庫架構(gòu)和對(duì)象

數(shù)據(jù)庫包含架構(gòu)，而架構(gòu)又包含對(duì)象，架構(gòu)可以看做是表、視圖、存儲(chǔ)過程等對(duì)象的容器。架構(gòu)是一個(gè)命名空間，它被用做對(duì)象名稱的前綴，比如在Cnblogs的架構(gòu)中有一個(gè)名稱為Blogs的表，此時(shí)我們用架構(gòu)式限定式名稱（即兩部分式對(duì)象名稱）所以Blogs表示為Cnblogs.Blogs。如果我們引用對(duì)象時(shí)省略了架構(gòu)名稱，SQL Server將會(huì)檢查對(duì)象是否存在用戶的默認(rèn)架構(gòu)中，如果不是則檢查是否存在dbo架構(gòu)中，當(dāng)我們創(chuàng)建數(shù)據(jù)庫時(shí)，在用戶沒有顯式地指定一個(gè)其他架構(gòu)時(shí)，數(shù)據(jù)庫會(huì)自動(dòng)dbo架構(gòu)作為我們默認(rèn)的架構(gòu)。微軟也建議在代碼中引用對(duì)象時(shí)始終用【兩部分式】對(duì)象名稱，基于此我們推薦的建議時(shí)在引用對(duì)象時(shí)建議：在代碼中始終使用架構(gòu)限定式的對(duì)象名稱即兩部分式名稱。

定義數(shù)據(jù)完整性

關(guān)系模型最大好處則是我們能夠自定義數(shù)據(jù)完整性，同時(shí)數(shù)據(jù)完整性是關(guān)系模型不可或缺的一部分，什么是數(shù)據(jù)完整性，說的通透一點(diǎn)則是對(duì)數(shù)據(jù)進(jìn)行聲明式約束，在SQL Server中聲明式約束包括：主鍵約束、唯一鍵約束、外鍵約束、檢查約束、默認(rèn)約束。下面我們一一來介紹這幾個(gè)約束。

主鍵約束

下面首先來創(chuàng)建一個(gè)表：

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

主鍵約束用來強(qiáng)制行的唯一性，上述我們無法表示行的唯一性，現(xiàn)在我們添加約束來強(qiáng)制行的唯一性，用PRIMARY KEY約束如下。

ALTER TABLE dbo.Blogs
ADD CONSTRAINT pk_constraint_blogId PRIMARY KEY(BlogId)

在鍵文件夾中則生成對(duì)列BlogId的約束即升級(jí)為主鍵，如下：

當(dāng)對(duì)主鍵插入重復(fù)數(shù)據(jù)時(shí)會(huì)提示插入重復(fù)鍵失敗，違反約束。為了強(qiáng)制邏輯主鍵約束的唯一性，SQL Server會(huì)在后臺(tái)創(chuàng)建一個(gè)唯一索引，唯一索引是SQL Server為了強(qiáng)制唯一性而使用的一種物理機(jī)制，索引（不一定是唯一索引）是為了加速查詢，避免不必要的全表掃描。

唯一約束

唯一約束強(qiáng)制行的唯一性，允許我們?cè)谧约旱臄?shù)據(jù)庫中實(shí)現(xiàn)關(guān)系模型的備用鍵概念。它與主鍵不同，可以在同一個(gè)表中定義多個(gè)唯一約束同時(shí)允許多個(gè)NULL標(biāo)記（類似NULL標(biāo)記彼此不同），但是SQL Server拒絕重復(fù)NULL標(biāo)記（類似兩個(gè)NULL標(biāo)記彼此相等）通過UNIQUE來約束。如下所示對(duì)BlogName進(jìn)行唯一約束。

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

此時(shí)添加唯一約束結(jié)果如下

一個(gè)個(gè)嘗試發(fā)現(xiàn)居然對(duì)字符串和文本類型無法添加唯一約束，漲知識(shí)了，不知道為何不能添加唯一約束（補(bǔ)充：在sql 2008R2卻可以建立，真納悶）。

通過上述對(duì)主鍵約束和唯一約束的講解，我們就搞清楚主鍵約束和唯一約束了呢？博主看的是SQL Server2012基礎(chǔ)教程，教程就講到這里結(jié)束，至此我是還沒弄清楚，主鍵約束和唯一約束到底應(yīng)該怎樣用以及主鍵約束和唯一約束有什么區(qū)別？

（1）對(duì)鍵添加主鍵約束，那么能不能在此基礎(chǔ)上添加唯一約束呢？

我們?cè)谏鲜鲆呀?jīng)添加BlogId為主鍵約束的基礎(chǔ)上來添加唯一約束，如下

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

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

（2）上述基礎(chǔ)教程中也講到唯一性約束的列可以允許多個(gè)NULL標(biāo)記，真的是這樣？我們看看另外一種情況

我們創(chuàng)建如下表

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

接下來對(duì)Id約束為唯一約束。

ALTER TABLE test
ADD CONSTRAINT UNQ UNIQUE(Id)

此時(shí)我們對(duì)Id添加一個(gè)NULL試試看，結(jié)果可以插入還是不可以呢？

INSERT INTO TEST VALUES(NULL,'B')

不是唯一約束的列可以為NULL么，難道教程出錯(cuò)了或者sb翻譯出錯(cuò)了么，這事我們應(yīng)該看看定義表時(shí)列Id是不能為NULL的，所以到這里我們的疑問算是結(jié)束了，唯一約束的列是可以為NULL的。

（3）主鍵約束和唯一約束的區(qū)別？

主鍵約束：通過對(duì)列強(qiáng)制唯一性，此時(shí)主鍵在列上創(chuàng)建一個(gè)聚集索引且主鍵不能為空。

唯一約束：通過對(duì)列強(qiáng)制唯一性，此時(shí)在列上創(chuàng)建的唯一鍵為非聚集索引，唯一約束僅僅允許一個(gè)NULL值。

二者最大區(qū)別在于：主鍵約束強(qiáng)調(diào)的是行的唯一性來標(biāo)識(shí)行，不允許重復(fù)，而唯一約束強(qiáng)調(diào)的是列的唯一性不允許重復(fù)。

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

【1】唯一索引通過主鍵約束和唯一約束都可以創(chuàng)建。

　　如果表中不存在聚集索引的話并且我們沒有明確指定一個(gè)非聚集索引的話，通過主鍵約束將自動(dòng)創(chuàng)建一個(gè)唯一聚集索引。

　　當(dāng)創(chuàng)建唯一約束時(shí)，默認(rèn)情況下一個(gè)非聚集索引會(huì)被創(chuàng)建來強(qiáng)制一個(gè)唯一約束，如果在表中聚集索引不存在的話，我們可以指定一個(gè)聚集索引。

【2】唯一約束和唯一索引區(qū)別

我們接下來創(chuàng)建一個(gè)表，如下：

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

首先我只對(duì)Code創(chuàng)建唯一索引

CREATE UNIQUE INDEX uq_ix ON dbo.test(Code)

此時(shí)我們?cè)僭贑ode列上添加唯一約束：

ALTER TABLE StudyTest.dbo.test
ADD CONSTRAINT uq_nonclster_ix UNIQUE(Code)

此時(shí)我們?cè)谒饕募A下可以看到所創(chuàng)建的唯一索引和唯一約束所創(chuàng)建的唯一非聚集索引

看起來二者都是唯一非聚集索引，只是圖標(biāo)不一樣而已，二者應(yīng)該是一樣的吧

（5）唯一索引和唯一約束的區(qū)別在哪里？唯一約束能替代唯一索引嗎？

【1】返回錯(cuò)誤碼不同

當(dāng)我們插入數(shù)據(jù)時(shí)，唯一索引返回錯(cuò)誤代碼為2601

唯一約束返回的錯(cuò)誤代碼為2627

【2】唯一約束不能篩選，而唯一索引能進(jìn)行篩選，如下

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

總結(jié)：上述只是表示二者在使用上的不同，對(duì)于唯一約束和唯一索引并沒有什么很大的差異，同時(shí)對(duì)于唯一約束和唯一索引在查詢性能上也沒有很大的不同，對(duì)于唯一約束我們一直強(qiáng)調(diào)的是數(shù)據(jù)完整性，對(duì)列進(jìn)行唯一約束保證其值不能重復(fù)，這同時(shí)對(duì)于建立索引查詢時(shí)性能會(huì)有顯著的提升。

外鍵約束

外鍵約束也用來強(qiáng)制數(shù)據(jù)完整性，外鍵的目的是限制在外鍵列中允許的值主要存在于那些被引用列中。下面我們來演示外鍵約束，我們創(chuàng)建如下雇員表和部門表：

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

由上我們知道雇員表是依賴于部門表，一個(gè)雇員到底是在哪個(gè)部門呢？所以此時(shí)雇員表中的部門Id應(yīng)該是部門表中部門Id的外鍵，接下來我們進(jìn)行外鍵約束，如下：

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

此時(shí)執(zhí)行完你會(huì)發(fā)現(xiàn)如下錯(cuò)誤：

現(xiàn)在我們知道外鍵可不是隨便就能建立的，為什么會(huì)出現(xiàn)我們引用部門表并將其雇員表中部門Id作為外鍵約束的錯(cuò)誤呢？通過上述錯(cuò)誤我們知道在引用表即部門表中沒有其匹配的主鍵或候選鍵，這是指的什么，它的意思是引用表中的外鍵必須是被引用表中的完整主鍵，而不是作為被引用表的一部分，說的更加明確一點(diǎn)則是被引用表即部門表中的部門Id應(yīng)該是主鍵，在這里我們未對(duì)部門表中部門Id進(jìn)行主鍵約束而導(dǎo)致如上錯(cuò)誤。我們添加主鍵約束即可

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

此時(shí)外鍵約束才算建立完成。到這里其實(shí)還存在一種可能，當(dāng)我們需要引用的表中已經(jīng)存在一個(gè)主鍵，而不是由外鍵引用的列，此時(shí)部門表中的Id不是作為主鍵，而我們雇員表中的部門Id又需要將部門Id作為外鍵約束，這個(gè)時(shí)候我們只需要在部門表中部門Id上創(chuàng)建唯一或者唯一約束即可。

CREATE UNIQUE INDEX [IX_DepartmentID]
ON [dbo].[Department] ( [DepartmentID] )
GO
ALTER 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] )
GO
ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )
GO

Check約束

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

比如在雇員表中再添加一個(gè)薪水字段，很顯然薪水必須為正值，此時(shí)我們則可以像如下進(jìn)行Check約束

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

如果試圖插入非正值，將會(huì)被數(shù)據(jù)庫所拒絕。我們需要注意的是Check約束只是對(duì)于結(jié)果為false才會(huì)拒絕，如果結(jié)果為True或者UNKNOWN是會(huì)被接受，即當(dāng)結(jié)果為NULL時(shí)也會(huì)插入或者修改成功。

默認(rèn)約束

默認(rèn)約束無非就是當(dāng)建立表時(shí)給定一個(gè)默認(rèn)值，常見的是在表中存在添加數(shù)據(jù)的日期這一列，此時(shí)我們完全給定一個(gè)默認(rèn)值，取當(dāng)前的日期。默認(rèn)約束用DEFAULT關(guān)鍵字表示。例如如下：

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

總結(jié)

本節(jié)我們?cè)敿?xì)講解了主鍵約束和唯一約束這一塊，其余相對(duì)比較簡單，算是略過，到此結(jié)束，下節(jié)再會(huì)。

以上就是本文的全部內(nèi)容，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，同時(shí)也希望多多支持腳本之家！