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

1、大連海事大學(xué)數(shù)據(jù)庫原理課程實驗大綱實驗名稱： 實驗七 完整性 實驗學(xué)時： 2 適用專業(yè)： 實驗環(huán)境： 執(zhí) 筆 者： 編寫日期： 1 實驗?zāi)康模?） 掌握實體完整性、參照完整性和用戶自定義完整性約束的創(chuàng)建方法。（2） 掌握完整性約束的運行檢查機制。（3） 掌握參照完整性的級聯(lián)刪除和修改方法。（4） 掌握正確設(shè)計關(guān)系模式完整性約束的方法。2 實驗內(nèi)容2.1 掌握實體完整性約束的創(chuàng)建和使用方法（1） 創(chuàng)建表時定義由一個屬性組成的主鍵（給約束命名）。（2） 創(chuàng)建表時定義由兩個或兩個以上屬性組成的主鍵（給約束命名）。（3） 刪除以上兩個主鍵約束。（4） 利用ALTER TABLE語句定義上述兩個主鍵。2

2、.2 掌握參照完整性約束的創(chuàng)建和使用方法（5） 創(chuàng)建表時定義一個列級參照完整性約束（給約束命名）。（6） 創(chuàng)建表時定義一個表級的由兩個屬性組成的參照完整性約束（給約束命名）。（7） 設(shè)計數(shù)據(jù)更新語句檢查參照完整性約束是否起作用。（8） 刪除上述完整性約束。（9） 利用ALTER TABLE 建立上述參照完整性約束，并且規(guī)定UPDATE/DELETE時的動作。（10） 設(shè)計數(shù)據(jù)更新語句檢查參照完整性約束及其相應(yīng)的動作是否起作用。2.3 掌握用戶自定完整性約束的創(chuàng)建和使用方法（11） 定義一個檢查約束，檢查其值在某個取值范圍內(nèi)，并設(shè)計相應(yīng)的更新語句檢查該約束是否起作用？（12） 定義一個檢查其值符

3、合某個匹配模式的檢查約束（使用LIKE），并設(shè)計相應(yīng)的更新語句檢查該約束是否起作用？（13） 定義一個檢查其值符合某個正則表達式的檢查約束（使用SIMILAR TO），并設(shè)計相應(yīng)的更新語句檢查該約束是否起作用？（14） 定義一個UNIQUE約束，并設(shè)計相應(yīng)的更新語句檢查該約束是否起作用？（15） 定義一個DEFAULT約束，設(shè)計一個INSERT語句檢查該約束是否起作用。3 實驗要求（1） 深入復(fù)習(xí)教材第五章數(shù)據(jù)庫完整性約束內(nèi)容。（2） 根據(jù)書上的例子，針對TPCH數(shù)據(jù)庫模式設(shè)計各種完整性約束，每種類型完整性約束至少要設(shè)計一個，描述清楚完整性約束要求，設(shè)計和運行觸發(fā)完整性約束檢查的數(shù)據(jù)更新語句，

4、并截圖相應(yīng)的實驗結(jié)果，每幅截圖并要有較為詳細的描述。也可以按照附1所列示例做實驗。（3） 實驗步驟和實驗總結(jié)中要詳細描述實驗過程中出現(xiàn)的問題、原因和解決方法。（4） 思考題：完整性約束的違約處理有哪幾種方式？4 實驗步驟4.1 掌握實體完整性約束的創(chuàng)建和使用方法（1） 創(chuàng)建表時定義由一個屬性組成的主鍵（給約束命名）。創(chuàng)建零件表part時定義主鍵。/CREATE TABLE PART(PARTKEY INTEGER,MFGR CHAR(100),TYPE CHAR(25),SIZE INTEGER,NAME CHAR(55),CONTAINER CHAR(10),BRAND CHAR(10),R

5、ETAILPRICE REAL,COMMENT VARCHAR(23),CONSTRAINT PARTY PRIMARY KEY(PARTKEY)如圖：partkey為主鍵（2） 創(chuàng)建表時定義由兩個或兩個以上屬性組成的主鍵（給約束命名）。創(chuàng)建零件供應(yīng)關(guān)系表partsupp時定義主鍵。CREATE TABLE PARTSUPP(PARTKEY INTEGER,AVAILQTY INTEGER,SUPPKEY INTEGER,COMMENT VARCHAR(199),SUPPLYCOST REAL,CONSTRAINT PARTSUPPY PRIMARY KEY(PARTKEY,SUPPKEY);

6、如圖給點約束名PARTSUPPY.PARTKEY,和SUPPKEY為主鍵（3） 刪除以上兩個主鍵約束。刪除零件表和零件供應(yīng)關(guān)系表的主鍵。ALTER TABLE PARTSUPPDROP CONSTRAINT PARTSUPPY;利用約束名SUPPKEY刪除約束ALTER TABLE PARTDROP CONSTRAINT PARTY利用約束名PARTY刪除約束（4） 利用ALTER TABLE語句定義上述兩個主鍵。利用ALTER TABLE增加part和partsupp表的主鍵。ALTER TABLE PARTADD CONSTRAINT PARTY PRIMARY KEY (PARTKEY)

7、;利用ALTER給表PART增加約束PARTYALTER TABLE PARTSUPPADD CONSTRAINT PARTSUPPY PRIMARY KEY(PARTKEY,SUPPKEY)利用ALTER給表增加約束PARTSUPPY主鍵PARTKEY,SUPPKEY4.2 掌握參照完整性約束的創(chuàng)建和使用方法（5） 創(chuàng)建表時定義一個列級參照完整性約束（給約束命名）。定義partsupp表的列級參照完整性約束。CREATE TABLE PARTSUPP(PARTKEY INTEGER,AVAILQTY INTEGER,SUPPKEY INTEGER,COMMENT VARCHAR(199),S

8、UPPLYCOST REAL NOT NULL,CONSTRAINT PARTSUPPY PRIMARY KEY(PARTKEY,SUPPKEY);（6） 創(chuàng)建表時定義一個表級的由兩個屬性組成的參照完整性約束（給約束命名）。定義lineitem的參照完整性約束。 CREATE TABLE lineitem (orderkey INTEGER,partkey INTEGER,suppkey INTEGER,linenumber INTEGER,quantity REAL,extendedprice REAL,discount REAL,tax REAL,returnflag CHAR(1),li

9、nestatus CHAR(1),shipDATE DATE,commitDATE DATE,receiptDATE DATE,shipinstruct CHAR(25),shipmode CHAR(10),comment VARCHAR(44)CONSTRAINT LINETY PRIMARY KEY(ORDERKEY,PARTKEY,SUPPKEY),CONSTRAINT FPART FOREIGN KEY(PARTKEY) REFERENCES PART,CONSTRAINT FPARTSUPP FOREIGN KEY(SUPPKEY,PARTKEY) REFERENCES PARTSU

10、PP);創(chuàng)建表時創(chuàng)建外碼FPART和FPARTSUPP分別為PART和PARTSUPP表的主碼（7） 設(shè)計數(shù)據(jù)更新語句檢查參照完整性約束是否起作用。設(shè)計INSERT、DELETE和UPDATE語句檢查上述完整性約束是否有效。INSERT INTO lineitem (orderkey,partkey,suppkey)VALUES(4,2,4)INSERT INTO PART(PARTKEY,SIZE,RETAILPRICE)VALUES(1,34,2)（8） 刪除上述完整性約束。刪除partsupp和lineitem的參照完整性約束。ALTER TABLE LINEITEM DROP CONS

11、TRAINT FPARTALTER TABLE LINEITEMDROP CONSTRAINT FPARTSUPPALTER TABLE PARTSUPPDROP CONSTRAINT FPART（9） 利用ALTER TABLE 建立上述參照完整性約束，并且規(guī)定UPDATE/DELETE時的動作。利用ALTER TABLE 建立上述兩個完整性約束，并規(guī)定UPDATE和DELETE級聯(lián)修改和刪除。ALTER TABLE PARTSUPP ADD CONSTRAINT FPART FOREIGN KEY(PARTKEY) REFERENCES PART ON DELETE CASCADE ON

12、UPDATE NO ACTIONALTER TABLE LINEITEMADD CONSTRAINT FPS1 FOREIGN KEY(PARTKEY) REFERENCES PART ON DELETE CASCADE ON UPDATE NO ACTIONALTER TABLE LINEITEM ONADD CONSTRAINT FPS2 FOREIGN KEY(SUPPKEY,PARTKEY) REFERENCES PARTSUPP ON DELETE NO ACTION ON UPDATE NO ACTION（10） 設(shè)計數(shù)據(jù)更新語句檢查參照完整性約束及其相應(yīng)的動作是否起作用。刪除零件

13、供應(yīng)記錄partsupp 或者是 lineitem表中的記錄，檢查約束是否起作用？刪除主鍵時因為主碼約束無法刪除4.3 掌握用戶自定完整性約束的創(chuàng)建和使用方法（11） 定義一個檢查約束，檢查其值在某個取值范圍內(nèi)，并設(shè)計相應(yīng)的更新語句檢查該約束是否起作用？定義一個檢查Lineitem中的折扣只能在0和1之間的CHECK約束，并設(shè)計INSERT語句檢查約束是否起作用。ALTER TABLE lineitem ADD CONSTRAINT CHE1 CHECK (DISCOUNT between 0 and 1 )（12） 定義一個檢查其值符合某個匹配模式的檢查約束（使用LIKE），并設(shè)計相應(yīng)的更新

14、語句檢查該約束是否起作用？定義一個CHECK約束，檢查國家表nation中的國家名稱的最后一個字必須為“國”字。設(shè)計一個INSERT語句檢查該約束是否有效。alter table nationadd constraint namek check(name like%國);（13） 定義一個檢查其值符合某個正則表達式的檢查約束（使用SIMILAR TO），并設(shè)計相應(yīng)的更新語句檢查該約束是否起作用？定義一個CHECK約束，檢查CUSTOMER表中的PHONE必須由12位數(shù)字組成。并設(shè)計一個INSERT語句或者是UPDATE語句檢查該約束是否有效。alter table customeradd co

15、nstraint CHE3 check( PHONE SIMILAR TO(_);不為12位語句已終止（14） 定義一個UNIQUE約束，并設(shè)計相應(yīng)的更新語句檢查該約束是否起作用？在NATION表的NAME字段上定義一個UNIQUE約束，不允許國家名重復(fù)。設(shè)計一個INSERT語句檢查約束是否有效。alter table nationadd constraint CHE4 UNIQUE(NAME);（15） 定義一個DEFAULT約束，設(shè)計一個INSERT語句檢查該約束是否起作用。在SUPPLIER表中的NATIONKEY字段上定義一個DEFAULT約束，默認取值為中國的國家代碼。并設(shè)計一個IN

16、SERT語句檢查該約束是否有效。ALTER TABLE SUPPLIERADD CONSTRAINT CHE5 DEFAULT 1 FOR NATIONKEYINSERT INTO SUPPLIER(SUPPKEY,NAME)VALUES(1,張鋒)默認為1，如圖示思考題：沖突處理有四種解決方式：no action,restrict,set null,cascadeNo action指在違約時直接回滾不作任何處理，restrict在違約時拒絕執(zhí)行命令，set 在違約時把違約項設(shè)為空值，cascade當(dāng)當(dāng)刪除或修改參照關(guān)系中某元組的主碼值時，被參照關(guān)系中，那些外部碼具有該值的元組也將被刪除或修改，以保證參照完整性。5 總結(jié)與體