第單元數(shù)據(jù)完整性優(yōu)秀文檔

SQLSERVER2008中的數(shù)據(jù)庫完整性一、教學(xué)回顧1、回顧數(shù)據(jù)表中數(shù)據(jù)的增加、刪除、修改操作，教師演示。2、讓學(xué)生演示。二、引入新課1、要求教師演示過程中輸入數(shù)據(jù)比如年齡輸入500，性別輸入非男女字符，從而引出數(shù)據(jù)完整性。2、展示幻燈片數(shù)據(jù)表中還有哪些數(shù)據(jù)存在數(shù)據(jù)的不完整。本單元內(nèi)容：一、空值約束二、默認(rèn)約束三、檢查約束四、主鍵約束五、外鍵約束六、唯一約束七、設(shè)計約束實施數(shù)據(jù)完整性Usewebshop商品數(shù)量：g_numbersmallintc_gender='男'orc_gender='女'2、總結(jié)完讓學(xué)生提問時間2分鐘。g_namevarchar(50),任務(wù)14：創(chuàng)建custumers1表，各列如下所示：——會員信譽等級為90以上，才能1、課本P43頁任務(wù)16：在修改表階段，對goods表中的數(shù)量t_id創(chuàng)建默認(rèn)約束Usewebshope_postcodechar(6),商品名稱：g_namevarchar(50)Usewebshopc_emaillike'%@%'任務(wù)10：創(chuàng)建webshop數(shù)據(jù)庫中g(shù)oods表時，為g_price創(chuàng)建非空約束（單列）(u_idvarchar(10),數(shù)據(jù)表存在的問題——數(shù)據(jù)完整性2、網(wǎng)狀模型請看下面作者表中存儲的信息，能否發(fā)現(xiàn)不合理的數(shù)據(jù)？提問侯俊杰的年齡不可靠年齡為620歲，年齡數(shù)據(jù)不準(zhǔn)確。電子郵件地址不合法數(shù)據(jù)表中存在不正確、不準(zhǔn)確的數(shù)據(jù)，數(shù)據(jù)庫“失去了完整性”數(shù)據(jù)表存在的問題——數(shù)據(jù)完整性數(shù)據(jù)完整性是指數(shù)據(jù)的準(zhǔn)確性和一致性，為防止數(shù)據(jù)庫中存在不符合語義規(guī)定的數(shù)據(jù)和防止因錯誤信息的輸入輸出造成無效操作而提出的。什么是數(shù)據(jù)完整性?數(shù)據(jù)完整性的問題大多是由于設(shè)計引起的，為了避免上述問題的發(fā)生我們應(yīng)重視表的前期設(shè)計。數(shù)據(jù)完整性數(shù)據(jù)表中是否存在重復(fù)數(shù)據(jù)？——作者表中一個作者只能出現(xiàn)一次。——圖書表中每本圖書只能描述一次。是否符合特定規(guī)則的要求?！獣T信譽等級為90以上，才能成為版主。

對表中整行的要求自定義規(guī)則數(shù)據(jù)是否在允許的范圍內(nèi)？——性別信息只能是“男”或“女”。數(shù)據(jù)類型是否正確？——生日必須是日期類型。輸入的格式是否正確？——電子郵件中應(yīng)包含“@”。對表的列信息要求數(shù)據(jù)完整性分類數(shù)據(jù)完整性主要分為4類：實體完整性、域完整性、引用完整性和用戶定義完整性。實體完整性：規(guī)定表的每一行在表中是唯一的。比如唯一約束、主鍵約束。域完整性：數(shù)據(jù)表中的列必須滿足某種特定的數(shù)據(jù)類型或者約束，比如檢查約束、默認(rèn)約束、非空等。引用完整性：指兩個表中的主關(guān)鍵字和外關(guān)鍵字的數(shù)據(jù)對應(yīng)一致。它確保了有主關(guān)鍵字的表中對應(yīng)其他表的外關(guān)鍵字的行的存在。即保證了表之間的數(shù)據(jù)的一致性，防止了數(shù)據(jù)丟失或無意義的數(shù)據(jù)在數(shù)據(jù)庫中擴(kuò)散。用戶定義完整性：如客戶等級數(shù)據(jù)完整性—實體完整性實體完整性：要求表中的每一行數(shù)據(jù)都反映不同的實體，不能存在重復(fù)的數(shù)據(jù)行。49崔西539汪中求448郎咸平351吳俊杰262譚浩強(qiáng)1….年齡作者姓名作者編號51吳俊杰2×約束方法：主鍵約束、標(biāo)識列、唯一約束（1）主鍵約束表通常具有包含唯一標(biāo)識表中每一行的值的一列或者多列，這樣的一列或者多列稱為表的主鍵，用于強(qiáng)制表的實體完整性。主鍵約束通過創(chuàng)建唯一索引保證指定列的實體完整性，使用主鍵約束時，該列不能為空。主鍵約束可以應(yīng)用于表中的一列或多列。1、使用管理控制臺實現(xiàn)主鍵約束2、使用T-SQL語句實現(xiàn)主鍵約束一、主鍵約束1、使用管理控制臺實現(xiàn)主鍵約束任務(wù)1：對單列將會員信息表emplyee中的e_id設(shè)置為主鍵約束如果表中一列不能確定一個實體，需要幾列的組合才能確定，那么這幾列可以聯(lián)合作為主鍵，稱為“聯(lián)合主鍵”。任務(wù)2：對多列將users表中的u_id和u_name設(shè)置為設(shè)置為主鍵一、主鍵約束2、使用T-SQL語句實現(xiàn)主鍵約束任務(wù)3：對單列，將支付信息表payments的p_id設(shè)置為主鍵約束創(chuàng)建時：UsewebshopGocreatetablepayments1(p_idchar(2)primarykey,P_modevarchar(20),P_remarkvarchar(100))修改表UsewebshopGoAltertablepayments1addConstraintp_idyshprimarykey(p_id)任務(wù)4：多列組合：將users表中的u_id和u_name設(shè)置為設(shè)置為主鍵（在建表時和創(chuàng)建表完成時）UsewebshopGocreatetableusers1(u_idvarchar(10),u_namevarchar(30),u_typevarchar(10)Constraintuyshprimarykey(u_id,u_name))或者UsewebshopGoaltertableusers1addConstraintuyshprimarykey(u_id,u_name)（1）主鍵約束設(shè)置AuthorID為主鍵后，再錄入重復(fù)信息將給予提示。3、實訓(xùn)任務(wù)UsewebshopDefault約束是指表中添加新行時給表中某一列指定的默認(rèn)值。3、引用完整性：用來保持表之間的關(guān)系，確保插入到表中數(shù)據(jù)是有效的。altertableemployees4數(shù)據(jù)表存在的問題——數(shù)據(jù)完整性g_namevarchar(50),客戶編號：c_idchar(5)Usewebshop如果表中一列不能確定一個實體，需要幾列的組合才能確定，那么這幾列可以聯(lián)合作為主鍵，稱為“聯(lián)合主鍵”。(2)在修改表時創(chuàng)建約束數(shù)據(jù)完整性的問題大多是由于設(shè)計引起的，為了避免上述問題的發(fā)生我們應(yīng)重視表的前期設(shè)計。不能更改主表中的數(shù)據(jù)，而導(dǎo)致子表中數(shù)據(jù)的孤立。選擇主鍵的原則最少性盡量選擇單個鍵作為主鍵。穩(wěn)定性盡量選擇數(shù)值更新少的列作為主鍵。如果表中一列不能確定一個實體，需要幾列的組合才能確定，那么這幾列可以聯(lián)合作為主鍵，稱為“聯(lián)合主鍵”。數(shù)據(jù)完整性—域完整性域完整性：限制列信息的有效性。約束方法：限制數(shù)據(jù)類型、檢查約束、外鍵約束、默認(rèn)值、非空約束40程杰6cx@163.com49崔西5wzq@39汪中求4lxp@163.com48郎咸平3wjj@163.com51吳俊杰2thq@163.com62譚浩強(qiáng)1電子郵件年齡作者姓名作者編號×（2）檢查約束Check約束限制輸入到一列或多列中的可能值，從而保證數(shù)據(jù)庫中數(shù)據(jù)的域完整性一個數(shù)據(jù)表可以定義多個check約束1、使用管理控制臺實現(xiàn)check約束任務(wù)5：會員信息表customers設(shè)置以下check約束會員性別只能輸入男或者女，默認(rèn)為男。c_gender='男'orc_gender='女'（2）檢查約束2、使用T-SQL實現(xiàn)check約束格式：Constraint約束名check表達(dá)式任務(wù)6：goods表中商品價格在1000到2000之間。usewebshopgoaltertablegoods2withnocheckaddconstraintpricecheck(g_price>=1000andg_price<=2000)任務(wù)7：身份證號只能為15位或者18位(對customers表中card_id)進(jìn)行設(shè)置。

usewebshopgoaltertablecustomerswithnocheckAddconstraintsfzcheck(len(c_cardid)=15orlen(c_cardid)=18)任務(wù)8：電子郵箱中必包含@符號c_emaillike'%@%'檢查約束設(shè)置檢查約束，以避免作者表中年齡數(shù)據(jù)在不合理的范圍。（3）非空約束如果表中某些字段為必填項，可設(shè)置非空約束。（3）空值約束列的為空性決定表中的行是否可為該列包含空值，空值約束不同于零或長度為零的字符串，NULL的意思是沒有輸入，出現(xiàn)NULL表示值未知或未定義。如果設(shè)置，則輸入數(shù)據(jù)時，該列不允許為空。1、使用管理控制臺創(chuàng)建非空約束c_emaillike'%@%'任務(wù)9：

將會員信息表customers的各列均設(shè)置為notnull2、使用T-SQL語句創(chuàng)建非空約束任務(wù)10：

創(chuàng)建webshop數(shù)據(jù)庫中g(shù)oods表時，為g_price創(chuàng)建非空約束（單列）UsewebshopGoaltertablegoodsaltercolumng_pricefloatnotnull任務(wù)11：員工信息表employees中的各列均設(shè)置為notnull方法：員工信息表employees利用T-SQL語句實現(xiàn)（語句如下）UsewebshopGoaltertableemployeesaltercolumne_idchar(10)notnullaltertableemployeesaltercolumne_namevarchar(30)notnullaltertableemployeesaltercolumne_genderchar(2)notnullaltertableemployeesaltercolumne_birthdatetimenotnullaltertableemployeesaltercolumne_addressvarchar(100)notnullaltertableemployeesaltercolumne_postcodechar(6)notnullaltertableemployeesaltercolumne_mobilevarchar(11)notnullaltertableemployeesaltercolumne_phonevarchar(15)notnullaltertableemployeesaltercolumne_e-mailvarchar(50)notnull（4）默認(rèn)約束（4）默認(rèn)約束Default約束是指表中添加新行時給表中某一列指定的默認(rèn)值。使用default定義，一是可以避免notnull值的數(shù)據(jù)錯誤，二是可以加快用戶的輸入速度1、使用管理控制臺實現(xiàn)default約束任務(wù)12：

對custumers數(shù)據(jù)表的性別列指定默認(rèn)約束“男”任務(wù)13：對goods表中的g_discount列設(shè)置為（4）默認(rèn)約束2、使用T-SQL語句實現(xiàn)default約束格式：Constraint約束名default默認(rèn)值任務(wù)14：在webshop數(shù)據(jù)庫中創(chuàng)建custumers1表，各列如下所示：在創(chuàng)建表時創(chuàng)建約束。客戶編號：c_idchar(5)客戶名稱：c_namevarchar(30)客戶性別：c_genderchar(2)客戶類別：c_typevarchar(10)

客戶提示信息：c_anwservarchar(50)數(shù)據(jù)表中用戶類別（c_type）列默認(rèn)為“普通用戶”2、課外拓展：查閱資料，比較sqlserver2008與oracle11數(shù)據(jù)庫的異同。客戶編號：c_idchar(5)altertableemployeesaltercolumne_addressvarchar(100)notnull2、使用T-SQL語句實現(xiàn)主鍵約束u_namevarchar(30),客戶編號：c_idchar(5)任務(wù)21：為員工表employees4中的支付模式（e_name）創(chuàng)建unique約束addConstraintp_idyshprimarykey(p_id)如果表中一列不能確定一個實體，需要幾列的組合才能確定，那么這幾列可以聯(lián)合作為主鍵，稱為“聯(lián)合主鍵”。addConstrainteddrunique(e_address)不能將主表中關(guān)聯(lián)列不存在的數(shù)據(jù)，插入到子表中。——如果刪除了作者表中作者信息，那么圖書表中對應(yīng)的作者信息也應(yīng)隨之刪除。Altertablepayments13、實訓(xùn)任務(wù)任務(wù)10：創(chuàng)建webshop數(shù)據(jù)庫中g(shù)oods表時，為g_price創(chuàng)建非空約束（單列）Constraintuyshprimarykey(u_id,u_name)外鍵約束為表中一列或者多列數(shù)據(jù)提供引用完整性，它限制插入到表中被約束列的值必須在被引用表中已經(jīng)存在。（4）默認(rèn)約束任務(wù)14：創(chuàng)建custumers1表，各列如下所示：usewebshopgocreatetablecustomers1(c_idchar(5),c_namevarchar(30),c_genderchar(2),c_typevarchar(10)constraintc_typemrdefault'普通用戶',c_answervarchar(50))（4）默認(rèn)約束任務(wù)15：創(chuàng)建表時創(chuàng)建約束，省略Constraint：對goods1表中的日期創(chuàng)建默認(rèn)約束,goods1表結(jié)構(gòu)如下：商品編號

：g_idchar(6),商品名稱：g_namevarchar(50)商品類別：t_idchar(2)商品價格：g_pricefloat(6)商品數(shù)量：g_numbersmallint商品生產(chǎn)日期：g_producedatedatetime任務(wù)15：省略Constraint：對goods表中的日期創(chuàng)建默認(rèn)約束UsewebshopGocreatetablegoods1(g_idchar(6),g_namevarchar(50),t_idchar(2),g_pricefloat(6),g_numbersmallint,g_producedatedatetimedefault'2007-9-8')任務(wù)16：在修改表階段，對goods表中的數(shù)量t_id創(chuàng)建默認(rèn)約束UsewebshopGoaltertablegoods6addconstraintttdefault'50'fort_id數(shù)據(jù)完整性—引用完整性3、引用完整性：用來保持表之間的關(guān)系，確保插入到表中數(shù)據(jù)是有效的。

圖書表67.003誰都逃不..17892228.003金融超限..15786230.005數(shù)據(jù)結(jié)構(gòu)..16890145.506大話設(shè)計..16521240.501C程序設(shè)..169876價格作者圖書名稱圖書編號杜曉月5011100015李小旭1001689014

高好11689013紀(jì)曉嵐501789222李云龍51652121

和珅101698761訂購人訂購數(shù)量圖書編號訂單編號訂單表約束方法：外鍵約束×（5）外鍵約束如何保證向圖書表中錄入的作者信息是有效的？提問分析向圖書表中增加外鍵約束，使圖書表中AuthorID字段與作者表AuthorID建立引用關(guān)系，可確保向圖書表中插入的作者信息是有效的。建立關(guān)系圖主表子表引用關(guān)系規(guī)則不能將主表中關(guān)聯(lián)列不存在的數(shù)據(jù)，插入到子表中。

——圖書表中不能夠出現(xiàn)作者信息表中不存在的作者編號。不能更改主表中的數(shù)據(jù)，而導(dǎo)致子表中數(shù)據(jù)的孤立。

——如果作者表中的作者編號改變了，圖書表中作者編號也應(yīng)隨之改變。不能刪除主表中的數(shù)據(jù)，而導(dǎo)致子表中數(shù)據(jù)的孤立

——如果刪除了作者表中作者信息，那么圖書表中對應(yīng)的作者信息也應(yīng)隨之刪除。刪除主表前，先刪子表

——先刪圖書表、再刪除作者表。（5）外鍵約束

外鍵約束為表中一列或者多列數(shù)據(jù)提供引用完整性，它限制插入到表中被約束列的值必須在被引用表中已經(jīng)存在。實時外鍵約束時，要求在被引用表中定義了主鍵約束或者唯一約束1、使用管理控制臺創(chuàng)建外鍵約束任務(wù)17：創(chuàng)建訂單信息表orders表，將其中的o_id設(shè)置為主鍵約束，e_id設(shè)置為外鍵約束（主鍵表為employees）,p_id設(shè)置為外鍵約束（主鍵表為payments）.2、使用T-SQL語句創(chuàng)建外鍵約束任務(wù)18：創(chuàng)建訂單信息表orders表，將其中的o_id設(shè)置為主鍵約束，e_id設(shè)置為外鍵約束（主鍵表為employees）,p_id設(shè)置為外鍵約束（主鍵表為payments）(1)使用T-SQL語句創(chuàng)建外鍵約束(在創(chuàng)建表時創(chuàng)建約束)任務(wù)19：創(chuàng)建訂單信息表orders2表，將其中的o_id設(shè)置為主鍵約束，e_id設(shè)置為外鍵約束（主鍵表為employees）,p_id設(shè)置為外鍵約束（主鍵表為payments）UsewebshopGoCreatetableorders2(o_idchar(10)primarykey,C_idchar(5),O_datedatetime,e_idchar(10),P_idchar(2)constraintorder_employforeignkey(e_id)referencesemployees(e_id),constraintorder_paymentsforeignkey(p_id)referencespayments(p_id))（2）在修改表時添加外鍵約束UsewebshopGoaltertableorders2addconstraintorder_employforeignkey(e_id)referencesemployees(e_id),constraintorder_paymentsforeignkey(p_id)referencespayments(p_id)（3）刪除約束UsewebshopGoaltertableorders2Droporder_employ,order_payments（6）唯一約束Unique約束通過確保在列中不輸入重復(fù)值保證一列或者多列的實體完整性，每個unique約束要創(chuàng)建一個唯一索引。對于實施unique約束的列，不允許任意兩行具有相同的索引值。與primary約束不同的是，允許為一個表創(chuàng)建多個唯一約束。1、使用管理控制臺創(chuàng)建唯一約束快捷菜單-索引/鍵-添加-列-是唯一的（是）任務(wù)20:為employees中的e_name創(chuàng)建唯一約束2、