第6章數(shù)據(jù)完整性

1、第6章 數(shù)據(jù)完整性為了維護(hù)數(shù)據(jù)庫中的數(shù)據(jù)和現(xiàn)實(shí)世界的一致性，SQL Server提供了確保數(shù)據(jù)庫的完整性技術(shù)。數(shù)據(jù)完整性是指存儲(chǔ)在數(shù)據(jù)庫中的數(shù)據(jù)的一致性和準(zhǔn)確性。強(qiáng)制數(shù)據(jù)完整性可以確保數(shù)據(jù)庫中的數(shù)據(jù)質(zhì)量，防止數(shù)據(jù)庫中存在不符合語義規(guī)定的數(shù)據(jù)和防止因錯(cuò)誤信息的輸入輸出造成無效操作或錯(cuò)誤信息。數(shù)據(jù)完整性有4種類型：實(shí)體完整性、域完整性、參照完整性和用戶自定義完整性。關(guān)系數(shù)據(jù)庫的數(shù)據(jù)與更新操作必須滿足這4種完整性規(guī)則。一、實(shí)體完整性實(shí)體完整性規(guī)定表的每一行在表中是唯一的實(shí)體。在SQL Server中，可以通過建立Primary Key約束、Unique約束來實(shí)現(xiàn)實(shí)體完整性。二、域完整性域完整性是指數(shù)

2、據(jù)庫表中的列必須滿足某種特定的數(shù)據(jù)類型或約束。在SQL Server中，可以通過建立check約束、foreign key約束和default、not null來定義域完整性。三、參照完整性 參照完整性是指兩個(gè)表的主關(guān)鍵字和外關(guān)鍵字的數(shù)據(jù)應(yīng)對(duì)應(yīng)一致。它確保有主關(guān)鍵字的表中對(duì)應(yīng)其他表的外關(guān)鍵字的行存在，即不能引用不存在的實(shí)體。SQL Server中，可以通過建立Primary Key約束和foreign key約束來實(shí)現(xiàn)參照完整性。四、用戶自定義完整性用戶自定義完整性是針對(duì)某個(gè)特定關(guān)系數(shù)據(jù)庫的約束條件，它反映某一具體應(yīng)用所涉及的數(shù)據(jù)必須滿足的語義要求。在SQL Server中，可以建立check

3、約束和default約束來實(shí)現(xiàn)用戶自定義完整性約束。6.2 創(chuàng)建和使用規(guī)則規(guī)則：對(duì)字段值進(jìn)行限制的方法。6.3 創(chuàng)建約束約束就是限制的意思，跟規(guī)則用途差不多。在SQL Server2000中，約束用來限制列值，確保列的有效性，以實(shí)現(xiàn)數(shù)據(jù)完整性。一、約束類型： 主鍵約束（Primary Key）：確保沒有輸入 重復(fù)值，不能為空，不能重復(fù)?？梢允?一個(gè)或多字段的組合，但一個(gè)表只能有 保證數(shù)據(jù)惟一性 一個(gè)主鍵。 （實(shí)體完整性） 惟一約束（Unique）：防止每一行的相關(guān) 列(非主鍵)出現(xiàn)重復(fù)值。一個(gè)表可以有 多個(gè)惟一約束。 默認(rèn)值約束（Default）：當(dāng)INSERT語句 沒有明確提供某列的值時(shí)，指

4、定為該列 保證數(shù)據(jù)有效性 提供缺省值 。 （用戶自定義完整性） 檢查約束（Check）：指定某列可接受的 值的范圍。 外鍵約束（Foreign Key）：定義一列或者多 保證數(shù)據(jù)關(guān)聯(lián)性 列，其值與其他表的主鍵值匹配。 （參照完整性）約束是限制用戶輸入到表中的數(shù)據(jù)的值的范圍，一般分為列級(jí)約束與表級(jí)約束。列級(jí)約束有六種：主鍵Primary key、外鍵foreign key 、唯一 unique、檢查 checck 、默認(rèn)default 、非空/空值 not null/ null表級(jí)約束有四種：主鍵、外鍵、唯一、檢查列級(jí)約束與表級(jí)約束的區(qū)別：如果完整性約束涉及到該表的多個(gè)屬性列，必須定義在表級(jí)上，

5、否則既可以定義在列級(jí)也可以定義在表級(jí)。簡而言之：列級(jí)約束：是對(duì)某一個(gè)特定列的約束，只能應(yīng)用于一列上。表級(jí)約束：是獨(dú)立于列的定義，可以應(yīng)用在一個(gè)表中的多列上。二、主鍵約束（primary key）primary key約束用于定義基本表的主鍵，它惟一確定表中每一條記錄，其值不能為NULL，也不能重復(fù)，以此來保證實(shí)體的完整性。具有以下特點(diǎn)：一個(gè)表只能建一個(gè)主鍵約束。主鍵約束可以定義在多個(gè)字段上。主鍵所在列不為空。主鍵所在列值不能重復(fù)。如果主鍵定義在多個(gè)列上，則一列中的值可以重復(fù)，但是所有列組合值必須唯一。1、創(chuàng)建primary key約束格式：constraint 約束名 primary key

6、(列,.n)形式一：創(chuàng)建列級(jí)約束Create table 學(xué)生(學(xué)號(hào) char(6) primary key, /*列約束是對(duì)某一個(gè)特定列的約束，包含在列 姓名 char(8) not null, 定義中，直接跟在該列的其他定義之后，用空 性別 char(2), 格分隔，不必指定列名*/年齡 tinyint,身份證號(hào) char(18)形式二：創(chuàng)建表級(jí)約束Create table 成績(學(xué)號(hào) char(6) not null, /*表約束與列定義相互獨(dú)立，不包括在列定義中，通常課程號(hào) char(4) not null, 用于對(duì)多個(gè)列一起進(jìn)行約束，與列定義用, 分隔，成績 tinyint, 定義表

7、約束時(shí)必須指出要約束的那些列的名稱。*/Constraint pk_成績 primary key(學(xué)號(hào),課程號(hào))2、添加primary key約束格式： alter table 表名 Add constraint 約束名 primary key(列，,n)create table 課程(課程號(hào) char(4) not null,課程名 varchar(18) not null,學(xué)時(shí) tinyint,學(xué)分 real)alter table 課程add constraint pk_課程 primary key (課程號(hào))3、刪除primary key約束格式： alter table 表名drop

8、 constraint 約束名alter table 課程drop constraint pk_課程4、用企業(yè)管理器創(chuàng)建主鍵約束數(shù)據(jù)庫數(shù)據(jù)表（右鍵）設(shè)計(jì)表選中字段（右鍵）“設(shè)置主鍵”三、惟一性約束（確保在非主鍵列中不輸入重復(fù)值）惟一性約束用于指定一個(gè)或者多個(gè)列的組合的值具有惟一性，以防止在列中輸入重復(fù)的值。unique約束是用來確保不受主鍵約束列上的數(shù)據(jù)的惟一性.當(dāng)使用唯一性約束時(shí)，需要考慮以下幾個(gè)因素： 使用唯一性約束的字段允許為空值。一個(gè)表中可以允許有多個(gè)唯一性約束?？梢园盐ㄒ恍约s束定義在多個(gè)字段上。注：惟一性約束允許為空，但系統(tǒng)為保證其唯一性最多只能出現(xiàn)一個(gè)NULL值。unique與pr

9、imary key的區(qū)別在于：unique約束主要用于非主鍵的一列或多列上要求數(shù)據(jù)唯一。unique約束允許該列上存在NULL值,而主鍵決不允許出現(xiàn)?？梢栽谝粋€(gè)表創(chuàng)建多個(gè)unique約束，而在一個(gè)表上只能創(chuàng)建一個(gè)主鍵。1、創(chuàng)建unique約束格式：constraint 約束名 unique （列）形式一：create table 學(xué)生表(學(xué)號(hào) char(8) primary key,姓名 char(8) not null,性別 char(2),年齡 tinyint,身份證號(hào) char(18) unique)形式二：create table 學(xué)生表(學(xué)號(hào) char(8) primary key,

10、姓名 char(8) not null,性別 char(2),專業(yè)名 varchar(20),年齡 tinyint,身份證號(hào) char(18),constraint uk_cardid unique (身份證號(hào))2、添加unique約束格式： alter table 表名 Add constraint 約束名 unique(列，,n)create table 學(xué)生表(學(xué)號(hào) char(8) primary key, 姓名 char(8) not null,性別 char(2),年齡 tinyint,身份證號(hào) char(18)alter table 學(xué)生表add constraint uk_nam

11、e unique (姓名)3、刪除唯一性約束格式： alter table 表名drop constraint 約束名alter table 學(xué)生表drop constraint uk_name4、用企業(yè)管理器創(chuàng)建唯一性約束數(shù)據(jù)表（右鍵）設(shè)計(jì)表選中字段（右鍵）“索引/鍵”新建選擇字段名稱，選擇復(fù)選框“創(chuàng)建unique”和“約束”關(guān)閉保存編輯框四、檢查約束（check）檢查約束對(duì)輸入列或者整個(gè)表中的值設(shè)置檢查條件，以限制輸入值，保證數(shù)據(jù)庫中數(shù)據(jù)的完整性。如，一個(gè)字段只能輸入整數(shù)，而且限定在0-100的整數(shù)，以此來保證域的完整性。一個(gè)表中可以定義多個(gè)檢查約束。每個(gè)create table語句中每個(gè)

12、字段只能定義一個(gè)檢查約束。在多個(gè)字段上定義檢查約束，則必須將檢查約束定義為表級(jí)約束。當(dāng)執(zhí)行insert語句或者update語句時(shí)，檢查約束將驗(yàn)證數(shù)據(jù)。1、創(chuàng)建check 檢查約束格式：constraint 約束名 check (條件)形式一：create table 成績表(學(xué)號(hào) char(8) not null,課程號(hào) char(4) not null,成績 tinyint check (成績>= 0 and 成績 <=100),constraint pk_chj primary key(學(xué)號(hào),課程號(hào))形式二：create table 成績表(學(xué)號(hào) char(8) not nul

13、l,課程號(hào) char(4) not null,成績 tinyint,constraint pk_chj primary key(學(xué)號(hào),課程號(hào)),constraint chk_score check (成績>= 0 and 成績 <=100)2、添加check檢查約束格式：alter table 表名 Add constraint 約束名 check (條件)create table 學(xué)生表(學(xué)號(hào) char(8) primary key,姓名 char(8) not null,性別 char(2),年齡 int,身份證號(hào) char(18)alter table 學(xué)生表add cons

14、traint chk_sex check(性別 in ('男','女')3、刪除check檢查約束格式： alter table 表名drop constraint 約束名alter table 學(xué)生表drop constraint chk_sex4、用企業(yè)管理器創(chuàng)建檢查約束數(shù)據(jù)表（右鍵）設(shè)計(jì)表選中字段（右鍵）“check約束”新建輸入約束表達(dá)式關(guān)閉保存五、默認(rèn)約束（default）默認(rèn)約束指定在插入操作中如果沒有提供輸入值時(shí)，則系統(tǒng)自動(dòng)指定值。使用缺省約束時(shí)，應(yīng)該注意以下幾點(diǎn)：每個(gè)字段只能定義一個(gè)缺省約束。如果定義的缺省值長于其對(duì)應(yīng)字段的允許長度，那么輸入到表

15、中的缺省值將被截?cái)?。不能加入到帶有identity屬性或者數(shù)據(jù)類型為timestamp的字段上。1、創(chuàng)建 default 默認(rèn)約束格式：constraint 約束名 default 表達(dá)式 for 字段名create table 學(xué)生表(學(xué)號(hào) char(8) not null primary key,姓名 char(8),性別 char(2) default'女')2、添加默認(rèn)約束alter table 表名Add constraint 約束名 default 表達(dá)式 for 字段名create table 學(xué)生表(學(xué)號(hào) char(8) not null primary key

16、,姓名 char(8),性別 char(2)alter table 學(xué)生表add constraint de_sex default '女' for 性別3、刪除默認(rèn)約束格式： alter table 表名drop constraint 約束名alter table 學(xué)生表drop constraint de_sex 4、用企業(yè)管理器創(chuàng)建默認(rèn)約束數(shù)據(jù)表（右鍵）設(shè)計(jì)表選中字段在默認(rèn)列輸入默認(rèn)值六、外鍵約束（foreign key）外鍵約束用于強(qiáng)制參照完整性，提供單個(gè)字段或者多個(gè)字段的參照完整性。當(dāng)使用外鍵約束時(shí)，應(yīng)該考慮以下幾個(gè)因素：外鍵約束提供了字段參照完整性。外鍵從句中的字段

17、數(shù)目和每個(gè)字段指定的數(shù)據(jù)類型必須和reference從句中的字段相匹配。外鍵約束不能自動(dòng)創(chuàng)建索引，需要用戶手動(dòng)創(chuàng)建。在臨時(shí)表中，不能使用外鍵約束。主鍵和外鍵的數(shù)據(jù)類型必須嚴(yán)格匹配。1、創(chuàng)建外鍵約束語法：constraint 約束名 foreign key (列,n) references 引用表 (引用列 ,n)形式一：create table 成績表(學(xué)號(hào) char(8) references 學(xué)生表(學(xué)號(hào)),課程號(hào) char(4) references 課程表(課程號(hào)),成績 tinyint,constraint pk_chj primary key(學(xué)號(hào),課程號(hào))形式二：create t

18、able 成績表(學(xué)號(hào) char(8) not null,課程號(hào) char(4) not null,成績 tinyint,constraint pk_chj primary key(學(xué)號(hào),課程號(hào)),constraint fk_學(xué)號(hào) foreign key (學(xué)號(hào)) references 學(xué)生表(學(xué)號(hào)),constraint fk_課程號(hào) foreign key(課程號(hào)) references課程表(課程號(hào))2、添加外鍵約束格式：alter table 表名 Add constraint 約束名 foreign key (列,n) references引用表 (引用列 ,n)create table 成績表(學(xué)號(hào) char(8) not null, 課程號(hào) char(4) not null, 成績 tinyint)alter table 成績表add constraint fk_學(xué)號(hào) foreign key (學(xué)號(hào)) references 學(xué)生表(學(xué)號(hào))alter table 成績表constraint fk_課程號(hào) foreign key (課程號(hào)) references 學(xué)生表(課程號(hào))alter table 成績表a