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

2023/2/3MicrosoftSQLServer20051數(shù)據(jù)完整性在SQLServer2005中，根據(jù)數(shù)據(jù)完整措施所作用的數(shù)據(jù)庫(kù)對(duì)象和范圍不同，可以將數(shù)據(jù)完整性分為以下幾種：1、實(shí)體完整性（主鍵、唯一性約束）2、參照完整性（外鍵約束）3、域完整性（check、默認(rèn)值約束）4、用戶(hù)定義的完整性2023/2/3MicrosoftSQLServer20052主鍵(PRIMARYKEY)約束

主鍵約束使用數(shù)據(jù)表中的一列數(shù)據(jù)或者多列數(shù)據(jù)來(lái)唯一的標(biāo)識(shí)一行數(shù)據(jù)。也就是說(shuō)，在數(shù)據(jù)表中不能存在主鍵相同的兩行數(shù)據(jù)，而且主鍵約束下的列不能為空（NULL），在創(chuàng)建表時(shí)，最好每個(gè)數(shù)據(jù)表都擁有自己唯一的主鍵，主鍵也可以由幾個(gè)列組合成。每個(gè)表只能有一個(gè)主鍵。不能定義TEXT或IMAGE數(shù)據(jù)類(lèi)型的字段列為主鍵。2023/2/3MicrosoftSQLServer20053SSMS創(chuàng)建主鍵的步驟①在創(chuàng)建表結(jié)構(gòu)的時(shí)候，選定列名或列名組合（shift鍵）,在所選列上右擊彈出相應(yīng)菜單，如右圖所示。②單擊有鑰匙圖標(biāo)的菜單項(xiàng)。③創(chuàng)建成功后該列名前出現(xiàn)一個(gè)小鑰匙形狀的圖標(biāo)如右圖所示。2023/2/3MicrosoftSQLServer20054創(chuàng)建表時(shí)定義主鍵CREATETABLETb_Class(ClassIdchar(8)PRIMARYKEY,ClassNamevarchar(30)NOTNULL,DeptIdchar(2)NOTNULL,ClassStuNumberint)------------------------------------------------------------------------CREATETABLETb_Class(ClassIdchar(8)NOTNULL,ClassNamevarchar(30)NOTNULL,DeptIdchar(2)NOTNULL,ClassStuNumberint,CONSTRAINTPK_ClassIdPRIMARYKEY(ClassId,ClassName))2023/2/3MicrosoftSQLServer20055向已有表中添加主鍵

先創(chuàng)建一個(gè)無(wú)主鍵的表CREATETABLETb_Class(ClassIdchar(8)NOTNULL,ClassNamevarchar(30)NOTNULL,DeptIdchar(2)NOTNULL,ClassStuNumberint)

再給相應(yīng)的列（ClassId）添加主鍵約束ALTERTABLETb_ClassADDCONSTRAINTPK_ClassIdPRIMARYKEY(ClassId)--------------------------------------------------------------------------------

刪除主鍵約束ALTERTABLETb_ClassDROPCONSTRAINTPK_ClassIdPRIMARYKEY(ClassId)2023/2/3MicrosoftSQLServer20056外鍵（ForeignKey）約束

外鍵約束定義了表之間的關(guān)系，主要用來(lái)維護(hù)兩個(gè)表之間的一致性。出于某種關(guān)聯(lián)的需要，當(dāng)一個(gè)表需要引用另一個(gè)表的主鍵作為自己的一個(gè)字段時(shí)，我們稱(chēng)這個(gè)引用來(lái)的字段為外鍵。這樣，當(dāng)主鍵更新或刪除時(shí)，其它所有表中與這個(gè)主鍵關(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)型的列為外鍵。2023/2/3MicrosoftSQLServer20057外鍵約束2023/2/3MicrosoftSQLServer20058外鍵約束2023/2/3MicrosoftSQLServer20059在創(chuàng)建表時(shí)定義外鍵約束CREATETABLETb_Dept(DeptIdchar(2)PRIMARYKEY,DeptNamevarchar(16)NOTNULL)------------------------------------------------------------------------CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULL,

CONSTRAINTFK_DeptIdFOREIGNKEY(DeptId)REFERENCESTb_Dept(DeptId))2023/2/3MicrosoftSQLServer200510上述創(chuàng)建外鍵約束的語(yǔ)句可以簡(jiǎn)化為如下形式：------------------------------------------------------------------------CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)

NOTNULL

REFERENCESTb_Dept)------------------------------------------------------------------------從添加外鍵約束時(shí)只需添加REFERENCES關(guān)鍵字和被引用的表名。這里需要注意的是，外鍵列和被引用表中的主鍵列應(yīng)該名稱(chēng)相同，且具有相同的列數(shù)據(jù)類(lèi)型。在創(chuàng)建表時(shí)定義外鍵約束2023/2/3MicrosoftSQLServer200511級(jí)聯(lián)更新、刪除CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULL

REFERENCESTb_DeptONUPDATECASCADEONDELETECASCADE)------------------------------------------------------------------------CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULL,

CONSTRAINTFK_DeptIdFOREIGNKEY(DeptId)REFERENCESTb_Dept(DeptId)ONUPDATECASCADEONDELETECASCADE)2023/2/3MicrosoftSQLServer200512添加和刪除外鍵約束

先創(chuàng)建一個(gè)無(wú)外鍵的表CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULL)

再給相應(yīng)的列（DeptId）添加外鍵約束ALTERTABLETb_StudentADDCONSTRAINTFK_DeptIdFOREIGNKEY(DeptId)REFERENCESTb_Dept(DeptId)--------------------------------------------------------------------------------

刪除外鍵約束ALTERTABLETb_StudentDROPCONSTRAINTFK_DeptId2023/2/3MicrosoftSQLServer200513SSMS設(shè)置外鍵約束2023/2/3MicrosoftSQLServer200514SSMS設(shè)置外鍵約束2023/2/3MicrosoftSQLServer200515SSMS設(shè)置外鍵約束2023/2/3MicrosoftSQLServer200516外鍵約束的作用修改父表主鍵時(shí)檢查刪除父表記錄時(shí)檢查在子表中插入數(shù)據(jù)時(shí)檢查（外鍵）2023/2/3MicrosoftSQLServer200517參照完整性（外鍵約束）參照完整性是指兩個(gè)表的主關(guān)鍵字（PRIMARYKEY）和外關(guān)鍵字（FOREIGNKEY）的數(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í)，該行不能被刪除也不允許改變。在參照表（從表）中，不允許參照不存在的主鍵值。2023/2/3MicrosoftSQLServer200518唯一性（UNIQUE）約束可以使用UNIQUE約束確保在非主鍵列中不輸入重復(fù)的值。盡管UNIQUE約束和PRIMARYKEY約束都強(qiáng)制唯一性，但想要強(qiáng)制一列或多列組合（不是主鍵）的唯一性時(shí)應(yīng)使用UNIQUE約束而不是PRIMARYKEY約束?？梢詫?duì)一個(gè)表定義多個(gè)UNIQUE約束，但只能定義一個(gè)PRIMARYKEY約束。而且，UNIQUE約束允許NULL值，這一點(diǎn)與PRIMARYKEY約束不同。不過(guò)，當(dāng)與參與UNIQUE約束的任何值一起使用時(shí)，每列只允許一個(gè)空值。FOREIGNKEY約束可以引用UNIQUE約束。2023/2/3MicrosoftSQLServer200519創(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約束后，SQLServer2005DatabaseEngine

將檢查列中的現(xiàn)有數(shù)據(jù)，以確保所有值都是唯一的。如果向含有重復(fù)值的列添加UNIQUE約束，數(shù)據(jù)庫(kù)引擎

將返回錯(cuò)誤消息，并且不添加約束。除非顯式指定了聚集索引，否則，默認(rèn)情況下將創(chuàng)建唯一的非聚集索引以強(qiáng)制執(zhí)行UNIQUE約束。唯一性約束2023/2/3MicrosoftSQLServer200520創(chuàng)建表時(shí)創(chuàng)建UNIQUE約束CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULLUNIQUENONCLUSTERED,DeptIdchar(2)NOTNULL

REFERENCESTb_Dept)--------------------------------------------------------------------------------CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULLREFERENCESTb_Dept,

CONSTRAINTUK_StuNameUNIQUE(StuName))2023/2/3MicrosoftSQLServer200521創(chuàng)建表結(jié)束后添加、刪除UNIQUE約束CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULL

REFERENCESTb_Dept)------------------------------------------------------------------------添加UNIQUE約束ALTERTABLETb_StudentADDCONSTRAINTUK_DepNameUNIQUE(DeptName)------------------------------------------------------------------------刪除UNIQUE約束ALTERTABLETb_StudentDROPCONSTRAINTUK_DepName2023/2/3MicrosoftSQLServer200522SSMS創(chuàng)建惟一約束右擊表名稱(chēng)，選擇快捷菜單中的“設(shè)計(jì)”，進(jìn)入表結(jié)構(gòu)修改狀態(tài)。單擊工具欄處“管理索引和鍵”按鈕，進(jìn)入惟一約束設(shè)置界面，單擊添加按鈕，定義類(lèi)型為“唯一鍵”選定惟一約束的列，為惟一約束定義標(biāo)識(shí)名，完成后關(guān)閉窗體。2023/2/3MicrosoftSQLServer200523默認(rèn)值約束如果插入行時(shí)沒(méi)有為列指定值，默認(rèn)值則指定列中使用什么值。默認(rèn)值可以是計(jì)算結(jié)果為常量的任何值，例如常量、內(nèi)置函數(shù)或數(shù)學(xué)表達(dá)式。若要應(yīng)用默認(rèn)值，可以通過(guò)在CREATETABLE中使用DEFAULT關(guān)鍵字來(lái)創(chuàng)建默認(rèn)值定義。這將為每一列分配一個(gè)常量表達(dá)式作為默認(rèn)值。默認(rèn)值必須與要應(yīng)用DEFAULT定義的列的數(shù)據(jù)類(lèi)型相配。例如，int列的默認(rèn)值必須是整數(shù)，而不能是字符串。后續(xù)版本的MicrosoftSQLServer將刪除該功能。請(qǐng)避免在新的開(kāi)發(fā)工作中使用該功能，并應(yīng)著手修改當(dāng)前還在使用該功能的應(yīng)用程序。2023/2/3MicrosoftSQLServer200524CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULL

REFERENCESTb_Dept,Sexchar(2)NOTNULL

DEFAULT‘M’,BIRTHDAYSMALLDATETIME

NOTNULL

DEFAULT

getdate())添加和刪除默認(rèn)值約束----------------------------------------------------ALTERTABLETb_StudentADDCONSTRAINTDEF_SexDEFAULT‘M’FORSex----------------------------------------------------------------------------------ALTERTABLETb_StudentDROPCONSTRAINTDEF_Sex默認(rèn)值約束2023/2/3MicrosoftSQLServer200525SSMS中設(shè)置默認(rèn)值約束2023/2/3MicrosoftSQLServer200526CHECK約束通過(guò)限制列可接受的值，CHECK約束可以強(qiáng)制域的完整性。此類(lèi)約束類(lèi)似于FOREIGNKEY約束，因?yàn)榭梢钥刂品湃肓兄械闹怠５?，它們?cè)诖_定有效值的方式上有所不同：FOREIGNKEY約束從其他表獲得有效值列表，而CHECK約束通過(guò)不基于其他列中的數(shù)據(jù)的邏輯表達(dá)式確定有效值。例如，可以通過(guò)創(chuàng)建CHECK約束將成績(jī)列中值的范圍限制為從0到100之間的數(shù)據(jù)。這將防止輸入的成績(jī)值超出正常的成績(jī)范圍。可以通過(guò)任何基于邏輯運(yùn)算符返回TRUE或FALSE的邏輯（布爾）表達(dá)式創(chuàng)建CHECK約束。對(duì)于上面的示例，邏輯表達(dá)式為：grade>=0ANDgrade<=100?？梢詫⒍鄠€(gè)CHECK約束應(yīng)用于單個(gè)列。還可以通過(guò)在表級(jí)創(chuàng)建CHECK約束，將一個(gè)CHECK約束應(yīng)用于多個(gè)列。2023/2/3MicrosoftSQLServer200527創(chuàng)建表時(shí)，可以創(chuàng)建CHECK約束作為表定義的一部分。如果表已經(jīng)存在，則可以添加CHECK約束。表和列可以包含多個(gè)CHECK約束。如果CHECK約束已經(jīng)存在，則可以修改或刪除該約束。例如，可能需要修改表中某列的CHECK約束使用的表達(dá)式。

注意：必須首先刪除現(xiàn)有的CHECK約束，然后使用新定義重新創(chuàng)建，才能修改CHECK約束。向現(xiàn)有表中添加CHECK約束后，CHECK約束可以?xún)H應(yīng)用于新數(shù)據(jù)，也可以應(yīng)用于現(xiàn)有數(shù)據(jù)。默認(rèn)情況下，CHECK約束同時(shí)應(yīng)用于現(xiàn)有數(shù)據(jù)和所有新數(shù)據(jù)。使用ALTERTABLE語(yǔ)句的WITHNOCHECK選項(xiàng)可以將新約束僅應(yīng)用于新添加的數(shù)據(jù)。如果現(xiàn)有數(shù)據(jù)已符合新的CHECK約束時(shí)，或業(yè)務(wù)規(guī)則要求僅從此開(kāi)始強(qiáng)制約束時(shí)，則可以使用此選項(xiàng)。CHECK約束2023/2/3MicrosoftSQLServer200528CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)NOTNULL

REFERENCESTb_Dept,Sexchar(2)NOTNULL

DEFAULT‘M’CHECK(SexIN(‘M’,’F’)),ZipCodechar(6)NOTNULL

CHECK(ZipCodelike‘[0-9][0-9][0-9][0-9][0-9][0-9]’),CONSTRAINTCK_StuIdCHECK(StuIdlike‘S[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’))創(chuàng)建表時(shí)創(chuàng)建CHECK約束

2023/2/3MicrosoftSQLServer200529其它例子CHECK(CourseScore>=0andCourseScore<=100)CHECK(EmpIdLIKE'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'OREmpIdLIKE'[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')CHECK(TelNoIN('86022679','86022235','86022879','86022886','86028225')ORTelNoLIKE'860221[0-9][0-9]')CHECK(SalaryBETWEEN3000AND10000)CHECK(IS_MANAGER=1ANDSex='F')CHECK(CASEWHENIs_Manager<>1ANDSex='F'THEN1ELSE0END=0)

2023/2/3MicrosoftSQLServer200530添加和刪除CHECK約束

添加CHECK約束ALTERTABLETb_StudentWITHNOCHECK

ADDCONSTRAINTCK_SexCHECK(SexIN('M','F'));

刪除CHECK約束ALTERTABLETb_StudentDROP

CONSTRAINTCK_Sex

2023/2/3Micr