sql第6章—約束

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

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

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

4、6.3 約束的創(chuàng)建,使用 CREATE TABLE 或者 ALTER TABLECREATE TABLE 是在創(chuàng)建表時創(chuàng)建約束ALTER TABLE 是在一個已有的表上添加約束可添加單列或多列約束若約束應(yīng)用于單列，稱為列級約束若約束引用了多列，稱為表級約束，一般此類約束都是在表創(chuàng)建完成后再進行添加約束.,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)建主鍵可以在建表時使用CREATE TABLE 命令完成。例1：建立一個民族表（民族代碼，民族名稱），將民族代碼指定為主鍵。其程序清單如下：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)建主鍵約束,同步案例參見實訓(xùn)教程p143頁[同步任務(wù)1],12,6.3.1 創(chuàng)建主鍵約束,2） 為已存在的表創(chuàng)建主鍵約束，其語法格式如下： 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è)置教師編號為主鍵 ： ALTER TABLE teacher ADD CONSTRAINT pk_

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

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

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

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

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

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

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

19、不能包含子查詢列級 CHECK 約束可省略名字，讓系統(tǒng)自動生成表達(dá)式可以用 AND 以及 OR 連接以表示復(fù)雜邏輯CHECK 約束中可使用系統(tǒng)函數(shù),26,6.3.4 創(chuàng)建默認(rèn)約束,1）使用SQL語句為已存在的表創(chuàng)建默認(rèn)約束，其語法格式如下： 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)建一個默認(rèn)約束，其默認(rèn)值為‘本科’。T-SQL語句如下： ALTER TABLE teacher ADD CONSTRAINT df_xueli DEFAULT ‘本科’

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

22、默認(rèn)值是沒有意義的常量值外面可以加或者不加括號，字符或者日期常量必須加上單引號或雙引號,30,6.3.4 創(chuàng)建默認(rèn)約束,如果在一個 INSERT 語句中對一個列進行如下操作：,31,6.3.5 創(chuàng)建外鍵約束,1）使用SQL語句創(chuàng)建外鍵約束的語法格式為： 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表上，增加一個民族字段,并為該字段 一個外鍵約束，從而保證輸入有效的民族代碼。T-SQL語句如下： 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語句在“學(xué)生”數(shù)據(jù)庫中，為“班級”表中的 “專業(yè)代碼”

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

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

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

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

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