之所以把約束和索引放到一起來看，主要是因?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ì)您有幫助就好】元
