TP05-數(shù)據(jù)庫完整性

1、數(shù)據(jù)完整性基本概念數(shù)據(jù)完整性基本概念 使用使用約約束束標(biāo)識(shí)列標(biāo)識(shí)列IDENTITY2022-3-26第五章數(shù)據(jù)庫完整性第五章數(shù)據(jù)庫完整性數(shù)據(jù)完整性數(shù)據(jù)完整性 數(shù)據(jù)存放在表中 “數(shù)據(jù)完整性的問題大多是由于設(shè)計(jì)引起的” 創(chuàng)建表的時(shí)候，就應(yīng)當(dāng)保證以后數(shù)據(jù)輸入是正確的錯(cuò)誤的數(shù)據(jù)、不符合要求的數(shù)據(jù)不允許輸入2022-3-26創(chuàng)建表：保證數(shù)據(jù)的完整性創(chuàng)建表：保證數(shù)據(jù)的完整性 = 實(shí)施完整性約束實(shí)施完整性約束數(shù)據(jù)完整性數(shù)據(jù)完整性 準(zhǔn)確性準(zhǔn)確性可靠性可靠性完整性內(nèi)容舉例完整性內(nèi)容舉例 輸入的類型是否正確？年齡必須是數(shù)字 輸入的格式是否正確？身份證號(hào)碼必須是18位 是否在允許的范圍內(nèi)？性別只能是“男”或者“女”

2、 是否存在重復(fù)輸入？學(xué)員信息輸入了兩次 是否符合其他特定要求？學(xué)生的出生日期必須小于入學(xué)日期 2022-3-26列值要求（約束）列值要求（約束）整行要求（約束）整行要求（約束）四種完整性約束四種完整性約束2022-3-26域完整性域完整性實(shí)體完整性實(shí)體完整性參照參照完完整性整性自定義完整性自定義完整性提供四種類型的約束保證完整性提供四種類型的約束保證完整性實(shí)體完實(shí)體完整性整性2022-3-26河南新鄉(xiāng)河南新鄉(xiāng)趙可以趙可以0010016河南新鄉(xiāng)河南新鄉(xiāng)張麗鵑張麗鵑0010015江西南昌江西南昌雷銅雷銅0010014湖南新田湖南新田吳蘭吳蘭0010013山東定陶山東定陶李山李山0010012.地址

3、地址姓名姓名學(xué)號(hào)學(xué)號(hào)江西南昌江西南昌雷銅雷銅0010014約束方法：唯一約束、主鍵約束、標(biāo)識(shí)列約束方法：唯一約束、主鍵約束、標(biāo)識(shí)列實(shí)體完整性：實(shí)體完整性：又稱行完整性，要求表中不能存在完全相同的記錄，而且每條記錄都具有一個(gè)非空且不重復(fù)的主鍵值。域完整性域完整性2022-3-26河南新鄉(xiāng)河南新鄉(xiāng)趙可以趙可以河南新鄉(xiāng)河南新鄉(xiāng)張麗鵑張麗鵑0010015江西南昌江西南昌雷銅雷銅0010014湖南新田湖南新田吳蘭吳蘭0010013山東定陶山東定陶李山李山0010012.地址地址姓名姓名學(xué)號(hào)學(xué)號(hào)湖北江門湖北江門李亮李亮8700000000約束方法：限制數(shù)據(jù)類型、檢查約束、外鍵約束、默認(rèn)值、非空約束約束方法

4、：限制數(shù)據(jù)類型、檢查約束、外鍵約束、默認(rèn)值、非空約束0010016學(xué)學(xué)號(hào)號(hào)要要求求7位位字字符符域完整性：域完整性：又稱列完整性，用以指定列的數(shù)據(jù)輸入是否具有正確的數(shù)據(jù)類型、格式以及有效的數(shù)據(jù)范圍。參照參照完完整性整性科目學(xué)號(hào)分?jǐn)?shù)數(shù)學(xué)001001288數(shù)學(xué)001001374語文001001267語文001001381數(shù)學(xué)0010016982022-3-26河南新鄉(xiāng)河南新鄉(xiāng)趙可以趙可以0010016河南新鄉(xiāng)河南新鄉(xiāng)張麗鵑張麗鵑0010015江西南昌江西南昌雷銅雷銅0010014湖南新田湖南新田吳蘭吳蘭0010013山東定陶山東定陶李山李山0010012地址地址姓名姓名學(xué)號(hào)學(xué)號(hào)980010021數(shù)

5、學(xué)數(shù)學(xué)約束方法：外鍵約束約束方法：外鍵約束參照參照完整性：完整性：又稱引用完整性，用以保證主表（被參照表）中的數(shù)據(jù)與從表（參照表）中數(shù)據(jù)的一致性。自定義完整性自定義完整性帳號(hào)姓名信用.00192孫悟空700288豬悟能612333段譽(yù)890111虛竹4093000岳不群-102022-3-26AV121322喬峰喬峰CV0016AV372133玄痛玄痛CV0015AV378291沙悟凈沙悟凈AV0014AV378290豬悟能豬悟能AV0013AV378289孫悟空孫悟空AV0012.會(huì)員證會(huì)員證用戶姓名用戶姓名用戶編號(hào)用戶編號(hào)約束方法：規(guī)則、存儲(chǔ)過程、觸發(fā)器約束方法：規(guī)則、存儲(chǔ)過程、觸發(fā)器觸發(fā)

6、器：檢查信用值觸發(fā)器：檢查信用值設(shè)置主鍵設(shè)置主鍵2022-3-26操作演示：設(shè)置主鍵操作演示：設(shè)置主鍵2022-3-26PRIMARY KEYPRIMARY KEY約束約束 該約束能確保數(shù)據(jù)的唯一性該約束能確保數(shù)據(jù)的唯一性 若若PRIMARY KEYPRIMARY KEY約束定義約束定義在多列上，則一列中的值可以在多列上，則一列中的值可以重復(fù)，但重復(fù)，但PRIMARY KEYPRIMARY KEY約束定約束定義中的所有列的組合的值必須義中的所有列的組合的值必須唯一唯一 強(qiáng)制表的實(shí)體性。不允許數(shù)據(jù)庫強(qiáng)制表的實(shí)體性。不允許數(shù)據(jù)庫表在指定列上具有相同的值，且表在指定列上具有相同的值，且不允許有空值。

7、不允許有空值。author_idtitle_idbookprice100001Prof_1Computer25100002Prof_2Nature35100001Prof_3English28100003Prof_4English312022-3-26組合主鍵組合主鍵PRIMARY KEYPRIMARY KEY約束約束 2022-3-26ALTER TABLE SubjectsALTER TABLE SubjectsADD CONSTRAINT PK_SubjectADD CONSTRAINT PK_SubjectPRIMARY KEY CLUSTEREDPRIMARY KEY CLUSTER

8、ED(PrimaryCode, SecondaryCode)(PrimaryCode, SecondaryCode)GOGO聚族索引PRIMARY KEYPRIMARY KEY約束約束 設(shè)置默認(rèn)值設(shè)置默認(rèn)值2022-3-26操作演示：設(shè)置默認(rèn)值操作演示：設(shè)置默認(rèn)值2022-3-26默認(rèn)值默認(rèn)值約束約束 若某列定義了默認(rèn)值（若某列定義了默認(rèn)值（defaultdefault）約束，用戶在輸入數(shù)）約束，用戶在輸入數(shù)據(jù)時(shí)，沒有為該列指定數(shù)據(jù)，那么系統(tǒng)將默認(rèn)值賦給據(jù)時(shí)，沒有為該列指定數(shù)據(jù)，那么系統(tǒng)將默認(rèn)值賦給該列。該列。語法：語法：constraint constraint 約束名約束名 default

9、 default 默認(rèn)值約束表達(dá)式默認(rèn)值約束表達(dá)式 for for 列名列名Eg Eg ：以創(chuàng)建表：以創(chuàng)建表readerreader，為其添加默認(rèn)約束，使，為其添加默認(rèn)約束，使“登記登記日期日期”列的默認(rèn)值為系統(tǒng)時(shí)間。列的默認(rèn)值為系統(tǒng)時(shí)間。 alter table reader alter table reader add add constraint df_djrq default getdate() constraint df_djrq default getdate() for for 登記日期登記日期創(chuàng)建檢查約束創(chuàng)建檢查約束2022-3-26操作演示：建立檢查約束操作演示：建立檢查約束

10、添加約束示例添加約束示例2-22022-3-26操作演示：查看約束結(jié)果操作演示：查看約束結(jié)果 2022-3-26用于限定輸入到一列或多列的值的范圍，從邏輯表達(dá)用于限定輸入到一列或多列的值的范圍，從邏輯表達(dá)式判斷數(shù)據(jù)的有效性。式判斷數(shù)據(jù)的有效性。語法：語法：constraint 約束名約束名 check（檢查約束表達(dá)式）（檢查約束表達(dá)式）例例：在：在表表reader上上定義檢查約束，要求定義檢查約束，要求“性別性別”列的列的輸入值只能是輸入值只能是“男男”或或“女女”。ALTER TABLE DepartmentsADD CONSTRAINT CK_sex check (性別性別=男男or性別性

11、別=女女)GO 檢查檢查約約束束 2022-3-26UNIQUEUNIQUE約束約束 該約束可確保在非主鍵列中不輸入重復(fù)值，可該約束可確保在非主鍵列中不輸入重復(fù)值，可以實(shí)現(xiàn)在一張表上定義以實(shí)現(xiàn)在一張表上定義多個(gè)多個(gè)UNIQUEUNIQUE約束，且在約束，且在定義有定義有UNIQUEUNIQUE約束的列上允許約束的列上允許有空有空值值可以對(duì)一個(gè)表定義多個(gè)可以對(duì)一個(gè)表定義多個(gè) UNIQUE UNIQUE 約束，但約束，但只能定義一個(gè)只能定義一個(gè) PRIMARY KEY PRIMARY KEY 約束約束UNIQUE UNIQUE 約束允許約束允許 NULL NULL 值，這一點(diǎn)與值，這一點(diǎn)與 PRI

12、MARY KEY PRIMARY KEY 約束不同，當(dāng)與參與約束不同，當(dāng)與參與 UNIQUE UNIQUE 約束的任何值一起使用時(shí)，每列只允許一約束的任何值一起使用時(shí)，每列只允許一個(gè)空值個(gè)空值 FOREIGN KEY FOREIGN KEY 約束可以引用約束可以引用 UNIQUE UNIQUE 約束約束2022-3-26例：在表例：在表DepartmentsDepartments上的上的TelCodeTelCode列上添加一個(gè)列上添加一個(gè)UNIQUEUNIQUE約束約束ALTER TABLE DepartmentsADD CONSTRAINT U_TelCode UNIQUE (TelCode

13、)GO UNIQUEUNIQUE約束約束 2022-3-26FOREIGN KEYFOREIGN KEY約束約束 該約束用于建立兩個(gè)表一列該約束用于建立兩個(gè)表一列或多列數(shù)據(jù)之間的鏈接的或多列數(shù)據(jù)之間的鏈接的將主鍵值將主鍵值(一列或多列一列或多列)添加到另一個(gè)表中，添加到另一個(gè)表中，可創(chuàng)建兩個(gè)表之間的可創(chuàng)建兩個(gè)表之間的鏈接，這個(gè)列就成為鏈接，這個(gè)列就成為第二個(gè)表的外鍵第二個(gè)表的外鍵該約束該約束能強(qiáng)制能強(qiáng)制引用完引用完整性整性建立主外鍵關(guān)系建立主外鍵關(guān)系2022-3-26操作演示：演示建立主外鍵關(guān)系操作演示：演示建立主外鍵關(guān)系主外鍵建立后注意事項(xiàng)主外鍵建立后注意事項(xiàng)2022-3-26 當(dāng)主表中沒有

14、對(duì)應(yīng)的記錄時(shí)，不能將記錄添加到子表成績表中不能出現(xiàn)在學(xué)員信息表中不存在的學(xué)號(hào) 不能更改主表中的值而導(dǎo)致子表中的記錄孤立把學(xué)員信息表中的學(xué)號(hào)改變了，學(xué)員成績表中的學(xué)號(hào)也應(yīng)當(dāng)隨之改變 子表存在與主表對(duì)應(yīng)的記錄，不能從主表中刪除該行不能把有成績的學(xué)員刪除了 刪除主表前，先刪子表先刪學(xué)員成績表、后刪除學(xué)員信息表FOREIGN KEY約束約束 外健用于建立和加強(qiáng)兩個(gè)表數(shù)據(jù)之外健用于建立和加強(qiáng)兩個(gè)表數(shù)據(jù)之間的鏈接的一列或多列間的鏈接的一列或多列2022-3-262022-3-26被被FOREIGNFOREIGN KEYKEY 參照的列在參照的列在表中應(yīng)該具有表中應(yīng)該具有PRIMARYPRIMARY KEY

15、KEY約束或約束或 UNIQUEUNIQUE 約束約束 IDNameTelIDsalarytax主鍵主鍵外鍵外鍵FOREIGN KEY約束約束2022-3-26語法：語法：ALTERALTER TABLETABLE table_namename ADDADD CONSTRAINTCONSTRAINT constraint_namename FOREIGNFOREIGN KEYKEY(columncolumn_namename,.) REFERENCESREFERENCES ref_table (columncolumn_namename,.)Eg ：已創(chuàng)建主表：已創(chuàng)建主表student和從表和

16、從表score，定義主表，定義主表“sid”列為主鍵，添加從表中的列為主鍵，添加從表中的“sid”列為外鍵列為外鍵ALTERALTER TABLETABLE score ADDADD CONSTRAINTCONSTRAINT constraint_sid FOREIGNFOREIGN KEYKEY(sid) REFERENCESREFERENCES student (sid)FOREIGN KEY約束約束設(shè)置標(biāo)識(shí)列設(shè)置標(biāo)識(shí)列2022-3-26 表中沒有合適的列作為主鍵怎么辦？操作演示：設(shè)置標(biāo)識(shí)列操作演示：設(shè)置標(biāo)識(shí)列2022-3-26標(biāo)識(shí)列標(biāo)識(shí)列identityidentity每個(gè)表中都可以有一

17、個(gè)標(biāo)識(shí)列。該列值由系統(tǒng)自動(dòng)生每個(gè)表中都可以有一個(gè)標(biāo)識(shí)列。該列值由系統(tǒng)自動(dòng)生成，用以唯一標(biāo)識(shí)插入到表中的每一行。成，用以唯一標(biāo)識(shí)插入到表中的每一行。語法：語法： identityidentity（標(biāo)識(shí)初始值，標(biāo)識(shí)增量值）（標(biāo)識(shí)初始值，標(biāo)識(shí)增量值） Eg Eg ：在：在readerreader表創(chuàng)建時(shí)，定義表創(chuàng)建時(shí)，定義ridrid為標(biāo)識(shí)列，其中標(biāo)為標(biāo)識(shí)列，其中標(biāo)識(shí)初始值和增量值都為識(shí)初始值和增量值都為1 1。Create table reader(Create table reader(rid int not null identity(1,1) ,rid int not null identi

18、ty(1,1) ,Name char(10) not nullName char(10) not null) ) 思考思考2022-3-26 標(biāo)識(shí)列允許為字符數(shù)據(jù)類型嗎？ 如果標(biāo)識(shí)列A的初始值為1，增長量為2，則輸入三行數(shù)據(jù)以后，再刪除兩行，下次再輸入數(shù)據(jù)行的時(shí)候，標(biāo)識(shí)值從多少開始？刪除約束刪除約束查看表中的約束： exec sp_helpconstraint 表名2022-3-26ALTER TABLE 表名表名 DROP CONSTRAINT 約束名約束名 例如：刪除例如：刪除Student表中地址默認(rèn)約束表中地址默認(rèn)約束ALTER TABLE Student DROP CONSTRAIN

19、T DF_stuAddress演示示例演示示例8：刪除：刪除Student表約束表約束添加約束添加約束 添加約束的語法2022-3-26ALTER TABLE 表名表名 ADD CONSTRAINT 約束名約束名 約束類型約束類型 具體的約束說明具體的約束說明約束名的取名規(guī)則推薦采用：約束類型約束名的取名規(guī)則推薦采用：約束類型_約束列約束列主鍵（主鍵（Primary Key）約束：如）約束：如 PK_stuNo唯一（唯一（Unique Key）約束：如）約束：如 UQ_stuID默認(rèn)（默認(rèn)（Default Key）約束：如）約束：如 DF_stuAddress檢查（檢查（Check Key）約

20、束：如）約束：如 CK_stuBornDate外鍵（外鍵（Foreign Key）約束：如）約束：如 FK_stuNo 添加約束示例添加約束示例2-1如何在表Student中添加下面的約束？ 主鍵約束：學(xué)號(hào) 唯一約束：身份證號(hào) 默認(rèn)約束：Address列的默認(rèn)值是“地址不詳” 檢查約束：出生日期的值為1980年1月1日后 外鍵約束：設(shè)置GradeID列為外鍵，建立Grade表與Student表的引用關(guān)系2022-3-26ALTER TABLE Student ADD CONSTRAINT PK_stuNo PRIMARY KEY (StudentNo)ALTER TABLE Student ADD CONSTRAINT UQ_stuID UNIQUE (IDENTITYCard)ALTER TABLE Student ADD CONSTRAINT DF_stuAddress DEFAULT (地址不詳?shù)刂凡辉? FOR