之所以把約束和索引放到一起來看，主要是因為主鍵約束和唯一鍵約束，它們會自動創建一個對應的索引，先分別看下數據庫中的幾個約束。
一 約束
在關系型數據庫里，通常有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)
)
單獨定義在某一列上的約束被稱為列級約束，定義在多列上的約束則稱為表級約束。
?
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
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
'
)
?
外鍵開發維護過程中，常見的問題及解決方法：
(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) 從表插入數據出錯
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'.
*/
--
從表在參照主表中的數據，可以先禁用外鍵(只是暫停約束檢查)
alter
table
tb2
NOCHECK
constraint
FK_tb2
alter
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
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) 清空/刪除主表出錯
--
清空主表時，即便禁用外鍵，但外鍵關系依然存在，所以任然無法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選項，因為主從表里數據不一致了，所以不檢查約束，否則外鍵加不上
alter
table
tb2
WITH
NOCHECK
add
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
=
null
if
@c
<>
'
abc
'
or
@c
=
'
abc
'
print
'
null
'
else
print
'
I donot know
'
GO
declare
@i
int
set
@i
=
null
print
@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
1
alter
table
tb6
add
c2
int
default
35767
not
null
select
*
from
tb6
--
在alter table完成前，表一直處于鎖定狀態；
--
如果向大型表添加列，對數據頁的操作需要一些時間，最好事先做好評估。
?
二 索引
定義約束時，并沒有定義數據庫實現約束的方法，目前的關系型數據庫系統，主鍵和唯一鍵約束借助唯一索引來實現，所以在創建主鍵/唯一鍵時，都會自動生成一個同名的索引。
那么由約束產生的唯一索引，和單獨創建的唯一索引有什么聯系和區別？
?
1.創建主鍵或唯一鍵約束時，數據庫自動創建唯一索引
自動生成的該索引是無法刪除的，因為這個索引要用于實現約束，在刪除約束的時候，該索引也被刪除。演示腳本如下：
--
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
'
--
查看約束，返回如下結果：
/*
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為非聚集索引
--
查看約束產生的索引，返回如下結果：
/*
object_id name
1227151417 UQ_TEST_CONS_ID
*/
--
check constraint
INSERT
INTO
TEST_CONS
SELECT
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 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
?
另外，約束生成的索引，有些屬性也是無法被修改的，比如：開關IGNORE_DUP_KEY，唯一的辦法是：先刪除約束，再重新定義約束/索引；單獨定義的索引，則沒有這個限制，如下例：
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
--
單獨創建的唯一索引，屬性可以隨意修改
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 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
'
--
此時提示為：唯一索引
/*
消息2601，級別14，狀態1，第1 行
不能在具有唯一索引'UQ_TEST_CONS_ID' 的對象'dbo.TEST_CONS' 中插入重復鍵的行。
*/
--
add constraint
ALTER
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 constraint
ALTER
TABLE
TEST_CONS
ADD
CONSTRAINT
UQ_TEST_CONS_ID_1
UNIQUE
NONCLUSTERED
(ID)
--
換個名字當然是可以成功的，但此時又生成了唯一索引UQ_TEST_CONS_ID_1
--
drop table
DROP
TABLE
TEST_CONS
?
3.主鍵是否是聚集索引？
SQL Server默認在定義主鍵時，將生成的唯一索引定義為聚集，剛剛接觸的時候容易被搞混淆了。主鍵對應的索引也可以非聚集，如下：
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中定義主鍵時，默認生成聚集索引，唯一的好處是主鍵列范圍掃描/查找的效率比較高，但數據插入效率欠佳(聚集索引，非聚集索引，都得被維護一次)，并且主鍵列如果選擇的不好，會影響其他非聚集索引的性能。
ORACLE中定義主鍵時，默認生成非聚集索引，不利于主鍵列的范圍掃描/查找，但是對于數據插入效率更佳，這是不同數據庫產品各自的權衡。
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
