第05章SQL數(shù)據(jù)定義與完整性約束的實(shí)現(xiàn)-副本

1、SQL的表定義和完整性定義功能SQL數(shù)據(jù)操作與完整性約束的作用5.1.1 定義架構(gòu)5.1.2 定義表及其完整性約束5.1.3 修改表結(jié)構(gòu)的命令CREATE SCHEMA 倉(cāng)儲(chǔ)CREATE SCHEMA 訂貨CREATE SCHEMA 基礎(chǔ)CREATE TABLE命令的基本格式列的定義計(jì)算列的定義表級(jí)約束5.1.2 定義表CREATE TABLE ( , , , ) 給出要?jiǎng)?chuàng)建的基本表的名稱； 給出列名或字段名； 為列指定數(shù)據(jù)為列指定數(shù)據(jù)類型及其數(shù)據(jù)類型及其數(shù)據(jù)寬度；寬度； 關(guān)系數(shù)據(jù)庫(kù)支關(guān)系數(shù)據(jù)庫(kù)支持非常豐富的持非常豐富的數(shù)據(jù)類型，不數(shù)據(jù)類型，不同的數(shù)據(jù)庫(kù)管同的數(shù)據(jù)庫(kù)管理系統(tǒng)支持的理系統(tǒng)支持的數(shù)

2、據(jù)類型基本數(shù)據(jù)類型基本是一樣的，在是一樣的，在描述時(shí)稍有不描述時(shí)稍有不同。同。 用于定義列或字段一級(jí)的完整性約束，一般包括：用于定義列或字段一級(jí)的完整性約束，一般包括：lNOT NULL和和NULL約束約束lPRIMARY KEY約束約束 lUNIQUE約束約束lFOREIGN KEY約束約束lCHECK約束約束 lDEFAULT定義定義用于定義表一級(jí)的完整性約束，一般包括：用于定義表一級(jí)的完整性約束，一般包括：lPRIMARY KEY約束（復(fù)合屬性構(gòu)成的主關(guān)鍵字說(shuō)約束（復(fù)合屬性構(gòu)成的主關(guān)鍵字說(shuō)明）明）例如：PRIMARY KEY(學(xué)號(hào)，課程號(hào))lFOREIGN KEY約束（外部關(guān)鍵字及參照關(guān)

3、系說(shuō)明）約束（外部關(guān)鍵字及參照關(guān)系說(shuō)明）例如： FOREIGN KEY(學(xué)號(hào)) REFERENCES 學(xué)生(學(xué)號(hào))lCHECK約束（同時(shí)涉及到多個(gè)屬性的域完整性約約束（同時(shí)涉及到多個(gè)屬性的域完整性約束）束） 例如：check (起始日期終止日期）說(shuō)明外部關(guān)鍵字，F(xiàn)OREIGN KEY可以省略什么是外部關(guān)鍵字？外部關(guān)鍵字的作用？ schema_name . referenced_table_name ( ref_column ) 指出參照的表和列，默認(rèn)模式時(shí)schema_name可以省略，列名相同時(shí)( ref_column )可以省略column_name NULL | NOT NULL CON

4、STRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression ON DELETE和

5、ON UPDATE說(shuō)明在執(zhí)行刪除和更新操作時(shí)如何處理參照完整性刪除操作檢查的完整性？更新操作檢查的完整性？column_name NULL | NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL

6、 | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression 其中column_name給出計(jì)算列的列名，computed_column_expression是對(duì)應(yīng)的計(jì)算表達(dá)式，PERSISTED說(shuō)明物理存儲(chǔ)計(jì)算列的值。其他選項(xiàng)和定義實(shí)列時(shí)類似。column_name AS computed_column_expression PERSISTED NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES

7、referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE ON UPDATE NO ACTION | CHECK ( logical_expression ) 要求：倉(cāng)庫(kù)表屬于倉(cāng)儲(chǔ)架構(gòu)，包含: 倉(cāng)庫(kù)號(hào)（主關(guān)鍵字） 城市 面積（倉(cāng)庫(kù)的面積值是大于0的整數(shù)）CREATE TABLE 倉(cāng)儲(chǔ).倉(cāng)庫(kù)(倉(cāng)庫(kù)號(hào) CHAR(6) ,城市 CHAR(10) ,面積 INT)PRIMARY KEYCHECK (面積 0) 要求：職工表屬于基礎(chǔ)架構(gòu)，包含： 倉(cāng)庫(kù)號(hào)（參照倉(cāng)庫(kù)表的倉(cāng)庫(kù)號(hào)字段值, 外鍵）職工號(hào)（主關(guān)鍵字）姓名工資（1000到50

8、00之間，默認(rèn)是1200班組長(zhǎng)（參照本關(guān)系的職工號(hào)屬性）CREATE TABLE 基礎(chǔ).職工(倉(cāng)庫(kù)號(hào) CHAR(6) CONSTRAINT ref_wh FOREIGN KEY REFERENCES 倉(cāng)儲(chǔ).倉(cāng)庫(kù)(倉(cāng)庫(kù)號(hào)),職工號(hào) CHAR(8) PRIMARY KEY,姓名 CHAR(10),工資 numeric(8,2) CHECK (工資= 1000 AND 工資=0),PRIMARY KEY(倉(cāng)庫(kù)號(hào),器件號(hào))要求：供應(yīng)商表屬于訂貨架構(gòu)，它包含：供應(yīng)商號(hào)（主關(guān)鍵字）供應(yīng)商名地址CREATE TABLE 訂貨.供應(yīng)商(供應(yīng)商號(hào) CHAR(5) PRIMARY KEY,供應(yīng)商名 CHAR(20

9、),地址 CHAR(20)要求：訂購(gòu)單表屬于訂貨架構(gòu)，它包含：訂購(gòu)單號(hào)（主關(guān)鍵字）經(jīng)手人（非空，參照職工關(guān)系的職工）供貨方（參照供應(yīng)商關(guān)系的供應(yīng)商號(hào)）訂購(gòu)日期（默認(rèn)值是系統(tǒng)的當(dāng)前日期）金額CREATE TABLE 訂貨.訂購(gòu)單(訂購(gòu)單號(hào) CHAR(5) PRIMARY KEY,經(jīng)手人 CHAR(8) NOT NULL FOREIGN KEY REFERENCES 基礎(chǔ).職工(職工號(hào)),供貨方 CHAR(5) NULL FOREIGN KEY REFERENCES 訂貨.供應(yīng)商(供應(yīng)商號(hào)),訂購(gòu)日期 DATETIME DEFAULT getdate(),金額 MONEY NULL)要求：訂購(gòu)明細(xì)

10、表屬于訂貨架構(gòu)，它包含：訂購(gòu)單號(hào)（參照訂購(gòu)單關(guān)系的訂購(gòu)單號(hào)）序號(hào)（大于等于1）器件號(hào)（參照器件表的器件號(hào)）單價(jià)數(shù)量（大于等于0）。其中主關(guān)鍵字由訂購(gòu)單號(hào)和序號(hào)共同構(gòu)成CREATE TABLE 訂貨.訂購(gòu)明細(xì)(訂購(gòu)單號(hào) CHAR(5) FOREIGN KEY REFERENCES 訂貨.訂購(gòu)單ON DELETE CASCADE ON UPDATE CASCADE,序號(hào) SMALLINT CHECK (序號(hào)=1),器件號(hào) CHAR(6) FOREIGN KEY REFERENCES 基礎(chǔ).器件,單價(jià) SMALLMONEY,數(shù)量 INT CONSTRAINT num CHECK (數(shù)量=0),PRI

11、MARY KEY (訂購(gòu)單號(hào),序號(hào)) )創(chuàng)建表時(shí)創(chuàng)建表時(shí)_列級(jí)列級(jí)創(chuàng)建表時(shí)創(chuàng)建表時(shí)_表級(jí)表級(jí)在已創(chuàng)建表上添加在已創(chuàng)建表上添加系統(tǒng)對(duì)完整性約束的檢查系統(tǒng)對(duì)完整性約束的檢查PRIMARY KEYPRIMARY KEY(只適合于單屬性主關(guān)鍵字只適合于單屬性主關(guān)鍵字)PRIMARY KEY(主關(guān)主關(guān)鍵字屬性集鍵字屬性集)ALTER TABLE ADD PRIMARY KEY(主主關(guān)鍵字屬性集關(guān)鍵字屬性集)INSERTUPDATEUNIQUEUNIQUEUNIQUE(列名集列名集)ALTER TABLE ADD UNIQUE(列名集列名集)INSERTUPDATEFOREIGN KEYFOREIGN K

12、EY(外部關(guān)鍵外部關(guān)鍵字字) REFERENCES 被參照被參照表表(主關(guān)鍵字主關(guān)鍵字)FOREIGN KEY(外部外部關(guān)鍵字關(guān)鍵字) REFERENCES 被參被參照表照表(主關(guān)鍵字主關(guān)鍵字)ALTER TABLE ADD FOREIGN KEY(外外部關(guān)鍵字部關(guān)鍵字) REFERENCES 被參照表被參照表(主關(guān)鍵字主關(guān)鍵字)參照表參照表:INSERTUPDATE被參照表被參照表:DELETE（4種選擇）種選擇）UPDATE （4種選擇）種選擇）DEFAULT DEFAULT 默認(rèn)值默認(rèn)值A(chǔ)LTER TABLE ADD DEFAULT 默認(rèn)值默認(rèn)值 FOR 列名列名INSERTCHECKC

13、HECK(邏輯表達(dá)式邏輯表達(dá)式)(只適合于單列只適合于單列)CHECK(邏輯表達(dá)式邏輯表達(dá)式) ALTER TABLE ADD CHECK(邏輯表達(dá)邏輯表達(dá)式式)INSERTUPDATEALTER TABLE schema_name.table_name ALTER COLUMN column_name NULL | NOT NULL | ADD | | | DROP CONSTRAINT constraint_name | COLUMN column_name ALTER COLUMN修改已有列的定義，但是只能修改為兼容數(shù)據(jù)類型或重新定義是否允許空值； ADD添加新列、或新的計(jì)算列或表級(jí)完整

14、性約束； ALTER TABLE schema_name.table_name ALTER COLUMN column_name NULL | NOT NULL | ADD | | | DROP CONSTRAINT constraint_name | COLUMN column_name DROP刪除指定的完整性約束或指定的列。 ALTER TABLE schema_name.table_name ALTER COLUMN column_name NULL | NOT NULL | ADD | | | DROP CONSTRAINT constraint_name | COLUMN colu

15、mn_name 刪除職工表上定義的約束ref_wh。ALTER TABLE 基礎(chǔ).職工DROP CONSTRAINT ref_wh如果在定義表和約束的時(shí)候沒(méi)有用CONSTRAINT短語(yǔ)指定約束名稱，系統(tǒng)將自動(dòng)生成約束名稱。為職工表的倉(cāng)庫(kù)號(hào)字段添加約束，說(shuō)明該字段是外部關(guān)鍵字，同時(shí)規(guī)定當(dāng)被參照記錄被刪除時(shí)，將參照記錄的倉(cāng)庫(kù)號(hào)字段值置為空值（即空值刪除），當(dāng)被參照記錄的倉(cāng)庫(kù)號(hào)修改時(shí)級(jí)聯(lián)修改參照記錄的倉(cāng)庫(kù)號(hào)字段值（即級(jí)聯(lián)更新）。ALTER TABLE 基礎(chǔ).職工ADD CONSTRAINT ref_whFOREIGN KEY (倉(cāng)庫(kù)號(hào)) REFERENCES 倉(cāng)儲(chǔ).倉(cāng)庫(kù)(倉(cāng)庫(kù)號(hào))ON DELETE

16、SET NULL ON UPDATE CASCADE為訂購(gòu)明細(xì)表增加一個(gè)完成日期字段：ALTER TABLE 訂貨.訂購(gòu)明細(xì)ADD 完成日期 datetime 刪除剛為訂購(gòu)明細(xì)表增加的完成日期字段：ALTER TABLE 訂貨.訂購(gòu)明細(xì) DROP COLUMN 完成日期 為訂購(gòu)單表增加一個(gè)完成日期字段，默認(rèn)值是空值NULL：ALTER TABLE 訂貨.訂購(gòu)單 ADD 完成日期 datetime DEFAULT NULL為訂購(gòu)明細(xì)表增加一個(gè)計(jì)算字段，字段名為金額，計(jì)算表達(dá)式為單價(jià)*數(shù)量：ALTER TABLE 訂貨.訂購(gòu)明細(xì) ADD 金額 AS 單價(jià)*數(shù)量插入操作及其完整性約束刪除操作及其完整

17、性約束更新操作及其完整性約束SQL的插入語(yǔ)句是INSERT，常用格式：INSERT INTO schema_name.table_name ( column_list ) VALUES(expression | DEFAULT | NULL ,.n ) schema_nameschema_name指出模式名指出模式名 table_nametable_name指出表名指出表名column_listcolumn_list給出插入操作所涉及列的列表（默認(rèn)是表的全部列）給出插入操作所涉及列的列表（默認(rèn)是表的全部列）expression | DEFAULT | NULL ,.n expression |

18、 DEFAULT | NULL ,.n 給出對(duì)應(yīng)于給出對(duì)應(yīng)于column_listcolumn_list的各個(gè)列的值的各個(gè)列的值 例5-12：插入倉(cāng)庫(kù)關(guān)系的第1條記錄。INSERT INTO 倉(cāng)儲(chǔ).倉(cāng)庫(kù) VALUES(WH1,北京,500)或INSERT INTO 倉(cāng)儲(chǔ).倉(cāng)庫(kù)(倉(cāng)庫(kù)號(hào),城市,面積) VALUES(WH1,北京,500)CREATE TABLE 倉(cāng)儲(chǔ).倉(cāng)庫(kù)(倉(cāng)庫(kù)號(hào) CHAR(6) PRIMARY KEY,城市 CHAR(10),面積 INT CHECK (面積 0) )例5-14：插入職工關(guān)系的第1條記錄。INSERT INTO 基礎(chǔ).職工 VALUES(WH1, E2, 王月,

19、 1220, NULL)其中經(jīng)理屬性的NULL說(shuō)明該職工暫時(shí)沒(méi)有直接領(lǐng)導(dǎo)（班組長(zhǎng)）。CREATE TABLE 基礎(chǔ).職工(倉(cāng)庫(kù)號(hào) CHAR(6) CONSTRAINT ref_wh FOREIGN KEY REFERENCES 倉(cāng)儲(chǔ).倉(cāng)庫(kù)(倉(cāng)庫(kù)號(hào)),職工號(hào) CHAR(8) PRIMARY KEY,姓名 CHAR(10),工資 numeric(8,2) CHECK (工資= 1000 AND 工資= 1000 AND 工資 0) )例5-15：嘗試執(zhí)行如下命令：INSERT INTO 基礎(chǔ).職工 VALUES(WH7, E17, 張揚(yáng), 1250, E2)違背參照完整性約束CREATE TABL

20、E 基礎(chǔ).職工(倉(cāng)庫(kù)號(hào)倉(cāng)庫(kù)號(hào) CHAR(6) CONSTRAINT ref_wh FOREIGN KEY REFERENCES 倉(cāng)儲(chǔ)倉(cāng)儲(chǔ).倉(cāng)庫(kù)倉(cāng)庫(kù)(倉(cāng)庫(kù)號(hào)倉(cāng)庫(kù)號(hào)),職工號(hào) CHAR(8) PRIMARY KEY,姓名 CHAR(10),工資 numeric(8,2) CHECK (工資= 1000 AND 工資= 1000 AND 工資= 1000 AND 工資工資=0),PRIMARY KEY(倉(cāng)庫(kù)號(hào),器件號(hào))違背實(shí)體完整性約束例5-22：嘗試為主屬性指定空值的插入。嘗試執(zhí)行如下命令：INSERT INTO 倉(cāng)儲(chǔ).庫(kù)存 VALUES(WH1,NULL,18)違背實(shí)體完整性約束CREATE T

21、ABLE 倉(cāng)儲(chǔ).庫(kù)存(倉(cāng)庫(kù)號(hào) CHAR(6) FOREIGN KEY REFERENCES 倉(cāng)儲(chǔ).倉(cāng)庫(kù),器件號(hào) CHAR(6) FOREIGN KEY REFERENCES 基礎(chǔ).器件,數(shù)量 INT CHECK (數(shù)量=0),PRIMARY KEY(倉(cāng)庫(kù)號(hào),器件號(hào))CREATE TABLE 訂貨訂貨.訂購(gòu)明細(xì)訂購(gòu)明細(xì)(訂購(gòu)單號(hào)訂購(gòu)單號(hào) CHAR(5) FOREIGN KEY REFERENCES 訂貨訂貨.訂購(gòu)單訂購(gòu)單ON DELETE CASCADE ON UPDATE CASCADE,序號(hào)序號(hào) SMALLINT CHECK (序號(hào)序號(hào)=1),器件號(hào)器件號(hào) CHAR(6) FOREIGN K

22、EY REFERENCES 基礎(chǔ)基礎(chǔ).器件器件,單價(jià)單價(jià) SMALLMONEY,數(shù)量數(shù)量 INT CONSTRAINT num CHECK (數(shù)數(shù)量量=0),PRIMARY KEY (訂購(gòu)單號(hào)訂購(gòu)單號(hào),序號(hào)序號(hào)) )ALTER TABLE 訂貨訂貨.訂購(gòu)明細(xì)訂購(gòu)明細(xì) ADD 金額金額 AS 單價(jià)單價(jià)*數(shù)量數(shù)量例5-25：插入訂購(gòu)明細(xì)關(guān)系的第一條記錄。INSERT INTO 訂貨.訂購(gòu)明細(xì) VALUES(OR67, 1, P2, 120, 5, 600)正確的插入命令是：INSERT INTO 訂貨.訂購(gòu)明細(xì) VALUES(OR67, 1, P2, 120, 5)錯(cuò)誤SQL的刪除語(yǔ)句是DELET

23、E，常用格式：DELETE FROM schema_name.table_nameWHERE schema_nameschema_name指出模式名；指出模式名；table_nametable_name指出從哪個(gè)表刪除記錄；指出從哪個(gè)表刪除記錄；WHEREWHERE用來(lái)指出刪除記錄的條件，默認(rèn)是用來(lái)指出刪除記錄的條件，默認(rèn)是刪除全部記錄。刪除全部記錄。刪除操作只與參照完整性有關(guān)，并且只有在刪除被參照表的記錄時(shí)才需要檢查參照完整性，系統(tǒng)將根據(jù)定義參照完整性時(shí)確定的處理方法（拒絕刪除、空值刪除、默認(rèn)值刪除或級(jí)聯(lián)刪除）進(jìn)行處理。DELETE FROM 訂貨.訂購(gòu)明細(xì) WHERE 訂購(gòu)單號(hào)=OR91或

24、DELETE 訂貨.訂購(gòu)明細(xì) WHERE 訂購(gòu)單號(hào)=OR91即關(guān)鍵詞FROM可以省略。WHERE指定被刪除記錄滿足的條件，如果缺省WHERE短語(yǔ)將刪除全部記錄，所以在執(zhí)行刪除操作的時(shí)候要慎重。訂購(gòu)明細(xì)表不是任何表的被參照表，所以在訂購(gòu)明細(xì)表上的刪除操作不需要做數(shù)據(jù)完整性檢查。DELETE 訂貨.訂購(gòu)單 WHERE 訂購(gòu)單號(hào)=OR67DELETE FROM 基礎(chǔ).器件 WHERE 器件號(hào)=P2失敗！拒絕刪除失敗！拒絕刪除CREATE TABLE 倉(cāng)儲(chǔ).庫(kù)存(倉(cāng)庫(kù)號(hào) CHAR(6) FOREIGN KEY REFERENCES 倉(cāng)儲(chǔ).倉(cāng)庫(kù),器件號(hào) CHAR(6) FOREIGN KEY REFER

25、ENCES 基礎(chǔ).器件,數(shù)量 INT CHECK (數(shù)量=0),PRIMARY KEY(倉(cāng)庫(kù)號(hào),器件號(hào))DELETE 倉(cāng)儲(chǔ).倉(cāng)庫(kù) WHERE 倉(cāng)庫(kù)號(hào)=WH4ALTER TABLE 基礎(chǔ).職工ADD CONSTRAINT ref_whFOREIGN KEY (倉(cāng)庫(kù)號(hào)) REFERENCES 倉(cāng)儲(chǔ).倉(cāng)庫(kù)(倉(cāng)庫(kù)號(hào))ON DELETE SET NULL ON UPDATE CASCADECREATE TABLE 倉(cāng)儲(chǔ).庫(kù)存(倉(cāng)庫(kù)號(hào) CHAR(6) FOREIGN KEY REFERENCES 倉(cāng)儲(chǔ).倉(cāng)庫(kù),器件號(hào) CHAR(6) FOREIGN KEY REFERENCES 基礎(chǔ).器件,數(shù)量 INT CHECK (數(shù)量=0),PRIMARY KEY(倉(cāng)庫(kù)號(hào),器件號(hào))DELETE 倉(cāng)儲(chǔ).倉(cāng)庫(kù) WHERE 倉(cāng)庫(kù)號(hào)=WH3失??！失?。LTER TABLE 基礎(chǔ).職工ADD CONSTRAINT ref_whFOREIGN KEY (倉(cāng)庫(kù)號(hào)) REFERENCES 倉(cāng)儲(chǔ).倉(cāng)庫(kù)(倉(cāng)庫(kù)號(hào))ON DELETE SET NULL ON UPDATE CASCADECREATE TABLE 倉(cāng)儲(chǔ).庫(kù)存(倉(cāng)庫(kù)號(hào) CHAR(6) FOREIGN KEY REFERENCES 倉(cāng)儲(chǔ).倉(cāng)庫(kù),器件號(hào) CHAR(6) FOREIGN KEY REFERENCES 基礎(chǔ).器