当前位置:  首页  >  PHP教程  >  PHP 应用  >  常见问题

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都可以被设置为默认值;

吐了个 "CAO" !
扫码关注 PHP1 官方微信号
PHP1.CN | 中国最专业的PHP中文社区 | PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | PHP问答
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved PHP1.CN 第一PHP社区 版权所有