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

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

主鍵約束使用數(shù)據(jù)表中的一列數(shù)據(jù)或者多列數(shù)據(jù)來唯一的標(biāo)識一行數(shù)據(jù)。也就是說，在數(shù)據(jù)表中不能存在主鍵相同的兩行數(shù)據(jù)，而且主鍵約束下的列不能為空（NULL），在創(chuàng)建表時，最好每個數(shù)據(jù)表都擁有自己唯一的主鍵，主鍵也可以由幾個列組合成。每個表只能有一個主鍵。不能定義TEXT或IMAGE數(shù)據(jù)類型的字段列為主鍵。2023/2/3MicrosoftSQLServer20053SSMS創(chuàng)建主鍵的步驟①在創(chuàng)建表結(jié)構(gòu)的時候，選定列名或列名組合（shift鍵）,在所選列上右擊彈出相應(yīng)菜單，如右圖所示。②單擊有鑰匙圖標(biāo)的菜單項。③創(chuàng)建成功后該列名前出現(xiàn)一個小鑰匙形狀的圖標(biāo)如右圖所示。2023/2/3MicrosoftSQLServer20054創(chuàng)建表時定義主鍵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)建一個無主鍵的表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)系，主要用來維護兩個表之間的一致性。出于某種關(guān)聯(lián)的需要，當(dāng)一個表需要引用另一個表的主鍵作為自己的一個字段時，我們稱這個引用來的字段為外鍵。這樣，當(dāng)主鍵更新或刪除時，其它所有表中與這個主鍵關(guān)聯(lián)的外鍵也將被相應(yīng)的更新或刪除。當(dāng)向外鍵所在的表插入或更新數(shù)據(jù)（外鍵字段）時，如果與外鍵表相關(guān)聯(lián)的主鍵表的主鍵中無與插入或更新的外鍵有相同的值時，系統(tǒng)會報錯并拒絕插入或更新數(shù)據(jù)。不能定義TEXT或IMAGE數(shù)據(jù)類型的列為外鍵。2023/2/3MicrosoftSQLServer20057外鍵約束2023/2/3MicrosoftSQLServer20058外鍵約束2023/2/3MicrosoftSQLServer20059在創(chuàng)建表時定義外鍵約束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)建外鍵約束的語句可以簡化為如下形式：------------------------------------------------------------------------CREATETABLETb_Student(StuIdchar(10)PRIMARYKEY,StuNamechar(8)NOTNULL,DeptIdchar(2)

NOTNULL

REFERENCESTb_Dept)------------------------------------------------------------------------從添加外鍵約束時只需添加REFERENCES關(guān)鍵字和被引用的表名。這里需要注意的是，外鍵列和被引用表中的主鍵列應(yīng)該名稱相同，且具有相同的列數(shù)據(jù)類型。在創(chuàng)建表時定義外鍵約束2023/2/3MicrosoftSQLServer200511級聯(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)建一個無外鍵的表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ù)據(jù)時檢查（外鍵）2023/2/3MicrosoftSQLServer200517參照完整性（外鍵約束）參照完整性是指兩個表的主關(guān)鍵字（PRIMARYKEY）和外關(guān)鍵字（FOREIGNKEY）的數(shù)據(jù)要對應(yīng)一致。它確保了有主關(guān)鍵字的表中對應(yīng)其它表的外關(guān)鍵字的行存在，即保證了表之間的數(shù)據(jù)的一致性，防止了數(shù)據(jù)丟失或無意義的數(shù)據(jù)在數(shù)據(jù)庫中擴散。參照完整性是建立在外關(guān)鍵字和主關(guān)鍵字之上的。這樣的一致性要求不能引用不存在的值，如果鍵值更改了，那么在整個數(shù)據(jù)庫中，對該鍵值的所有引用要進行一致的更改。在被參照表（主表）中，當(dāng)其主鍵值被其他表所參照時，該行不能被刪除也不允許改變。在參照表（從表）中，不允許參照不存在的主鍵值。2023/2/3MicrosoftSQLServer200518唯一性（UNIQUE）約束可以使用UNIQUE約束確保在非主鍵列中不輸入重復(fù)的值。盡管UNIQUE約束和PRIMARYKEY約束都強制唯一性，但想要強制一列或多列組合（不是主鍵）的唯一性時應(yīng)使用UNIQUE約束而不是PRIMARYKEY約束。可以對一個表定義多個UNIQUE約束，但只能定義一個PRIMARYKEY約束。而且，UNIQUE約束允許NULL值，這一點與PRIMARYKEY約束不同。不過，當(dāng)與參與UNIQUE約束的任何值一起使用時，每列只允許一個空值。FOREIGNKEY約束可以引用UNIQUE約束。2023/2/3MicrosoftSQLServer200519創(chuàng)建表時，可以創(chuàng)建UNIQUE約束作為表定義的一部分。如果表已經(jīng)存在，可以添加UNIQUE約束（假設(shè)組成UNIQUE約束的列或列組合僅包含唯一的值）。一個表可含有多個UNIQUE約束。若要修改UNIQUE約束，必須首先刪除現(xiàn)有的UNIQUE約束，然后用新定義重新創(chuàng)建。默認(rèn)情況下，向表中的現(xiàn)有列添加UNIQUE約束后，SQLServer2005DatabaseEngine

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

將返回錯誤消息，并且不添加約束。除非顯式指定了聚集索引，否則，默認(rèn)情況下將創(chuàng)建唯一的非聚集索引以強制執(zhí)行UNIQUE約束。唯一性約束2023/2/3MicrosoftSQLServer200520創(chuàng)建表時創(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)建惟一約束右擊表名稱，選擇快捷菜單中的“設(shè)計”，進入表結(jié)構(gòu)修改狀態(tài)。單擊工具欄處“管理索引和鍵”按鈕，進入惟一約束設(shè)置界面，單擊添加按鈕，定義類型為“唯一鍵”選定惟一約束的列，為惟一約束定義標(biāo)識名，完成后關(guān)閉窗體。2023/2/3MicrosoftSQLServer200523默認(rèn)值約束如果插入行時沒有為列指定值，默認(rèn)值則指定列中使用什么值。默認(rèn)值可以是計算結(jié)果為常量的任何值，例如常量、內(nèi)置函數(shù)或數(shù)學(xué)表達式。若要應(yīng)用默認(rèn)值，可以通過在CREATETABLE中使用DEFAULT關(guān)鍵字來創(chuàng)建默認(rèn)值定義。這將為每一列分配一個常量表達式作為默認(rèn)值。默認(rèn)值必須與要應(yīng)用DEFAULT定義的列的數(shù)據(jù)類型相配。例如，int列的默認(rèn)值必須是整數(shù)，而不能是字符串。后續(xù)版本的MicrosoftSQLServer將刪除該功能。請避免在新的開發(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約束通過限制列可接受的值，CHECK約束可以強制域的完整性。此類約束類似于FOREIGNKEY約束，因為可以控制放入列中的值。但是，它們在確定有效值的方式上有所不同：FOREIGNKEY約束從其他表獲得有效值列表，而CHECK約束通過不基于其他列中的數(shù)據(jù)的邏輯表達式確定有效值。例如，可以通過創(chuàng)建CHECK約束將成績列中值的范圍限制為從0到100之間的數(shù)據(jù)。這將防止輸入的成績值超出正常的成績范圍?？梢酝ㄟ^任何基于邏輯運算符返回TRUE或FALSE的邏輯（布爾）表達式創(chuàng)建CHECK約束。對于上面的示例，邏輯表達式為：grade>=0ANDgrade<=100?？梢詫⒍鄠€CHECK約束應(yīng)用于單個列。還可以通過在表級創(chuàng)建CHECK約束，將一個CHECK約束應(yīng)用于多個列。2023/2/3MicrosoftSQLServer200527創(chuàng)建表時，可以創(chuàng)建CHECK約束作為表定義的一部分。如果表已經(jīng)存在，則可以添加CHECK約束。表和列可以包含多個CHECK約束。如果CHECK約束已經(jīng)存在，則可以修改或刪除該約束。例如，可能需要修改表中某列的CHECK約束使用的表達式。

注意：必須首先刪除現(xiàn)有的CHECK約束，然后使用新定義重新創(chuàng)建，才能修改CHECK約束。向現(xiàn)有表中添加CHECK約束后，CHECK約束可以僅應(yīng)用于新數(shù)據(jù)，也可以應(yīng)用于現(xiàn)有數(shù)據(jù)。默認(rèn)情況下，CHECK約束同時應(yīng)用于現(xiàn)有數(shù)據(jù)和所有新數(shù)據(jù)。使用ALTERTABLE語句的WITHNOCHECK選項可以將新約束僅應(yīng)用于新添加的數(shù)據(jù)。如果現(xiàn)有數(shù)據(jù)已符合新的CHECK約束時，或業(yè)務(wù)規(guī)則要求僅從此開始強制約束時，則可以使用此選項。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)建表時創(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