之所以把約束和索引放到一起來看,主要是因?yàn)橹麈I約束和唯一鍵約束,它們會(huì)自動(dòng)創(chuàng)建一個(gè)對(duì)應(yīng)的索引,先分別看下數(shù)據(jù)庫(kù)中的幾個(gè)約束。
一 約束
在關(guān)系型數(shù)據(jù)庫(kù)里,通常有5種約束,示例如下:
use tempdb go create 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) )
單獨(dú)定義在某一列上的約束被稱為列級(jí)約束,定義在多列上的約束則稱為表級(jí)約束。
?
1.主鍵約束
在表中的一列或者多列上,定義主鍵來唯一標(biāo)識(shí)表中的數(shù)據(jù)行,也就是數(shù)據(jù)庫(kù)設(shè)計(jì)3范式里的第2范式;
主鍵約束要求鍵值唯一且不能為空:primary key = unique constraint + not null constraint
?
2.唯一鍵約束
唯一約束和主鍵約束的區(qū)別就是:允許NULL,SQL Server 中唯一鍵列,僅可以有一行為NULL,ORACLE中可以有多行列值為NULL。
?一個(gè)表只能有一個(gè)主鍵,但可以有多個(gè)唯一鍵:unique index = unique constraint
?
在一個(gè)允許為NULL的列上,想要保證非NULL值的唯一性,該怎么辦?
從SQL Server 2008開始,可以用篩選索引(filtered index)
use tempdb GO create table tb5 ( id int null ) create unique nonclustered index un_ix_01 on tb5(id) where id is not null GO
?
3.外鍵約束
表中的一列或者多列,引用其他表的主鍵或者唯一鍵。外鍵定義如下:
use tempdb GO -- drop table tb1,tb2 create table tb1 ( col1 int Primary key , col2 int ) insert into tb1 values ( 2 , 2 ),( 3 , 2 ),( 4 , 2 ),( 5 , 2 ) GO create table tb2 ( col3 int primary key , col4 int constraint FK_tb2 foreign key references tb1(col1) ) GO select * from tb1 select * from tb2 select 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_columns where referenced_object_id = object_id ( ' tb1 ' )
?
外鍵開發(fā)維護(hù)過程中,常見的問題及解決方法:
(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 1 There 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 1 Could not create constraint. See previous errors. */
?
(2) 從表插入數(shù)據(jù)出錯(cuò)
insert into tb2 values ( 1 , 1 ) /* Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tb2". The conflict occurred in database "tempdb", table "dbo.tb1", column 'col1'. */ -- 從表在參照主表中的數(shù)據(jù),可以先禁用外鍵(只是暫停約束檢查) alter table tb2 NOCHECK constraint FK_tb2 alter table tb2 NOCHECK constraint ALL -- 從表插入數(shù)據(jù)后,再啟用外鍵 insert into tb2 values ( 1 , 1 ),( 3 , 3 ),( 4 , 4 ) alter table tb2 CHECK constraint FK_tb2
?
(3) 主表刪除/更新數(shù)據(jù)出錯(cuò)
-- 先刪除從表tb2的數(shù)據(jù)或禁用外鍵,才能刪除主表tb1中的值,否則報(bào)錯(cuò)如下 -- 未被引用的行可被直接刪除 insert into tb2 values ( 2 , 2 ) delete from tb1 GO /* Msg 547, Level 16, State 0, Line 3 The DELETE statement conflicted with the REFERENCE constraint "FK_tb2". The conflict occurredin database "tempdb", table "dbo.tb2", column 'col4'. */
?
(4) 清空/刪除主表出錯(cuò)
-- 清空主表時(shí),即便禁用外鍵,但外鍵關(guān)系依然存在,所以任然無法truncate truncate table tb1 /* Msg 4712, Level 16, State 1, Line 2 Cannot truncate table 'tb1' because it is being referenced by a FOREIGN KEY constraint. */ -- 刪除主表也不行 drop table tb1 /* Msg 3726, Level 16, State 1, Line 2 Could not drop object 'tb1' because it is referenced by a FOREIGN KEY constraint. */ -- 先truncate從表,再truncate主表也不行 truncate table tb2 truncate table tb1 -- 唯一的辦法刪掉外鍵,truncate將不受控制 alter table tb2 drop constraint FK_tb2 truncate table tb1 -- 最后再加上外鍵,注意with nocheck選項(xiàng),因?yàn)橹鲝谋砝飻?shù)據(jù)不一致了,所以不檢查約束,否則外鍵加不上 alter table tb2 WITH NOCHECK add constraint FK_tb2 foreign key (col4) references tb1(col1)
? 最后,雖然一個(gè)表上可以創(chuàng)建多個(gè)外鍵,但通常出于性能考慮,不推薦使用外鍵,數(shù)據(jù)參照完整性可以在程序里完成;
?
4.CHECK約束
可定義表達(dá)式以檢查列值,通常出于性能考慮,不推薦使用。
?
5.NULL 約束
用于控制列是否允許為NULL。使用NULL時(shí)有幾個(gè)注意點(diǎn):
(1) SQL SERVER中聚合函數(shù)是會(huì)忽略NULL值的;
(2) 字符型的字段,如果not null,那這個(gè)字段不能為null值,但可以為'',這是空串,和null是不一樣的;
(3) NULL值無法直接參與比較/運(yùn)算;
declare @c varchar ( 100 ) set @c = null if @c <> ' abc ' or @c = ' abc ' print ' null ' else print ' I donot know ' GO declare @i int set @i = null print @i + 1
在開發(fā)過程中,NULL會(huì)帶來3值邏輯,不推薦使用,對(duì)于可能為NULL的值可用默認(rèn)值等來代替。
?
6.DEFAULT約束
從系統(tǒng)視圖來看,default也是被SQL Server當(dāng)成約束來管理的。
select * from sys.default_constraints
(1) 常量/表達(dá)式/標(biāo)量函數(shù)(系統(tǒng),自定義、CLR函數(shù))/NULL都可以被設(shè)置為默認(rèn)值;
(2) 利用默認(rèn)值,向表中添加一個(gè)NOT NULL的列,如下:
create table tb6(c1 int not null ) insert into tb6 select 1 alter table tb6 add c2 int default 35767 not null select * from tb6 -- 在alter table完成前,表一直處于鎖定狀態(tài); -- 如果向大型表添加列,對(duì)數(shù)據(jù)頁(yè)的操作需要一些時(shí)間,最好事先做好評(píng)估。
?
二 索引
定義約束時(shí),并沒有定義數(shù)據(jù)庫(kù)實(shí)現(xiàn)約束的方法,目前的關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng),主鍵和唯一鍵約束借助唯一索引來實(shí)現(xiàn),所以在創(chuàng)建主鍵/唯一鍵時(shí),都會(huì)自動(dòng)生成一個(gè)同名的索引。
那么由約束產(chǎn)生的唯一索引,和單獨(dú)創(chuàng)建的唯一索引有什么聯(lián)系和區(qū)別?
?
1.創(chuàng)建主鍵或唯一鍵約束時(shí),數(shù)據(jù)庫(kù)自動(dòng)創(chuàng)建唯一索引
自動(dòng)生成的該索引是無法刪除的,因?yàn)檫@個(gè)索引要用于實(shí)現(xiàn)約束,在刪除約束的時(shí)候,該索引也被刪除。演示腳本如下:
-- create table CREATE TABLE TEST_CONS ( ID int , CODE varchar ( 100 ) ) -- insert data INSERT INTO TEST_CONS SELECT 1 , ' test1 ' -- add unique constraint ALTER TABLE TEST_CONS ADD CONSTRAINT UQ_TEST_CONS_ID UNIQUE NONCLUSTERED (ID) -- retrieve constraint SELECT * FROM sys.objects WHERE parent_object_id = object_id ( ' TEST_CONS ' ) AND type = ' UQ ' -- 查看約束,返回如下結(jié)果: /* name object_id UQ_TEST_CONS_ID 1243151474 */ -- retrieve index SELECT * FROM sys.indexes WHERE object_id = object_id ( ' TEST_CONS ' ) AND type = 2 -- 2為非聚集索引 -- 查看約束產(chǎn)生的索引,返回如下結(jié)果: /* object_id name 1227151417 UQ_TEST_CONS_ID */ -- check constraint INSERT INTO TEST_CONS SELECT 1 , ' test1 ' -- 如果插入重復(fù)值提示:UNIQUE KEY 約束,返回如下錯(cuò)誤: /* 消息,級(jí)別,狀態(tài),第行 違反了UNIQUE KEY 約束'UQ_TEST_CONS_ID'。不能在對(duì)象'dbo.TEST_CONS' 中插入重復(fù)鍵。 */ -- drop index DROP INDEX UQ_TEST_CONS_ID ON TEST_CONS -- 如果刪除由約束產(chǎn)生的索引,返回如下錯(cuò)誤: /* 消息,級(jí)別,狀態(tài),第行 不允許對(duì)索引'TEST_CONS.UQ_TEST_CONS_ID' 顯式地使用DROP INDEX。該索引正用于UNIQUE KEY 約束的強(qiáng)制執(zhí)行。 */ -- drop constraint ALTER TABLE TEST_CONS DROP CONSTRAINT UQ_TEST_CONS_ID -- 如果刪除約束,索引也被刪除,以下查詢返回空結(jié)果集: -- retrieve constraint SELECT * FROM sys.objects WHERE parent_object_id = object_id ( ' TEST_CONS ' ) AND type = ' UQ ' -- retrieve index SELECT * FROM sys.indexes WHERE object_id = object_id ( ' TEST_CONS ' ) AND type = 2 -- 2為非聚集索引 -- drop table DROP TABLE TEST_CONS
?
另外,約束生成的索引,有些屬性也是無法被修改的,比如:開關(guān)IGNORE_DUP_KEY,唯一的辦法是:先刪除約束,再重新定義約束/索引;單獨(dú)定義的索引,則沒有這個(gè)限制,如下例:
use tempdb GO create 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 1 Cannot 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 -- 單獨(dú)創(chuàng)建的唯一索引,屬性可以隨意修改 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
在保證數(shù)據(jù)唯一性上,唯一索引、唯一約束并沒有區(qū)別,那么應(yīng)該使用約束還是索引?
約束定義通常出現(xiàn)在數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì)階段,即定義表結(jié)構(gòu)時(shí),索引定義通常出現(xiàn)在數(shù)據(jù)庫(kù)物理結(jié)構(gòu)設(shè)計(jì)/查詢優(yōu)化階段。
從功能上來說唯一約束和唯一索引沒有區(qū)別,但在數(shù)據(jù)庫(kù)維護(hù)上則不太一樣,對(duì)于唯一約束可以用唯一索引代替,以方便維護(hù),但是主鍵約束則沒法代替。
?
2. 先創(chuàng)建唯一索引,再創(chuàng)建該索引字段的唯一約束
這時(shí)數(shù)據(jù)庫(kù)并不會(huì)使用已存在的唯一索引,此時(shí)會(huì)提示已存在同名索引,約束創(chuàng)建失敗,如果指定不同名的約束,則會(huì)生成另個(gè)唯一索引。演示腳本如下:?
-- create table CREATE TABLE TEST_CONS ( ID int , CODE varchar ( 100 ) ) -- insert data INSERT INTO TEST_CONS SELECT 1 , ' test1 ' -- create index CREATE UNIQUE INDEX UQ_TEST_CONS_ID ON TEST_CONS(ID) -- retrieve constraint SELECT * FROM sys.objects WHERE parent_object_id = object_id ( ' TEST_CONS ' ) AND type = ' UQ ' -- retrieve index SELECT * FROM sys.indexes WHERE object_id = object_id ( ' TEST_CONS ' ) AND type = 2 -- 2為非聚集索引 -- check index INSERT INTO TEST_CONS SELECT 1 , ' test1 ' -- 此時(shí)提示為:唯一索引 /* 消息2601,級(jí)別14,狀態(tài)1,第1 行 不能在具有唯一索引'UQ_TEST_CONS_ID' 的對(duì)象'dbo.TEST_CONS' 中插入重復(fù)鍵的行。 */ -- add constraint ALTER TABLE TEST_CONS ADD CONSTRAINT UQ_TEST_CONS_ID UNIQUE NONCLUSTERED (ID) -- 此時(shí)無法創(chuàng)建與索引同名的唯一約束,因?yàn)榧s束會(huì)去生成同名的索引 /* 消息1913,級(jí)別16,狀態(tài)1,第2 行 操作失敗,因?yàn)樵诒?TEST_CONS' 上已存在名稱為'UQ_TEST_CONS_ID' 的索引或統(tǒng)計(jì)信息。 消息1750,級(jí)別16,狀態(tài)0,第2 行 無法創(chuàng)建約束。請(qǐng)參閱前面的錯(cuò)誤消息。 */ -- add constraint ALTER TABLE TEST_CONS ADD CONSTRAINT UQ_TEST_CONS_ID_1 UNIQUE NONCLUSTERED (ID) -- 換個(gè)名字當(dāng)然是可以成功的,但此時(shí)又生成了唯一索引UQ_TEST_CONS_ID_1 -- drop table DROP TABLE TEST_CONS
?
3.主鍵是否是聚集索引?
SQL Server默認(rèn)在定義主鍵時(shí),將生成的唯一索引定義為聚集,剛剛接觸的時(shí)候容易被搞混淆了。主鍵對(duì)應(yīng)的索引也可以非聚集,如下:
use tempdb GO create table test_pk(id int not null ) alter table test_pk add constraint PK_test_pk primary key nonclustered (id);
SQL Server中定義主鍵時(shí),默認(rèn)生成聚集索引,唯一的好處是主鍵列范圍掃描/查找的效率比較高,但數(shù)據(jù)插入效率欠佳(聚集索引,非聚集索引,都得被維護(hù)一次),并且主鍵列如果選擇的不好,會(huì)影響其他非聚集索引的性能。
ORACLE中定義主鍵時(shí),默認(rèn)生成非聚集索引,不利于主鍵列的范圍掃描/查找,但是對(duì)于數(shù)據(jù)插入效率更佳,這是不同數(shù)據(jù)庫(kù)產(chǎn)品各自的權(quán)衡。
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
