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