【實(shí)驗(yàn)?zāi)康摹?

1、掌握完整性的概念；

2、熟悉SQL SERVER 的完整性技術(shù)。

3、了解SQL SERVER 的違反完整性處理措施。

【實(shí)驗(yàn)性質(zhì)】

綜合性實(shí)驗(yàn)

【實(shí)驗(yàn)導(dǎo)讀】

1、完整性概述

數(shù)據(jù)庫(kù)完整性指數(shù)據(jù)的（邏輯而非物理）正確性和相容性。為了防止數(shù)據(jù)庫(kù)中存在不合語(yǔ)義的數(shù)據(jù)，防止錯(cuò)誤數(shù)據(jù)的輸入和輸出。數(shù)據(jù)庫(kù)完整性技術(shù)包括完整性約束條件與完整性檢查兩部分。完整性約束條件指為維護(hù)數(shù)據(jù)庫(kù)的完整性，DBMS提供加在數(shù)據(jù)庫(kù)數(shù)據(jù)之上的語(yǔ)義約束條件，作為數(shù)據(jù)庫(kù)模式的一部分存入數(shù)據(jù)庫(kù)。完整性檢查意味檢查數(shù)據(jù)庫(kù)是否滿足完整性約束條件的機(jī)制。

完整性約束條件作用的對(duì)象可以是關(guān)系、元組、列三種。其中列約束主要是列的類型、取值范圍、精度、排序等的約束條件。元組的約束是元組中各個(gè)字段間的聯(lián)系的約束。關(guān)系的約束是若干元組間、關(guān)系集合上以及關(guān)系之間的聯(lián)系的約束。完整性約束條件涉及這三類對(duì)象，其狀態(tài)可以是靜態(tài)的，也可以是動(dòng)態(tài)的。

完整性約束條件一般分為實(shí)體完整性、參考完整性 自定義完整性。定義實(shí)體完整性約束條件要考慮修改關(guān)系中主碼的問(wèn)題；定義參考完整性約束條件要考慮外碼能否接受空值問(wèn)題、在被參照關(guān)系中刪除元組的問(wèn)題（級(jí)聯(lián)刪除或受限刪除）、在參照關(guān)系中插入元組時(shí)的問(wèn)題。

RDBMS一般提供定義完整性約束條件：作為表定義的一部分在創(chuàng)建表時(shí)創(chuàng)建（采用CREATE TABLE）；或者添加到尚沒(méi)有完整性約束條件的表中（ALTER TABLE）。

2、MS SQL Server完整性控制技術(shù)。

詳細(xì)內(nèi)容參閱 “聯(lián)機(jī)叢書”中“創(chuàng)建和管理數(shù)據(jù)庫(kù)”－>“表” －>“設(shè)計(jì)表” －>“使用約束、默認(rèn)值和空值” 、“創(chuàng)建和管理數(shù)據(jù)庫(kù)”－>“表” －>“創(chuàng)建和修改表”相關(guān)主題以及“聯(lián)機(jī)從書”的“Transact-SQL參考”相關(guān)語(yǔ)句。

在MS SQL Server中將完整性分為：

（1）實(shí)體完整性。實(shí)體完整性將行定義為特定表的唯一實(shí)體。實(shí)體完整性強(qiáng)制表的標(biāo)識(shí)符列或主碼的完整性（通過(guò)索引、UNIQUE 約束、PRIMARY KEY 約束或 IDENTITY 屬性）。

（2）域完整性。域完整性是指給定列的輸入有效性。強(qiáng)制域有效性的方法有：限制類型（通過(guò)數(shù)據(jù)類型）、格式（通過(guò) CHECK 約束和規(guī)則）或可能值的范圍（通過(guò) FOREIGN KEY 約束、CHECK 約束、DEFAULT 定義、NOT NULL 定義和規(guī)則）。

（3）引用完整性。在輸入或刪除記錄時(shí)，引用完整性保持表之間已定義的關(guān)系。在 MS SQL Server中，引用完整性基于外碼與主碼之間或外碼與唯一碼之間的關(guān)系（通過(guò) FOREIGN KEY 和 CHECK 約束）。引用完整性確保鍵值在所有表中一致。這樣的一致性要求不能引用不存在的值，如果鍵值更改了，那么在整個(gè)數(shù)據(jù)庫(kù)中，對(duì)該鍵值的所有引用要進(jìn)行一致的更改。

強(qiáng)制引用完整性時(shí)，MS SQL Server 禁止用戶進(jìn)行下列操作：

? 當(dāng)主表中沒(méi)有關(guān)聯(lián)的記錄時(shí)，將記錄添加到相關(guān)表中。

? 更改主表中的值并導(dǎo)致相關(guān)表中的記錄孤立。

? 從主表中刪除記錄，但仍存在與該記錄匹配的相關(guān)記錄。

（4）用戶定義完整性。用戶定義完整性使您得以定義不屬于其它任何完整性分類的特定業(yè)務(wù)規(guī)則。所有的完整性類型都支持用戶定義完整性（CREATE TABLE 中的所有列級(jí)和表級(jí)約束、存儲(chǔ)過(guò)程和觸發(fā)器）。

設(shè)計(jì)表時(shí)需要識(shí)別列的有效值并決定如何強(qiáng)制實(shí)現(xiàn)列中數(shù)據(jù)的完整性。MS SQL Server 提供多種強(qiáng)制列中數(shù)據(jù)完整性的機(jī)制：

? PRIMARY KEY 約束

? FOREIGN KEY 約束

? UNIQUE 約束

? CHECK 約束

? DEFAULT 定義

? 為空性

3、PRIMARY KEY 約束

表中經(jīng)常有一個(gè)列或列的組合，其值能唯一地標(biāo)識(shí)表中的每一行。這樣的一列或多列稱為表的主碼，通過(guò)它可強(qiáng)制表的實(shí)體完整性。當(dāng)創(chuàng)建或更改表時(shí)可通過(guò)定義 PRIMARY KEY 約束來(lái)創(chuàng)建主碼。

一個(gè)表只能有一個(gè) PRIMARY KEY 約束，而且 PRIMARY KEY 約束中的列不能接受空值。由于 PRIMARY KEY 約束確保唯一數(shù)據(jù)，所以經(jīng)常用來(lái)定義標(biāo)識(shí)列。

當(dāng)為表指定 PRIMARY KEY 約束時(shí)，MS SQL Server通過(guò)為主碼列創(chuàng)建唯一索引強(qiáng)制數(shù)據(jù)的唯一性。當(dāng)在查詢中使用主碼時(shí)，該索引還可用來(lái)對(duì)數(shù)據(jù)進(jìn)行快速訪問(wèn)。

如果 PRIMARY KEY 約束定義在不止一列上，則一列中的值可以重復(fù)，但 PRIMARY KEY 約束定義中的所有列的組合的值必須唯一。

4、FOREIGN KEY 約束

外碼是用于建立和加強(qiáng)兩個(gè)表數(shù)據(jù)之間的鏈接的一列或多列。通過(guò)將保存表中主碼值的一列或多列添加到另一個(gè)表中，可創(chuàng)建兩個(gè)表之間的鏈接。這個(gè)列就成為第二個(gè)表的外碼。當(dāng)創(chuàng)建或更改表時(shí)可通過(guò)定義 FOREIGN KEY 約束來(lái)創(chuàng)建外碼。FOREIGN KEY 約束并不僅僅只可以與另一表的 PRIMARY KEY 約束相鏈接，它還可以定義為引用另一表的 UNIQUE 約束。盡管 FOREIGN KEY 約束的主要目的是控制存儲(chǔ)在外碼表中的數(shù)據(jù)，但它還可以控制對(duì)主碼表中數(shù)據(jù)的修改。

當(dāng)用戶試圖刪除或更新外碼所指向的碼時(shí)，級(jí)聯(lián)引用完整性約束可以定義 MS SQL Server 所采取的操作。

5、UNIQUE 約束

可使用 UNIQUE 約束確保在非主碼列中不輸入重復(fù)值。盡管 UNIQUE 約束和 PRIMARY KEY約束都強(qiáng)制唯一性，但在強(qiáng)制下面的唯一性時(shí)應(yīng)使用 UNIQUE 約束而不是 PRIMARY KEY 約束：

? 非主碼的一列或列組合。一個(gè)表可以定義多個(gè) UNIQUE 約束，而只能定義一個(gè) PRIMARY KEY 約束。

? 允許空值的列。允許空值的列上可以定義 UNIQUE 約束，而不能定義 PRIMARY KEY 約束。

6、CHECK 約束

CHECK 約束通過(guò)限制輸入到列中的值來(lái)強(qiáng)制域的完整性。這與 FOREIGN KEY 約束控制列中數(shù)值相似。區(qū)別在于它們?nèi)绾闻袛嗄男┲涤行?#xff1a;FOREIGN KEY 約束從另一個(gè)表中獲得有效數(shù)值列表，CHECK 約束從邏輯表達(dá)式判斷而非基于其它列的數(shù)據(jù)。

對(duì)單獨(dú)一列可使用多個(gè) CHECK 約束。按約束創(chuàng)建的順序?qū)ζ淙≈怠Ｍㄟ^(guò)在表一級(jí)上創(chuàng)建 CHECK 約束，可以將該約束應(yīng)用到多列上。

7、DEFAULT 約束

元組中的每一列均必須有值，即使它是 NULL。可能會(huì)有這種情況，當(dāng)向表中裝載新行時(shí)可能不知道某一列的值，或該值尚不存在。如果該列允許空值，就可以將該行賦予空值。由于有時(shí)不希望有可為空的列，因此如果合適，更好的解決辦法可能是為該列定義 DEFAULT 定義。

當(dāng)將某行裝載到為行定義了 DEFAULT 的表中時(shí)，若沒(méi)有指定列值，則是隱性要求 MS SQL Server將默認(rèn)值裝載到該列中

8、允許空值

列為空性決定該列在表中是否允許空值。空值或 NULL 并不等于零 (0)、空白或零長(zhǎng)度的字符串（如“”），NULL 意味著沒(méi)有輸入。NULL 的存在通常表明值未知或未定義。例如，pubs 數(shù)據(jù)庫(kù) titles 表中 price 列的空值并不表示該書沒(méi)有價(jià)格，而是指其價(jià)格未知或尚未設(shè)定。總之，由于空值在查詢和更新時(shí)會(huì)使事情變得更復(fù)雜，而且有其它列選項(xiàng)，如 PRIMARY KEY 約束等不能使用允許空值的列，所以應(yīng)避免允許空值。

如果插入某行但沒(méi)有為允許空值的列包括值，則 MS SQL Server提供 NULL 值（除非存在 DEFAULT 定義或 DEFAULT 對(duì)象）。

指定一列不允許空值而確保行中一列永遠(yuǎn)包含數(shù)據(jù)可以保持?jǐn)?shù)據(jù)的完整性。如果不允許空值，用戶在向表中寫數(shù)據(jù)時(shí)必須在列中輸入一個(gè)值，否則該行不被接收入數(shù)據(jù)庫(kù)。

【實(shí)驗(yàn)內(nèi)容】

use qixin
drop table 職工
drop table 部門

create table 部門
(
?部門號(hào)???? char(4),
?名稱???? varchar(20) not null,
?經(jīng)理名???? varchar(8),
?地址???? varchar(50),
?電話號(hào)???? varchar(20),
?constraint PK_部門號(hào) primary key(部門號(hào)),
?constraint U_名稱 unique(名稱)
)

create table 職工
(
?職工號(hào)???? char(4) ,
?姓名???? varchar(8) not null ,
?年齡???? int ,
?職務(wù)???? varchar(10) ,
?工資???? money,
?部門號(hào)???? char(4),
?constraint PK_職工號(hào) primary key(職工號(hào)),
?constraint FK_部門號(hào) foreign key(部門號(hào)) references 部門(部門號(hào)) on delete cascade,
?constraint CK_年齡 check(年齡<=60)
)

insert into 部門 values('0001','財(cái)務(wù)科','張三','湖北汽車工業(yè)學(xué)院','8238787')
/*(1)*/
--如果再次insert into 部門 values('0001','財(cái)務(wù)科','張三','湖北汽車工業(yè)學(xué)院','8238787')，
--則會(huì)違反了 PRIMARY KEY 約束 'PK__部門__571DF1D5'。不能在對(duì)象 '部門' 中插入重復(fù)鍵。
/*(2)*/
--如果執(zhí)行insert into 部門 values('0002','財(cái)務(wù)科','張三','湖北汽車工業(yè)學(xué)院','8238787')
--則會(huì)違反了 UNIQUE KEY 約束 'UQ__部門__5812160E'。不能在對(duì)象 '部門' 中插入重復(fù)鍵。
insert into 部門 values('0002','教務(wù)科','李四','湖北汽車工業(yè)學(xué)院','8238787')
insert into 部門 values('0003','人事科','王二','湖北汽車工業(yè)學(xué)院','8238787')

insert into 職工 values('0001','張偉','30','科長(zhǎng)',2000,'0001')
insert into 職工 values('0002','李紅','25','副科長(zhǎng)',1500,'0001')
insert into 職工 values('0003','王強(qiáng)','33','科長(zhǎng)',2000,'0002')
insert into 職工 values('0004','趙東','34','副科長(zhǎng)',1500,'0002')
insert into 職工 values('0005','陳三','29','科長(zhǎng)',2000,'0003')
insert into 職工 values('0006','孫波','28','副科長(zhǎng)',1500,'0003')
--如果執(zhí)行insert into 職工 values('0007','陳紅','70','副科長(zhǎng)',1500,'0003')
--則會(huì)INSERT 語(yǔ)句與 COLUMN CHECK 約束 'CK__職工__年齡__656C112C' 沖突。
--該沖突發(fā)生于數(shù)據(jù)庫(kù) 'qixin'，表 '職工', column '年齡'。

select * from 部門
select * from 職工

--當(dāng)指定on delete cascade時(shí)為級(jí)聯(lián)刪除，刪除部門表記錄時(shí)，職工表中相關(guān)的記錄也會(huì)同時(shí)刪除
delete from 部門 where 部門號(hào)='0001'
delete from 部門 where 部門號(hào)='0002'
delete from 部門 where 部門號(hào)='0003'

--如果不指定on delete cascade時(shí)默認(rèn)為受限刪除,刪除部門表記錄時(shí)
--則會(huì)DELETE 語(yǔ)句與 COLUMN REFERENCE 約束 'FK__職工__部門號(hào)__6D0D32F4' 沖突。
--該沖突發(fā)生于數(shù)據(jù)庫(kù) 'qixin'，表 '職工', column '部門號(hào)'。

總結(jié)

以上是生活随笔為你收集整理的实验五 数据库完整性技术的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。