T-SQL數(shù)據(jù)完整性_主鍵_外鍵

1、-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 20051數(shù)據(jù)完整性 在SQL Server 2005中，根據(jù)數(shù)據(jù)完整措施所作用的數(shù)據(jù)庫(kù)對(duì)象和范圍不同，可以將數(shù)據(jù)完整性分為以下幾種： 1、實(shí)體完整性（主鍵、唯一性約束） 2、參照完整性（外鍵約束） 3、域完整性（check、默認(rèn)值約束） 4、用戶(hù)定義的完整性-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 20052主鍵(PRIMARY KEY)約束 主鍵約束使用數(shù)據(jù)表中的一列數(shù)據(jù)或者多列數(shù)據(jù)來(lái)唯一的標(biāo)識(shí)一行數(shù)據(jù)。也就是

2、說(shuō)，在數(shù)據(jù)表中不能存在主鍵相同的兩行數(shù)據(jù)，而且主鍵約束下的列不能為空（NULL），在創(chuàng)建表時(shí)，最好每個(gè)數(shù)據(jù)表都擁有自己唯一的主鍵，主鍵也可以由幾個(gè)列組合成。每個(gè)表只能有一個(gè)主鍵。 不能定義TEXT或IMAGE數(shù)據(jù)類(lèi)型的字段列為主鍵。-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 20053SSMS創(chuàng)建主鍵的步驟 在創(chuàng)建表結(jié)構(gòu)的時(shí)候，選定列名或列名組合（shift鍵）,在所選列上右擊彈出相應(yīng)菜單，如右圖所示。 單擊有鑰匙圖標(biāo)的菜單項(xiàng)。 創(chuàng)建成功后該列名前出現(xiàn)一個(gè)小鑰匙形狀的圖標(biāo)如右圖所示。 -江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院

3、計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 20054創(chuàng)建表時(shí)定義主鍵CREATE TABLE Tb_Class( ClassId char(8) PRIMARY KEY, ClassName varchar(30) NOT NULL, DeptId char(2) NOT NULL, ClassStuNumber int)-CREATE TABLE Tb_Class( ClassId char(8) NOT NULL, ClassName varchar(30) NOT NULL, DeptId char(2) NOT NULL, ClassStuNumber in

4、t, CONSTRAINT PK_ClassId PRIMARY KEY (ClassId,ClassName)-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 20055向已有表中添加主鍵 先創(chuàng)建一個(gè)無(wú)主鍵的表CREATE TABLE Tb_Class( ClassId char(8) NOT NULL, ClassName varchar(30) NOT NULL, DeptId char(2) NOT NULL, ClassStuNumber int) 再給相應(yīng)的列（ ClassId ）添加主鍵約束ALTER TABLE Tb

5、_ClassADD CONSTRAINT PK_ClassId PRIMARY KEY (ClassId)- 刪除主鍵約束ALTER TABLE Tb_ClassDROP CONSTRAINT PK_ClassId PRIMARY KEY (ClassId)-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 20056外鍵（Foreign Key）約束 外鍵約束定義了表之間的關(guān)系，主要用來(lái)維護(hù)兩個(gè)表之間的一致性。出于某種關(guān)聯(lián)的需要，當(dāng)一個(gè)表需要引用另一個(gè)表的主鍵主鍵作為自己的一個(gè)字段時(shí)，我們稱(chēng)這個(gè)引用來(lái)的字段為外鍵外鍵。 這樣，當(dāng)主鍵

6、更新或刪除時(shí)，其它所有表中與這個(gè)主鍵關(guān)聯(lián)的外鍵關(guān)聯(lián)的外鍵也將被相應(yīng)的更新或刪除。當(dāng)向外鍵所在的表插入或更新數(shù)據(jù)（外鍵字段）時(shí)，如果與外鍵表相關(guān)聯(lián)的主鍵表的主鍵主鍵中無(wú)與插入或更新的外外鍵鍵有相同的值時(shí)，系統(tǒng)會(huì)報(bào)錯(cuò)并拒絕插入或更新數(shù)據(jù)。 不能定義TEXT或IMAGE數(shù)據(jù)類(lèi)型的列為外鍵。-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 20057外鍵約束-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 20058外鍵約束-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-20

7、22-5-24Microsoft SQL Server 20059在創(chuàng)建表時(shí)定義外鍵約束CREATE TABLE Tb_Dept( DeptId char(2) PRIMARY KEY, DeptName varchar(16) NOT NULL)-CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL, CONSTRAINT FK_DeptId FOREIGN KEY (DeptId) REFERENCES Tb_Dept (DeptId)-江

8、陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200510上述創(chuàng)建外鍵約束的語(yǔ)句可以簡(jiǎn)化為如下形式：-CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL REFERENCES Tb_Dept)-從添加外鍵約束時(shí)只需添加REFERENCES關(guān)鍵字和被引用的表名。這里需要注意的是，外鍵列和被引用表中的主鍵列應(yīng)該名稱(chēng)相同，且具有相同的列數(shù)據(jù)類(lèi)型。在創(chuàng)建表時(shí)定義外鍵約束-江陰職業(yè)技術(shù)學(xué)院計(jì)

9、算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200511級(jí)聯(lián)更新、刪除CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL REFERENCES Tb_Dept ON UPDATE CASCADE ON DELETE CASCADE)-CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, Dep

10、tId char(2) NOT NULL, CONSTRAINT FK_DeptId FOREIGN KEY (DeptId) REFERENCES Tb_Dept (DeptId) ON UPDATE CASCADE ON DELETE CASCADE)-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200512添加和刪除外鍵約束 先創(chuàng)建一個(gè)無(wú)外鍵的表CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char

11、(2) NOT NULL) 再給相應(yīng)的列（ DeptId ）添加外鍵約束ALTER TABLE Tb_StudentADD CONSTRAINT FK_DeptId FOREIGN KEY (DeptId) REFERENCES Tb_Dept (DeptId) - 刪除外鍵約束ALTER TABLE Tb_StudentDROP CONSTRAINT FK_DeptId-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200513SSMS設(shè)置外鍵約束-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Micros

12、oft SQL Server 200514SSMS設(shè)置外鍵約束-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200515SSMS設(shè)置外鍵約束-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200516外鍵約束的作用 修改父表主鍵時(shí)檢查 刪除父表記錄時(shí)檢查 在子表中插入數(shù)據(jù)時(shí)檢查（外鍵）-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200517參照完整性（外鍵約束） 參照完整性是指兩個(gè)表的主關(guān)鍵字（PR

13、IMARY KEY）和外關(guān)鍵字（FOREIGN KEY）的數(shù)據(jù)要對(duì)應(yīng)一致。它確保了有主關(guān)鍵字的表中對(duì)應(yīng)其它表的外關(guān)鍵字的行存在，即保證了表之間的數(shù)據(jù)的一致性，防止了數(shù)據(jù)丟失或無(wú)意義的數(shù)據(jù)在數(shù)據(jù)庫(kù)中擴(kuò)散。 參照完整性是建立在外關(guān)鍵字和主關(guān)鍵字之上的。這樣的一致性要求不能引用不存在的值，如果鍵值更改了，那么在整個(gè)數(shù)據(jù)庫(kù)中，對(duì)該鍵值的所有引用要進(jìn)行一致的更改。在被參照表（主表）中，當(dāng)其主鍵值被其他表所參照時(shí)，該行不能被刪除也不允許改變。在參照表（從表）中，不允許參照不存在的主鍵值。 -江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200

14、518唯一性（UNIQUE）約束 可以使用 UNIQUE 約束確保在非主鍵列中不輸入重復(fù)的值。盡管 UNIQUE 約束和 PRIMARY KEY 約束都強(qiáng)制唯一性，但想要強(qiáng)制一列或多列組合（不是主鍵）的唯一性時(shí)應(yīng)使用 UNIQUE 約束而不是 PRIMARY KEY 約束。 可以對(duì)一個(gè)表定義多個(gè) UNIQUE 約束，但只能定義一個(gè) PRIMARY KEY 約束。 而且，UNIQUE 約束允許 NULL 值，這一點(diǎn)與 PRIMARY KEY 約束不同。不過(guò)，當(dāng)與參與 UNIQUE 約束的任何值一起使用時(shí)，每列只允許一個(gè)空值。 FOREIGN KEY 約束可以引用 UNIQUE 約束。-江陰職業(yè)技

15、術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200519 創(chuàng)建表時(shí)，可以創(chuàng)建 UNIQUE 約束作為表定義的一部分。如果表已經(jīng)存在，可以添加 UNIQUE 約束（假設(shè)組成 UNIQUE 約束的列或列組合僅包含唯一的值）。一個(gè)表可含有多個(gè) UNIQUE 約束。 若要修改 UNIQUE 約束，必須首先刪除現(xiàn)有的 UNIQUE 約束，然后用新定義重新創(chuàng)建。 默認(rèn)情況下，向表中的現(xiàn)有列添加 UNIQUE 約束后，SQL Server 2005 Database Engine 將檢查列中的現(xiàn)有數(shù)據(jù)，以確保所有值都是唯一的。如果向含有重復(fù)值的列添加

16、UNIQUE 約束，數(shù)據(jù)庫(kù)引擎 將返回錯(cuò)誤消息，并且不添加約束。 除非顯式指定了聚集索引，否則，默認(rèn)情況下將創(chuàng)建唯一的非聚集索引以強(qiáng)制執(zhí)行 UNIQUE 約束。 唯一性約束-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200520創(chuàng)建表時(shí)創(chuàng)建UNIQUE約束CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL UNIQUE NONCLUSTERED, DeptId char(2) NOT NULL REFERENCES Tb_D

17、ept)-CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL REFERENCES Tb_Dept, CONSTRAINT UK_StuName UNIQUE (StuName)-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200521創(chuàng)建表結(jié)束后添加、刪除UNIQUE約束CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY,

18、StuName char(8) NOT NULL, DeptId char(2) NOT NULL REFERENCES Tb_Dept)-添加UNIQUE約束ALTER TABLE Tb_StudentADD CONSTRAINT UK_DepName UNIQUE (DeptName)-刪除UNIQUE約束ALTER TABLE Tb_Student DROP CONSTRAINT UK_DepName-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200522SSMS創(chuàng)建惟一約束 右擊表名稱(chēng)右擊表名稱(chēng)，選擇快捷菜單中的“設(shè)計(jì)

19、設(shè)計(jì)”，進(jìn)入表結(jié)構(gòu)修改狀態(tài)。單擊工具欄處工具欄處“管理索引和鍵管理索引和鍵”按鈕按鈕，進(jìn)入惟一約束設(shè)置界面，單擊添加按鈕，定義類(lèi)型為“唯一鍵”選定惟一約束的列，為惟一約束定義標(biāo)識(shí)名，完成后關(guān)閉窗體。 -江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200523默認(rèn)值約束 如果插入行時(shí)沒(méi)有為列指定值，默認(rèn)值則指定列中使用什么值。默認(rèn)值可以是計(jì)算結(jié)果為常量的任何值，例如常量、內(nèi)置函數(shù)或數(shù)學(xué)表達(dá)式。 若要應(yīng)用默認(rèn)值，可以通過(guò)在 CREATE TABLE 中使用 DEFAULT 關(guān)鍵字來(lái)創(chuàng)建默認(rèn)值定義。這將為每一列分配一個(gè)常量表達(dá)式作為默

20、認(rèn)值。 默認(rèn)值必須與要應(yīng)用 DEFAULT 定義的列的數(shù)據(jù)類(lèi)型相配。例如，int 列的默認(rèn)值必須是整數(shù)，而不能是字符串。 后續(xù)版本的 Microsoft SQL Server 將刪除該功能。請(qǐng)避免在新的開(kāi)發(fā)工作中使用該功能，并應(yīng)著手修改當(dāng)前還在使用該功能的應(yīng)用程序。 -江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200524CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL R

21、EFERENCES Tb_Dept, Sex char(2) NOT NULL DEFAULT M, BIRTHDAY SMALLDATETIME NOT NULL DEFAULT getdate()添加和刪除默認(rèn)值約束-ALTER TABLE Tb_StudentADD CONSTRAINT DEF_Sex DEFAULT M FOR Sex-ALTER TABLE Tb_Student DROP CONSTRAINT DEF_Sex默認(rèn)值約束-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200525SSMS中設(shè)置默認(rèn)值約束-

22、江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200526CHECK約束 通過(guò)限制列可接受的值，CHECK 約束可以強(qiáng)制域的完整性。此類(lèi)約束類(lèi)似于 FOREIGN KEY 約束，因?yàn)榭梢钥刂品湃肓兄械闹?。但是，它們?cè)诖_定有效值的方式上有所不同：FOREIGN KEY 約束從其他表獲得有效值列表，而 CHECK 約束通過(guò)不基于其他列中的數(shù)據(jù)的邏輯表達(dá)式確定有效值。例如，可以通過(guò)創(chuàng)建 CHECK 約束將 成績(jī)列中值的范圍限制為從 0 到 100 之間的數(shù)據(jù)。這將防止輸入的成績(jī)值超出正常的成績(jī)范圍。 可以通過(guò)任何基于邏輯運(yùn)算符返回 TR

23、UE 或 FALSE 的邏輯（布爾）表達(dá)式創(chuàng)建 CHECK 約束。對(duì)于上面的示例，邏輯表達(dá)式為：grade= 0 AND grade = 0 and CourseScore = 100) CHECK (EmpId LIKE A-ZA-ZA-Z1-90-90-90-90-9FM OR EmpId LIKE A-Z-A-Z1-90-90-90-90-9FM) CHECK (TelNo IN (86022679, 86022235, 86022879, 86022886, 86028225) OR TelNo LIKE 8602210-90-9) CHECK (Salary BETWEEN 3000

24、 AND 10000) CHECK (IS_MANAGER = 1 AND Sex = F ) CHECK (CASE WHEN Is_Manager 1 AND Sex = F THEN 1 ELSE 0 END = 0)-江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200530添加和刪除CHECK約束 添加CHECK約束 ALTER TABLE Tb_Student WITH NOCHECK ADD CONSTRAINT CK_Sex CHECK (Sex IN (M,F) ; 刪除CHECK約束 ALTER TABLE Tb_Student DROP CONSTRAINT CK_Sex -江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)系-2022-5-24Microsoft SQL Server 200531使用SSMS創(chuàng)建CHECK約束 1、在數(shù)據(jù)庫(kù)關(guān)系圖數(shù)據(jù)庫(kù)關(guān)系圖中，右鍵單擊將包含該約束的表，再?gòu)目旖莶藛沃羞x擇“CHECK 約束”。 - 或?qū)τ趯摷s束的表，打開(kāi)表設(shè)計(jì)器，在表設(shè)計(jì)器中右鍵單擊，再?gòu)目旖莶藛沃羞x擇“CHECK 約束”。 2、單擊“添加添加”。 注意：注意： 如果希望為約束指定一個(gè)不同的