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（外?。┘s束 6、DEFAULT（默認）約束,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)建默認約束 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)建主鍵約束,同步案例參見實訓教程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)建主鍵約束,同步案例參見實訓教程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)建學生成績表score2,結(jié)構(gòu)如下:,20,6.3.2 創(chuàng)建唯一約束,同步案例參見實訓教程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語句在“學生”數(shù)據(jù)庫中，為“學生 成績”表中的“Sscore”字段創(chuàng)建一個檢查約 束，以保證輸入的數(shù)據(jù)大于等于0而小于等于 100。T-SQL語句如下：ALTER TABLE 學生成績ADD CONSTRAINT ck_chengji CHECK

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

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

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

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

22、默認值是沒有意義的常量值外面可以加或者不加括號，字符或者日期常量必須加上單引號或雙引號,30,6.3.4 創(chuàng)建默認約束,如果在一個 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語句在“學生”數(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)建外鍵約束,同步案例參見實訓教程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、當給一個表添加約束的時候，SQL Server 將檢查現(xiàn)有數(shù)據(jù)是否違反約束建議創(chuàng)建約束的時候指定名稱，否則系統(tǒng)將為約束自動產(chǎn)生一個復(fù)雜的名稱名稱必須惟一，且符合 SQL Server 標識符的規(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