05-数据库的约束
一、总结
1、关系型数据库里一般有5种约束:主键约束、唯一键约束、外键约束、CHECK约束、NULL约束、DEFAULT(从系统视图来看,default也被SQLServer当成约束来管理);
2、主键约束和唯一键约束会自动创建一个对应的索引;
3、单独定义在某一列上的约束被称为列级约束,定义在多列上的约束称为表级约束;
4、有外键的表,插入数据会失败,耦合度太高,所以外键一般不适用;
二、五种约束详解
1、主键约束
(1)在表中的一列或者多列(联合主键)上,定义主键来唯一标识表中的数据行;
(2)主键约束要求键值唯一且不能为null,字段的属性必须是not null,才能创建主键,不然会报错;
(3)主键约束 = 唯一约束 + 非空约束;
(4)一张表只能有一个主键,并且创建主键后,就会生成一个聚集索引;
(5)相关SQL
--创建语句 alter table student add constraint pk_name primary key (id,name) --删除语句 alter table student drop constraint pk_name
2、唯一键约束
(1)唯一约束和主键约束的区别就是允许为null,所以创建的字段不用非得是not null;
(2)唯一键列仅可以有一行为null,因为有2个以上为null的话,就违背唯一约束的原则了;
(3)创建唯一约束会相应的创建一个非聚集索引;
(4)也可以创建联合唯一约束,也称作唯一键;
(5)相关SQL
--创建单列唯一键 alter table student add constraint unique_classname unique(classname) --创建联合唯一键 alter table student add constraint unique_classsex unique(studytime,sex) --删除唯一键 alter table student drop constraint unique_classsex
3、外键约束
(1)外键约束指的是表中的一列或者多列,引用其他表的主键或者唯一键;
(2)从表和主表中要创建外键的字段,长度或小数位数必须一样(比如要是nvarchar(20)就都是nvarchar(20)),不然会报错;
(3)创建外键的主表和从表字段是否为null无所谓,比如主表字段not null ,从表字段为null,外键也可以创建成功;
(4)从表创建外键的时候,从表可以没有主键,这个不是必须的;
(5)如果表的主键被其他表创建为外键,那么主键不能直接删除,否则会报错,需要先删除从表的外键,再删除主表的主键;
(6)创建外键的脚本(可以是主表的主键或者唯一键)
a、从表的外键列是主表的主键
alter table teachclass with nocheck add constraint FK_class foreign key(class) references student(name)
b、从表的外键列是主表的主键
alter table teachclass with nocheck add constraint FK_class foreign key(class) references student(name)
(7)从表里的同一个列既可以为自己的主键,也可以定义为外键;
(8)不能将主表的主键或唯一键的部分列作为外键,必须是全部列一起使用;
--主表联合主键 alter table student add constraint pk_name primary key (id,name) --从表的外键(错误示范) alter table teachclass with nocheck add constraint FK_class foreign key(class) references student(name) --从表的外键(正确示范) alter table teachclass add id int null alter table teachclass with nocheck add constraint FK_class foreign key(id,class) references student(id,name)
(9)从表插入数据失败
解决办法:
--先禁用外键 alter table teachclass nocheck constraint all --禁用所有约束 alter table teachclass nocheck constraint FK_class --插入数据后启用外键 insert into teachclass values('jack','m','22','sjj','sd','dsd','ess',1) alter table teachclass check constraint FK_class
(10)主表删除/更新数据出错
解决办法:
--先禁用外键或者删除从表中被引用的数据
注:未被引用的表可直接被删除
(11)truncate或drop主表会报错,即使是禁用外键,操作也会失败,因为外键关系依然存在;需要先drop从表或者删除外键才能成功truncate或drop主表;
(12)一个表上可以创建多个多个外键,但是出于性能和耦合度的考虑,不推荐使用外键;
4、CHECK约束
(1)check约束用于限制列中值的范围;
(2)check约束通过逻辑表达式来判断数据是否满足条件,用来限制一列或多列的值的范围,从而来约束输入的内容;
(3)可以对单独的列定义约束,也可以对整个表定义约束来限制多个列;
(4)创建脚本
--给sex列添加约束,要不‘男’要不‘女’,不能是其他值 alter table teacher add constraint check1 check(sex='男' or sex ='女')
(5)相关SQL
--可以定义表达式来检查列值,出于性能考虑,一把不推荐使用 select * from Test.dbo.teacher --给sex列添加约束,要不‘男’要不‘女’,不能是其他值 alter table teacher add constraint check1 check(sex='男' or sex ='女') --插入数据(不满足check约束) insert into teacher(sex) values('ma') --插入数据(满足check约束) insert into teacher(sex) values('男') --删除约束 alter table teacher drop constraint check1
5、NULL约束
(1)用于控制列是否允许为null;
(2)SQLServer中的聚合函数是会忽略null值的;
(3)字符型的字段,如果not null,那这个字段不能为null值,但是可以为空值‘’,意思是空字符串,和null值是不一样的;
(4)null无法直接参与比较运算,例如下面的语句:
declare @a varchar(100) set @a = null if @a<>'jack' or @a = 'jack' print 'null' else print 'I donot know' GO
declare @i int set @i = null print @i + 1
6、DEFAULT约束
(1)default在SQLServer中也被看做约束来管理的,可以通过sys.default_constraints来查看,每个default返回一行;
(2)创建一个带有默认值的表
create table test(id int,sex char(4) default '男')
(3)查看当前库的所有default约束
select * from test.sys.default_constraints