T-SQL开发

之所以把约束和索引放到一起来看，主要是因为主键约束和唯一键约束，它们会自动创建一个对应的索引，先分别看下数据库中的几个约束。一约束在关系型数据库里，通

之所以把约束和索引放到一起来看，主要是因为主键约束和唯一键约束，它们会自动创建一个对应的索引，先分别看下数据库中的几个约束。

一 约束

在关系型数据库里，通常有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 occurred in 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都可以被设置为默认值；