數(shù)據(jù)庫技術(shù)與應(yīng)用實驗 實驗6 數(shù)據(jù)完整性實驗6 數(shù)據(jù)完整性一、實驗?zāi)康?1掌握Transact-SQL語句(CREATE RULE、DROP RULE)創(chuàng)建和刪除規(guī)則的方法。 2掌握系統(tǒng)存儲過程sp_bindrule、sp_unbindrule綁定和解除綁定規(guī)則的操作方法，以及sp_help、sp_helptext查詢規(guī)則信息、sp_rename更名規(guī)則的方法。 3掌握Transact-SQL語句(CREATE DEFAULT、DROPDEFAULT)創(chuàng)建和刪除默認(rèn)對象的方法。4掌握系統(tǒng)存儲過程sp_bindefault、sp_unbindefault綁定和解除綁定默認(rèn)對象的操作方法，以及sp_helptext查詢規(guī)則信息。5掌握SQL Server管理平臺和Transact-SQL語句(CREATE TABLE、ALTER TABLE)定義和刪除約束的方法，并了解約束的類型。二、實驗內(nèi)容和步驟 1為studentsdb數(shù)據(jù)庫創(chuàng)建一個規(guī)則，限制所輸入的數(shù)據(jù)為7位09的數(shù)字。 (1)復(fù)制學(xué)生表命名為stu_phone，在stu_phone表中插入一列，列名為“電話號碼”。完成以下代碼實現(xiàn)該操作。 SELECT * INTO stu_phone FROM 學(xué)生表 ALTER TABLE stu_phone ADD 電話號碼 CHAR(7)NULLstu_phone表結(jié)構(gòu)如圖1-10所示。圖1-10 stu_phone表結(jié)構(gòu)SELECT * INTO stu_phone FROM 學(xué)生表ALTER TABLE stu_phone ADD 電話號碼CHAR(7)NULL(2)創(chuàng)建一個規(guī)則phone_rule，限制所輸入的數(shù)據(jù)為7位09的數(shù)字。CREATE rule phone_ruleAS電話號碼LIKE '0-90-90-90-90-90-90-9'檢驗：電話號碼為8位時無法插入！電話號碼為7位時可以插入！(3)使用系統(tǒng)存儲過程sp_hndrule將phone_rule規(guī)則綁定到stu_phone表的“電話號碼”列上。sp_hndrule phone_rule , stu_phone.電話號碼(4)插入操作輸入以下數(shù)據(jù)：學(xué)號 姓名 電話號碼 0009 王國強(qiáng) 1234yyy是否可以成果插入？如果出現(xiàn)錯誤，請列出錯誤信息，為什么會產(chǎn)生該出錯信息?如果要實現(xiàn)插入操作，應(yīng)修改INSERT INTO語句中的哪個值?phone_rule規(guī)則能否對其他操作(如DELETE)進(jìn)行規(guī)則檢查?INSERT INTO stu_phone(學(xué)號,姓名,電話號碼) VALUES('0009','王國強(qiáng)','1234yyy')錯誤信息：消息513，級別16，狀態(tài)0，第1 行列的插入或更新與先前的CREATE RULE 語句所指定的規(guī)則發(fā)生沖突。該語句已終止。沖突發(fā)生于數(shù)據(jù)庫'studentsdb'，表'dbo.stu_phone'，列'電話號碼'。語句已終止。修改后：INSERT INTO stu_phone(學(xué)號,姓名,電話號碼) VALUES('0009','王國強(qiáng)','1234yyy')phone_rule規(guī)則能否對其他操作(如DELETE)進(jìn)行規(guī)則檢查?可以2創(chuàng)建一個規(guī)則stusex_rule，將其綁定到stu_phone表的“性別”列上，保證輸入的性別值只能是“男”或“女”。CREATE rule stusex_ruleAS性別in ('男','女')sp_bindrule stusex_rule , 'stu_phone.性別'3使用系統(tǒng)存儲過程sp_help查詢stusex_rule規(guī)則列表，使用sp_helptext查詢stusex_rule規(guī)則的文本，使用sp_rename將stusex_rule規(guī)則更名為stu_s_rule。sp_help stusex_rulesp_helptext stusex_rulesp_rename stusex_rule,stu_s_rule4刪除stu_s_rule規(guī)則。先解除綁定：sp_unbindrule stu_s_rule, 'stu_phone.性別'再刪除規(guī)則：drop rule stu_s_rule注意：stu_s_ rule為stusex_ rule更名后規(guī)則名，是否仍然綁定在stu_phone表的“性別”列上，應(yīng)如何操作才能刪除它。 5在studentdb數(shù)據(jù)庫中，建立日期、貨幣和字符等數(shù)據(jù)類型的默認(rèn)對象。 (1)創(chuàng)建默認(rèn)對象df_date、df_char、df_money。l 創(chuàng)建日期型默認(rèn)對象df_date，默認(rèn)日期為2006-4-12。l 創(chuàng)建字符型默認(rèn)對象df_char，默認(rèn)字符為“unknown”l 創(chuàng)建貨幣型默認(rèn)對象df_money，默認(rèn)為100元CREATE DEFAULT df_dateAS '2009-4-12'GOCREATE DEFAULT df_charAS 'unknown'GOCREATE DEFAULT df_money AS $100GO(2)在studentsdb數(shù)據(jù)庫中創(chuàng)建stu_fee數(shù)據(jù)表。學(xué)號 char(10) NOT NULL姓名 char(8) NOT NULL，學(xué)費 money交費日期 datetime電話號碼 char(7)表stu_fee的數(shù)據(jù)結(jié)構(gòu)如圖1-11所示。學(xué)號姓名性別交費日期電話號碼圖l-11 stu_fee的數(shù)據(jù)結(jié)構(gòu)CREATE TABLE stu_fee(學(xué)號char(10) NOT NULL,姓名char（）NOT NULL,學(xué)費money,交費日期datetime,電話號碼char（）(3)使用系統(tǒng)存儲過程sp_bindefault將默認(rèn)對象df_date、df_char、df_money分別綁定在stu_fee表的“學(xué)費”、“交費日期”、“電話號碼”列上。sp_bindefault df_money,'stu_fee.學(xué)費'GOsp_bindefault df_date,'stu_fee.交費日期'GOsp_bindefault df_char,'stu_fee.電話號碼'GO(4) 輸入命令，在stu_fee表進(jìn)行插入操作:(學(xué)號，姓名) values ('0001'，劉衛(wèi)平)（學(xué)號，姓名，學(xué)費) values ('0001'，張衛(wèi)民,$120)(學(xué)號，姓名，學(xué)費，交費日期) values ('0001'，馬東，$110，2006-5-12)分析stu_fee表中插入記錄的各列的值是什么?INSERT INTO stu_fee(學(xué)號,姓名) VALUES('0001','劉衛(wèi)平')INSERT INTO stu_fee(學(xué)號,姓名,學(xué)費)VALUES('0001','張衛(wèi)民',$120)INSERT INTO stu_fee(學(xué)號,姓名,學(xué)費,交費日期) VALUES('0001','馬東',$110,'2006-5-12')各列值為：劉衛(wèi)平，張衛(wèi)民的交費日期為默認(rèn)值(5)完成以下代碼解除默認(rèn)對象df_char、df_date、df_money的綁定，并刪除之。sp_unbindefault 'stu_fee.電話號碼'DROP DEFAULT df_charsp_unbindefault 'stu_fee.交費日期'DROP DEFAULT df_datesp_unbindefault 'stu_fee.學(xué)費'DROP DEFAULT df_money6為學(xué)生表添加一列，命名為“院系”，創(chuàng)建一個默認(rèn)對象stu_d_df，將其綁定到學(xué)生表的“院系”列上，使其默認(rèn)值為“信息院”，對學(xué)生表進(jìn)行插入操作，操作完成后，刪除該默認(rèn)對象。SELECT * FROM 學(xué)生表ALTER TABLE 學(xué)生表ADD 院系CHAR(8) NULLCREATE DEFAULT stu_d_df AS '信息院'sp_bindefault stu_d_df,'學(xué)生表.院系'INSERT INTO 學(xué)生表(學(xué)號,姓名) VALUES('3244','郭冰驊')sp_unbindefault '學(xué)生表.院系'DROP DEFAULT stu_d_df 7在studentsdb數(shù)據(jù)庫中用CREATE TABLE語句創(chuàng)建表stu_con，并同時創(chuàng)建約束。 (1)創(chuàng)建表的同時創(chuàng)建約束。表結(jié)構(gòu)如圖1-12所示。 圖1-12 要創(chuàng)建的表的結(jié)構(gòu)約束要求如下： 將學(xué)號設(shè)置為主鍵(PRIMARY KEY)，主鍵名為pk_sid。 為姓名添加唯一約束(UNIQUE)，約束名為uk_name。 為性別添加默認(rèn)約束(DEFAULT)，默認(rèn)名稱為df_sex，其值為“男” 為出生日期添加屬性值約束(CHECK)，約束名為ck_bday，其檢查條件為：出生日期>'1988-1-1。(2)在stu_con表中插入如表1-1所示的數(shù)據(jù)記錄。 表1-1 在stu_con表中插入的數(shù)據(jù)學(xué)號姓名性別出生日期家庭住址0009張小東1989-4-60010李梅男1983-8-50011王強(qiáng)1988-9-100012王強(qiáng)1989-6-3 分析各約束在插入記錄時所起的作用，查看插入記錄后表中數(shù)據(jù)與所插入的數(shù)據(jù)是否一致?CREATE TABLE stu_con (學(xué)號char(4) NOT NULL CONSTRAINT pk_sid PRIMARY KEY , 姓名char(8) NOT NULL CONSTRAINT uk_name UNIQUE, 學(xué)費money, 性別char(2) CONSTRAINT df_sex DEFAULT ('男'), 出生日期datetime CONSTRAINT ck_bday CHECK (出生日期>'1988-1-1'), 家庭住址char(50)INSERT INTO stu_con(學(xué)號,姓名,出生日期) VALUES('0009','張小東','1989-4-6')INSERT INTO stu_con(學(xué)號,姓名,性別,出生日期)VALUES('0010','李梅','男','1983-8-5')INSERT INTO stu_con(學(xué)號,姓名,出生日期) VALUES('0011','王強(qiáng)','1988-9-10')INSERT INTO stu_con(學(xué)號,姓名,出生日期) VALUES('0012','王強(qiáng)','1989-6-3')運行結(jié)果：(1 行受影響)消息547，級別16，狀態(tài)0，第2 行INSERT 語句與CHECK 約束"ck_bday"沖突。該沖突發(fā)生于數(shù)據(jù)庫"studentsdb"，表"dbo.stu_con", column '出生日期'。語句已終止。(1 行受影響)消息2627，級別14，狀態(tài)1，第4 行違反了UNIQUE KEY 約束'uk_name'。不能在對象'dbo.stu_con' 中插入重復(fù)鍵。語句已終止。(3)使用ALTER TABLE語句的DROP CONSTRAINT參數(shù)項在查詢分析器中刪除為stu_con表所建的約束。ALTER TABLE stu_conDROP CONSTRAINT pk_sidALTER TABLE stu_conDROP CONSTRAINT uk_nameALTER TABLE stu_conDROP CONSTRAINT df_sexALTER TABLE stu_conDROP CONSTRAINT ck_bday8用SQL Server管理平臺完成實驗內(nèi)容7的所有設(shè)置。出現(xiàn)問題！只能出現(xiàn)張小東的信息9. 在查詢分析器中，為studentsdb數(shù)據(jù)庫的成績表添加外鍵約束(FOREIGN KEY)，要求將“學(xué)號”設(shè)置為外鍵，參照表為學(xué)生表，外鍵名稱為fk_sid。ALTER TABLE 成績表ADD CONSTRAINT fk_sidFOREIGN KEY(學(xué)號) references 學(xué)生表(學(xué)號)注意：學(xué)生表里的學(xué)號一定要與成績表中的學(xué)號對應(yīng)，否則會報錯沖突使用系統(tǒng)存儲過程sp_help查看grade表的外鍵信息。sp_help 成績表 在成績表中插入表1-2所示記錄，觀察SQLServer會做何處理，為什么?如何解決所產(chǎn)生的問題?表1-2學(xué)號課程編號分?jǐn)?shù)0100000178INSERT INTO 成績表(學(xué)號,課程編號,分?jǐn)?shù)) VALUES('0010','0001','78')出現(xiàn)問題：消息547，級別16，狀態(tài)0，第2 行INSERT 語句與FOREIGN KEY 約束"fk_sid"沖突。該沖突發(fā)生于數(shù)據(jù)庫"studentsdb"，表"dbo.學(xué)生表", column '學(xué)號'。語句已終止。解決問題：在學(xué)生表中先插入一個學(xué)號為0010的學(xué)生信息使用查詢分析器刪除成績表的外鍵fk_sid。ALTER table 成績表DROP CONSTRAINT fk_sid四、實驗思考1在SQLServer 2005中，可采用哪些方法實現(xiàn)數(shù)據(jù)完整性?答：數(shù)據(jù)類型、主鍵、外鍵、默認(rèn)值、約束與規(guī)則2，比較默認(rèn)對象和默認(rèn)約束的異同。答：相同點：默認(rèn)對象與默認(rèn)約束功能相似。默認(rèn)值可以在沒有指定具體數(shù)據(jù)的列中自動插入數(shù)據(jù)。默認(rèn)約束是通過定義列的默認(rèn)值或使用數(shù)據(jù)庫的默認(rèn)值對象綁定表的列，以確保在沒有為某列指定數(shù)據(jù)時，來指定列的值。每列只能有一個默認(rèn)約束一個默認(rèn)值不同點：默認(rèn)對象與CREAT TABLE 和ALTER TABLE語句一起使用，而默認(rèn)約束只能用于INSERT語句。3.在數(shù)據(jù)庫中建立的規(guī)則不綁定到到數(shù)據(jù)表的字段上會起作用嗎？為什么？ 答：不會規(guī)則僅僅只是一個存在與數(shù)據(jù)庫中的對象，并未發(fā)生作用。14南京師范大學(xué)中北學(xué)院