T-SQL開發

之所以把約束和索引放到一起來看，主要是因為主鍵約束和唯一鍵約束，它們會自動建立一個對應的索引，先分別看下資料庫中的幾個約束。

一 約束

在關係型資料庫裡，通常有5種約束，樣本如下：

use tempdbgocreate table s(sid     varchar(20),sname   varchar(20),ssex    varchar(2)  check(ssex='男' or ssex='女') default '男',sage    int         check(sage between 0 and 100),sclass  varchar(20) unique,constraint PK_s primary key (sid,sclass))create table t(teacher  varchar(20) primary key,sid      varchar(20) not null,sclass   varchar(20) not null,num      int,foreign key(sid,sclass) references s(sid,sclass))

單獨定義在某一列上的約束被稱為資料行層級條件約束，定義在多列上的約束則稱為表級約束。

1.主鍵約束

在表中的一列或者多列上，定義主鍵來唯一標識表中的資料行，也就是資料庫設計3範式裡的第2範式；

主鍵約束要求索引值唯一且不可為空：primary key = unique constraint + not null constraint

2.唯一鍵約束

唯一約束和主鍵約束的區別就是：允許NULL，SQL Server 中唯一鍵列，僅可以有一行為NULL，ORACLE中可以有多行列值為NULL。

一個表只能有一個主鍵，但可以有多個唯一鍵：unique index = unique constraint

在一個允許為NULL的列上，想要保證非NULL值的唯一性，該怎麼辦？

從SQL Server 2008開始，可以用篩選索引(filtered index)

use tempdbGOcreate table tb5(id int null)create unique nonclustered index un_ix_01on tb5(id)where id is not nullGO

3.外鍵約束

表中的一列或者多列，引用其他表的主鍵或者唯一鍵。外鍵定義如下：

use tempdbGO--drop table tb1,tb2create table tb1(col1 int Primary key,col2 int)insert into tb1 values (2,2),(3,2),(4,2),(5,2)GOcreate table tb2(col3 int primary key,col4 int constraint FK_tb2 foreign key  references tb1(col1))GOselect * from tb1select * from tb2select object_name(constraint_object_id) constraint_name,       object_name(parent_object_id) parent_object_name,       col_name(parent_object_id,parent_column_id) parent_object_column_name,       object_name(referenced_object_id) referenced_object_name,       col_name(referenced_object_id,referenced_column_id) referenced_object_column_name from sys.foreign_key_columnswhere referenced_object_id = object_id('tb1')

外鍵開發維護過程中，常見的問題及解決方案：

(1) 不能將主表中主鍵/唯一鍵的部分列作為外鍵，必須是全部列一起引用

create table tb3(c1 int,c2 int,c3 int,  constraint PK_tb3 primary key (c1,c2));                                                                                                                                      create table tb4(c4 int constraint FK_tb4 foreign key references tb3(c1),c5 int,c6 int);/*Msg 1776, Level 16, State 0, Line 1There are no primary or candidate keys in the referenced table 'tb3' that match the referencing column list in the foreign key 'FK_tb4'.Msg 1750, Level 16, State 0, Line 1Could not create constraint. See previous errors.*/

(2) 從表插入資料出錯

insert into tb2 values (1,1)/*Msg 547, Level 16, State 0, Line 1The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tb2". The conflict occurred in database "tempdb", table "dbo.tb1", column 'col1'.*/--從表在參照主表中的資料，可以先禁用外鍵(只是暫停約束檢查)alter table tb2 NOCHECK constraint FK_tb2alter table tb2 NOCHECK constraint ALL--從表插入資料後，再啟用外鍵insert into tb2 values (1,1),(3,3),(4,4)alter table tb2 CHECK constraint FK_tb2

(3) 主表刪除/更新資料出錯

--先刪除從表tb2的資料或禁用外鍵，才能刪除主表tb1中的值，否則報錯如下--未被引用的行可被直接刪除insert into tb2 values (2,2)delete from tb1GO/*Msg 547, Level 16, State 0, Line 3The DELETE statement conflicted with the REFERENCE constraint "FK_tb2". The conflict occurred in database "tempdb", table "dbo.tb2", column 'col4'.*/

(4) 清空/刪除主表出錯

--清空主表時，即便禁用外鍵，但外鍵關係依然存在，所以任然無法truncatetruncate table tb1/*Msg 4712, Level 16, State 1, Line 2Cannot truncate table 'tb1' because it is being referenced by a FOREIGN KEY constraint.*/--刪除主表也不行drop table tb1/*Msg 3726, Level 16, State 1, Line 2Could not drop object 'tb1' because it is referenced by a FOREIGN KEY constraint.*/--先truncate從表，再truncate主表也不行truncate table tb2truncate table tb1--唯一的辦法刪掉外鍵，truncate將不受控制alter table tb2 drop constraint FK_tb2truncate table tb1--最後再加上外鍵，注意with nocheck選項，因為主從表裡資料不一致了，所以不檢查約束，否則外鍵加不上alter table tb2 WITH NOCHECKadd constraint FK_tb2 foreign key(col4) references tb1(col1)

最後，雖然一個表上可以建立多個外鍵，但通常出於效能考慮，不推薦使用外鍵，資料參照完整性可以在程式裡完成；

4.CHECK約束

可定義運算式以檢查列值，通常出於效能考慮，不推薦使用。

5.NULL 約束

用於控制列是否允許為NULL。使用NULL時有幾個注意點：

(1) SQL SERVER中彙總函式是會忽略NULL值的；

(2) 字元型的欄位，如果not null，那這個欄位不能為null值，但可以為''，這是空串，和null是不一樣的；

(3) NULL值無法直接參与比較/運算；

declare @c varchar(100)set @c = nullif @c<>'abc' or @c  = 'abc'    print 'null'else    print 'I donot know'GOdeclare @i intset @i = nullprint @i + 1

在開發過程中，NULL會帶來3值邏輯，不推薦使用，對於可能為NULL的值可用預設值等來代替。

6.DEFAULT約束

從系統檢視表來看，default也是被SQL Server當成約束來管理的。

select * from sys.default_constraints

(1) 常量/運算式/純量涵式(系統，自訂、CLR函數)/NULL都可以被設定為預設值；

(2) 利用預設值，向表中添加一個NOT NULL的列，如下：

create table tb6(c1 int not null)insert into tb6 select 1alter table tb6 add c2 int default 35767 not nullselect * from tb6--在alter table完成前，表一直處於鎖定狀態；--如果向大型表添加列，對資料頁的操作需要一些時間，最好事先做好評估。

二 索引

定義約束時，並沒有定義資料庫實現約束的方法，目前的關係型資料庫系統，主鍵和唯一鍵約束藉助唯一索引來實現，所以在建立主鍵/唯一鍵時，都會自動產生一個同名的索引。

那麼由約束產生的唯一索引，和單獨建立的唯一索引有什麼聯絡和區別？

1.建立主鍵或唯一鍵約束時，資料庫自動建立唯一索引

自動產生的該索引是無法刪除的，因為這個索引要用於實現約束，在刪除約束的時候，該索引也被刪除。示範指令碼如下：

--create tableCREATE TABLE TEST_CONS(ID             int,CODE           varchar(100))--insert dataINSERT INTO TEST_CONSSELECT 1,'test1'--add unique constraintALTER TABLE TEST_CONS  ADD CONSTRAINT UQ_TEST_CONS_ID UNIQUE NONCLUSTERED(ID)--retrieve constraintSELECT *  FROM sys.objects WHERE parent_object_id = object_id('TEST_CONS') AND type = 'UQ'--查看約束，返回如下結果：/*name    object_idUQ_TEST_CONS_ID 1243151474*/--retrieve indexSELECT *  FROM sys.indexes WHERE object_id = object_id('TEST_CONS') AND type = 2  --2為非叢集索引--查看約束產生的索引，返回如下結果：/*object_id   name1227151417  UQ_TEST_CONS_ID*/--check constraintINSERT INTO TEST_CONSSELECT 1,'test1'--如果插入重複值提示：UNIQUE KEY 約束，返回如下錯誤：/*訊息，層級，狀態，第行違反了UNIQUE KEY 約束'UQ_TEST_CONS_ID'。不能在對象'dbo.TEST_CONS' 中插入重複鍵。*/ --drop index DROP INDEX UQ_TEST_CONS_ID ON TEST_CONS--如果刪除由約束產生的索引，返回如下錯誤：/*訊息，層級，狀態，第行不允許對索引'TEST_CONS.UQ_TEST_CONS_ID' 顯式地使用DROP INDEX。該索引正用於UNIQUE KEY 約束的強制執行。*/ --drop constraint ALTER TABLE TEST_CONS  DROP CONSTRAINT UQ_TEST_CONS_ID--如果刪除約束，索引也被刪除，以下查詢返回空結果集：--retrieve constraintSELECT *  FROM sys.objects WHERE parent_object_id = object_id('TEST_CONS') AND type = 'UQ'--retrieve indexSELECT *  FROM sys.indexes WHERE object_id = object_id('TEST_CONS') AND type = 2  --2為非叢集索引--drop tableDROP TABLE TEST_CONS

另外，約束產生的索引，有些屬性也是無法被修改的，比如：開關IGNORE_DUP_KEY，唯一的辦法是：先刪除約束，再重新定義約束/索引；單獨定義的索引，則沒有這個限制，如下例：

use tempdbGOcreate table tb_cons(ID int constraint pk_tb_cons primary key)create unique clustered index pk_tb_cons on tb_cons(id) with(DROP_EXISTING = ON, FILLFACTOR = 90)alter index pk_tb_cons on tb_cons rebuild with(IGNORE_DUP_KEY = ON)/*Msg 1979, Level 16, State 1, Line 1Cannot use index option ignore_dup_key to alter index 'pk_tb_cons' as it enforces a primary or unique constraint.*/exec sp_helpindex tb_cons--單獨建立的唯一索引，屬性可以隨意修改create unique index ix_tb_cons on tb_cons(id)alter index ix_tb_cons on tb_cons rebuild with(IGNORE_DUP_KEY = ON, ONLINE = ON)drop table tb_cons

在保證資料唯一性上，唯一索引、唯一約束並沒有區別，那麼應該使用約束還是索引？

約束定義通常出現在資料庫邏輯結構設計階段，即定義表結構時，索引定義通常出現在資料庫物理結構設計/查詢最佳化階段。

從功能上來說唯一約束和唯一索引沒有區別，但在資料庫維護上則不太一樣，對於唯一約束可以用唯一索引代替，以方便維護，但是主鍵約束則沒法代替。

2. 先建立唯一索引，再建立該索引欄位的唯一約束

這時資料庫並不會使用已存在的唯一索引，此時會提示已存在同名索引，約束建立失敗，如果指定不同名的約束，則會產生另個唯一索引。示範指令碼如下：

--create tableCREATE TABLE TEST_CONS(ID             int,CODE           varchar(100))--insert dataINSERT INTO TEST_CONSSELECT 1,'test1'--create indexCREATE UNIQUE INDEX UQ_TEST_CONS_IDON TEST_CONS(ID)--retrieve constraintSELECT *  FROM sys.objects WHERE parent_object_id = object_id('TEST_CONS') AND type = 'UQ'                                                                                             --retrieve indexSELECT *  FROM sys.indexes WHERE object_id = object_id('TEST_CONS') AND type = 2  --2為非叢集索引--check indexINSERT INTO TEST_CONSSELECT 1,'test1'--此時提示為：唯一索引/*訊息2601，層級14，狀態1，第1 行不能在具有唯一索引'UQ_TEST_CONS_ID' 的對象'dbo.TEST_CONS' 中插入重複鍵的行。*/--add constraintALTER TABLE TEST_CONS  ADD CONSTRAINT UQ_TEST_CONS_ID UNIQUE NONCLUSTERED(ID)--此時無法建立與索引同名的唯一約束，因為約束會去產生同名的索引/*訊息1913，層級16，狀態1，第2 行操作失敗，因為在表'TEST_CONS' 上已存在名稱為'UQ_TEST_CONS_ID' 的索引或統計資訊。訊息1750，層級16，狀態0，第2 行無法建立約束。請參閱前面的錯誤訊息。*/--add constraintALTER TABLE TEST_CONS  ADD CONSTRAINT UQ_TEST_CONS_ID_1 UNIQUE NONCLUSTERED(ID)--換個名字當然是可以成功的，但此時又產生了唯一索引UQ_TEST_CONS_ID_1--drop tableDROP TABLE TEST_CONS

3.主鍵是否是叢集索引？

SQL Server預設在定義主鍵時，將產生的唯一索引定義為聚集，剛剛接觸的時候容易被搞混淆了。主鍵對應的索引也可以非聚集，如下：

use tempdbGOcreate table test_pk(id int not null)alter table test_pk add constraint PK_test_pk primary key nonclustered(id);

SQL Server中定義主鍵時，預設產生叢集索引，唯一的好處是主鍵列範圍掃描/尋找的效率比較高，但資料插入效率欠佳(叢集索引，非叢集索引，都得被維護一次)，並且主鍵列如果選擇的不好，會影響其他非叢集索引的效能。

ORACLE中定義主鍵時，預設產生非叢集索引，不利於主鍵列的範圍掃描/尋找，但是對於資料插入效率更佳，這是不同資料庫產品各自的權衡。

本文出自 “SQL Server DBA” 部落格，請務必保留此出處http://qianzhang.blog.51cto.com/317608/1333464