之所以把约束和索引放到一起来看，主要是因为主键约束和唯一键约束，它们会自动创建一个对应的索引，先分别看下数据库中的几个约束。
一 约束
在关系型数据库里，通常有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中定义主键时，默认生成非聚集索引，不利于主键列的范围扫描/查找，但是对于数据插入效率更佳，这是不同数据库产品各自的权衡。
