sql第6章—約束

1、1,第6章 數(shù)據(jù)完整性,6.1 完整性的概念 6.2 約束的類(lèi)型 6.3 約束的創(chuàng)建 6.4 查看約束的定義 6.5 刪除約束,2,6.1 完整性的概念,完整性的概念 數(shù)據(jù)完整性指存儲(chǔ)在數(shù)據(jù)庫(kù)中的數(shù)據(jù)正確無(wú)誤并且相關(guān)數(shù)據(jù)具有一致性。數(shù)據(jù)完整性可分為以下四種：1、實(shí)體完整性：要求在表中不能存在兩條完全相同的記錄。實(shí)現(xiàn)實(shí)體完整性的方法有：主鍵約束、惟一索引、唯一約束、指定IDENTITY屬性。,3,6.1 完整性的概念,2、

2、域完整性：要求向表中指定列輸入的數(shù)據(jù)必須具有正確的數(shù)據(jù)類(lèi)型、格式及有效的數(shù)據(jù)范圍。實(shí)現(xiàn)域完整性的方法有：檢查約束、外鍵約束、非空約束、規(guī)則及在建表時(shí)設(shè)置的數(shù)據(jù)類(lèi)型。3、參照完整性：指作用于有關(guān)聯(lián)的表通過(guò)主鍵和外鍵或主鍵和惟一鍵間的關(guān)系，使表中的鍵值在相關(guān)表中保持一致。實(shí)現(xiàn)參照完整性的方法有：外鍵約束4、用戶(hù)定義的完整性：指應(yīng)用領(lǐng)域需要遵守的約束條件。,4,第6章 數(shù)據(jù)完整性,6.1 完整性的概念 6.2 約束的類(lèi)型 6.3

3、約束的創(chuàng)建 6.4 查看約束的定義 6.5 刪除約束,5,6.2 約束的類(lèi)型,1、NOT NULL（非空）約束 2、 PRIMARY KEY（主鍵）約束3、 UNIQUE（惟一）約束4、 CHECK（檢查）約束 5、FOREIGN KEY（外?。┘s束 6、DEFAULT（默認(rèn)）約束,6,第6章 數(shù)據(jù)完整性,6.1 完整性的概念 6.2 約束的類(lèi)型 6.3 約束的創(chuàng)建 6.4 查看約束的定義 6.5 刪除約束,7,

4、6.3 約束的創(chuàng)建,使用 CREATE TABLE 或者 ALTER TABLECREATE TABLE 是在創(chuàng)建表時(shí)創(chuàng)建約束ALTER TABLE 是在一個(gè)已有的表上添加約束可添加單列或多列約束若約束應(yīng)用于單列，稱(chēng)為列級(jí)約束若約束引用了多列，稱(chēng)為表級(jí)約束，一般此類(lèi)約束都是在表創(chuàng)建完成后再進(jìn)行添加約束.,8,6.3 約束的創(chuàng)建,CREATE TABLE table_name    ({

5、 column_name data_type }[ ...n ] }             [ [ DEFAULT constant_expression ] ]   < [ CONSTRAINT constraint_name ]   

6、; {  | [ { PRIMARY KEY | UNIQUE }            [ CLUSTERED | NONCLUSTERED ]           

7、0;| [ [ FOREIGN KEY ]            REFERENCES ref_table [ ( ref_column ) ]            [ ON DELETE {

8、CASCADE | NO ACTION } ]            [ ON UPDATE { CASCADE | NO ACTION } ]            | CHECK] 

9、 ( logical_expression ) },9,6.3 約束的創(chuàng)建,6.3.1 創(chuàng)建主鍵約束 6.3.2 創(chuàng)建惟一約束 6.3.3 創(chuàng)建檢查約束 6.3.4 創(chuàng)建默認(rèn)約束 6.3.5 創(chuàng)建外鍵約束,10,6.3.1 創(chuàng)建主鍵約束,1） 創(chuàng)建主鍵可以在建表時(shí)使用CREATE TABLE 命令完成。例1：建立一個(gè)民族表（民族代碼，民族名稱(chēng)），將民族代碼指定為主鍵。其程序清單如下：CREAT

10、E TABLE native(native_id char(2) CONSTRAINT pk_mzdm PRIMARY KEY, native_name varchar(30) NOT NULL） GO,11,6.3.1 創(chuàng)建主鍵約束,同步案例參見(jiàn)實(shí)訓(xùn)教程p143頁(yè)[同步任務(wù)1],12,6.3.1 創(chuàng)建主鍵約束,2） 為已存在的表創(chuàng)建主鍵約束，其語(yǔ)法格式如下： ALTER TABLE table_na

11、me ADD CONSTRAINT constraint_name PRIMARY KEY [CLUSTERED|NONCLUSTERED]{(column[,…n])},13,6.3.1 創(chuàng)建主鍵約束,例2-1：創(chuàng)建教師表teacher：,14,6.3.1創(chuàng)建主鍵約束,例2-2：修改教師表teacher,在該表中設(shè)置教師編號(hào)為主鍵 ： ALTER TABLE teacher ADD CONSTRAINT pk_

12、bh PRIMARY KEY(TID);,在為表添加主鍵約束時(shí),如該表已存在數(shù)據(jù),則要求主鍵列不能具有相同的值,也不能存在NULL值,否則將添加失敗.,15,6.3.1 創(chuàng)建主鍵約束,同步案例參見(jiàn)實(shí)訓(xùn)教程p143頁(yè)[同步任務(wù)2],16,6.3.1 創(chuàng)建主鍵約束,PRIMARY KEY 約束的注意事項(xiàng)每張表只能有一個(gè) PRIMARY KEY 約束輸入的值必須是惟一的不允許空值將在指定列上創(chuàng)建唯一索引,17,6.3.2 創(chuàng)建唯

13、一約束,1）創(chuàng)建表時(shí)創(chuàng)建唯一約束，其語(yǔ)法格式如下：CREATE TABLE table_name (column_name data_type CONSTRAINT constraint_name UNIQUE) 2) 為存在的表創(chuàng)建唯一約束，其語(yǔ)法格式如下： ALTER TABLE table_nameADD CONSTRAINT constraint_nameUNIQUE [CLUSTERED|NONC

14、LUSTERED] {(column[,…n])},18,6.3.2 創(chuàng)建唯一約束,例3-1：為“民族”native表中的“民族名稱(chēng)” native_name 字段創(chuàng)建一個(gè)唯一約束。其程序清單如下： ALTER TABLE native ADD CONSTRAINT uk_mzmz UNIQUE (native_name ) GO,1

15、9,6.3.2 創(chuàng)建唯一約束,例3-2:創(chuàng)建學(xué)生成績(jī)表score2,結(jié)構(gòu)如下:,20,6.3.2 創(chuàng)建唯一約束,同步案例參見(jiàn)實(shí)訓(xùn)教程p144頁(yè)[同步任務(wù)3],21,6.3.2 創(chuàng)建唯一約束,UNIQUE 約束的注意事項(xiàng)：允許一個(gè)空值在一個(gè)表上允許多個(gè) UNIQUE 約束可在一個(gè)或者多個(gè)列上定義是通過(guò)一個(gè)唯一索引強(qiáng)制約束的,22,6.3.3 創(chuàng)建檢查約束,1）創(chuàng)建表時(shí)創(chuàng)建檢查約束，其語(yǔ)法格式如下： CREATE TA

16、BLE table_name (column_name data_type CONSTRAINT constraint_name CHECK(logical_expression) )2）使用SQL語(yǔ)句為已存在的表創(chuàng)建檢查約束，其語(yǔ)法格式如下： ALTER TABLE table_name ADD CONSTRAINT constraint_name

17、 CHECK (logical_expression),23,6.3.3 創(chuàng)建檢查約束,例4：使用T-SQL語(yǔ)句在“學(xué)生”數(shù)據(jù)庫(kù)中，為“學(xué)生 成績(jī)”表中的“Sscore”字段創(chuàng)建一個(gè)檢查約 束，以保證輸入的數(shù)據(jù)大于等于0而小于等于 100。T-SQL語(yǔ)句如下：ALTER TABLE 學(xué)生成績(jī)ADD CONSTRAINT ck_chengji CHECK

18、 (Sscore>=0 and Sscore<=100),24,6.3.3 創(chuàng)建檢查約束,同步案例參見(jiàn)實(shí)訓(xùn)教程p144頁(yè)[同步任務(wù)4-1] [同步任務(wù)4-2],25,6.3.3 創(chuàng)建檢查約束,CHECK 約束的注意事項(xiàng)：在每次執(zhí)行 INSERT 或者 UPDATE 語(yǔ)句的時(shí)候校驗(yàn)數(shù)據(jù)值可以引用同表中的其他列,但不能引用其他表中的列

19、不能包含子查詢(xún)列級(jí) CHECK 約束可省略名字，讓系統(tǒng)自動(dòng)生成表達(dá)式可以用 AND 以及 OR 連接以表示復(fù)雜邏輯CHECK 約束中可使用系統(tǒng)函數(shù),26,6.3.4 創(chuàng)建默認(rèn)約束,1）使用SQL語(yǔ)句為已存在的表創(chuàng)建默認(rèn)約束，其語(yǔ)法格式如下： ALTER TABLE table_name ADD CONSTRAINT constraint_name DE

20、FAULT constant_expression [FOR column_name],27,6.3.4 創(chuàng)建默認(rèn)約束,例5： 教師表上，為學(xué)歷字段tdegree創(chuàng)建一個(gè)默認(rèn)約束，其默認(rèn)值為‘本科’。T-SQL語(yǔ)句如下： ALTER TABLE teacher ADD CONSTRAINT df_xueli DEFAULT ‘本科’

21、 FOR tdegree GO,28,6.3.3 創(chuàng)建默認(rèn)約束,同步案例參見(jiàn)實(shí)訓(xùn)教程p144頁(yè)[同步任務(wù)5],29,6.3.4 創(chuàng)建默認(rèn)約束,DEFAULT 約束的注意事項(xiàng)：DEFAULT 約束創(chuàng)建時(shí)將檢查表中的現(xiàn)存數(shù)據(jù)DEFAULT 約束只對(duì) INSERT 語(yǔ)句有效每列只能定義一個(gè) DEFAULT 約束不能和“標(biāo)識(shí)”屬性共同使用為具有 PRIMARY KEY 或 UNIQUE 約束的列指定

22、默認(rèn)值是沒(méi)有意義的常量值外面可以加或者不加括號(hào)，字符或者日期常量必須加上單引號(hào)或雙引號(hào),30,6.3.4 創(chuàng)建默認(rèn)約束,如果在一個(gè) INSERT 語(yǔ)句中對(duì)一個(gè)列進(jìn)行如下操作：,31,6.3.5 創(chuàng)建外鍵約束,1）使用SQL語(yǔ)句創(chuàng)建外鍵約束的語(yǔ)法格式為： ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN

23、 KEY (column_name[,…]) REFERENCES ref_table [ ( ref_column_name[,…] ) ],32,6.3.5 創(chuàng)建外鍵約束,例6：在student表上，增加一個(gè)民族字段,并為該字段 一個(gè)外鍵約束，從而保證輸入有效的民族代碼。T-SQL語(yǔ)句如下： ALTER TABLE student Add

24、 native_id char(2); ALTER TABLE student ADD CONSTRAINT fk_mzdm FOREIGN KEY (native_id) REFERENCES native(native_id); GO,33,6.3.5 創(chuàng)建外鍵約束,例7：使用T-SQL語(yǔ)句在“學(xué)生”數(shù)據(jù)庫(kù)中，為“班級(jí)”表中的 “專(zhuān)業(yè)代碼”

25、字段創(chuàng)建一個(gè)外鍵約束，從而保證輸入有 效的專(zhuān)業(yè)代碼。使用T-SQL語(yǔ)句代碼如下：ALTER TABLE 班級(jí)ADD CONSTRAINT fk_zydm FOREIGN KEY (專(zhuān)業(yè)代碼)REFERENCES 專(zhuān)業(yè)(專(zhuān)業(yè)代碼),34,6.3.5 創(chuàng)建外鍵約束,同步案例參見(jiàn)實(shí)訓(xùn)教程p145頁(yè)[同步任務(wù)6],35,6.3.5 創(chuàng)建外鍵約束,FOREIGN KEY 約束的注意事項(xiàng)：提供了單列或多列的引用完

26、整性。FOREIGN KEY 子句中指定的列的個(gè)數(shù)和數(shù)據(jù)類(lèi)型必須和 REFERENCES 子句中指定的列的個(gè)數(shù)和數(shù)據(jù)類(lèi)型匹配并不自動(dòng)創(chuàng)建索引修改數(shù)據(jù)的時(shí)候，用戶(hù)必須在被 FOREIGN KEY 約束引用的表上具有 SELECT 或 REFERENCES 權(quán)限若引用的是同表中的列，那么可只用 REFERENCES 子句而省略 FOREIGN KEY 子句,36,總結(jié)：使用約束的注意事項(xiàng),SQL Server 里的約束只是“最后防線(xiàn)”

27、當(dāng)給一個(gè)表添加約束的時(shí)候，SQL Server 將檢查現(xiàn)有數(shù)據(jù)是否違反約束建議創(chuàng)建約束的時(shí)候指定名稱(chēng)，否則系統(tǒng)將為約束自動(dòng)產(chǎn)生一個(gè)復(fù)雜的名稱(chēng)名稱(chēng)必須惟一，且符合 SQL Server 標(biāo)識(shí)符的規(guī)則,37,第6章 數(shù)據(jù)完整性,6.1 完整性的概念 6.2 約束的類(lèi)型 6.3 約束的創(chuàng)建 6.4 查看約束的定義 6.5 刪除約束,38,6. 4 查看約束信息,1) 使用SSMS查詢(xún)約束信息2) 系統(tǒng)存儲(chǔ)過(guò)程sp_help

28、用來(lái)查看約束的名稱(chēng)、創(chuàng)建者、 類(lèi)型和創(chuàng)建時(shí)間，其語(yǔ)法格式為： EXEC sp_help 約束名稱(chēng)3) 如果約束存在文本信息，可以使用sp_helptext來(lái)查看，其語(yǔ)法格式為： EXEC sp_helptext 約束名稱(chēng) 例8：使用系統(tǒng)存儲(chǔ)過(guò)程查看教師表上的約束ck_csrq 的文本信息。其程序清單如下： EXEC sp_helpte

29、xt ck_csrq,39,第6章 數(shù)據(jù)完整性,6.1 完整性的概念 6.2 約束的類(lèi)型 6.3 約束的創(chuàng)建 6.4 查看約束的定義 6.5 刪除約束,40,6.5 刪除約束,1) 使用SSMS刪除約束2) 使用DROP命令刪除表約束 其語(yǔ)法格式為： ALTER TABLE table_name DROP CONSTRAINT constraint_name[,…n