--数据的完整性
六种约束:not null 、check、unique、primary key、foreign key、default
--创建唯一约束
--这是删除唯一约束
alter table xibu
drop constraint weiyi
--这是建立唯一约束
alter table xibu
add constraint weiyi
unique nonclustered (xbName)
alter table xibu
alter column asd char(8)
--这是check约束
insert xs values('010101000017','克群','a','1989-2-9','2010-9-1','0101011','04','0402')
select * from xs
if exists(ck_xs)
alter table xs
drop constraint CK_xs
alter table xs
add constraint aaa
check(sex = '男')
--withnocheck对你以前插入的数据不进行检查
alter table xs with nocheck add
constraint CK_sex check(sex ='男' or sex='女')
exec sp_help xs--这是查看表结构
--这是default约束
alter table bj drop constraint DF_bj_bz
alter table bj add constraint DF_bj_bz default '我爱你' for bz;
create table test
(
id int not null primary key,
name varchar(10) not null
)
drop table test
alter table test
drop constraint aaa
alter table test with nocheck
add constraint aaa default '我真的' for name
exec sp_help test
insert into test values(2,default);
select * from test
规则
/*数据的完整性*/
--规则的创建
create rule check_rule
as
@a = '男' or @a = '女'
--将规则绑定到数据库表的字段上
exec sp_bindrule 'check_rule', 'test.name'
insert into test values(1, '男');
select * from test
--注意:规则可以用到多个表上
select * into new_table from test
select * from new_table
exec sp_bindrule 'check_rule', 'new_table.name'
insert into new_table values(1, 'asd');
--删除绑定到表中某个字段上的规则
exec sp_unbindrule 'new_table.name'
exec sp_unbindrule 'test.name'
--删除规则
drop rule check_rule
--default规则的一些应用
--创建默认值
create default ch_default
as '曹欢'
--将默认值绑定到数据库表的某个字段
exec sp_bindefault ch_default,'test.name'
insert into test values(13,default);
select * from test
--删除绑定到表字段上的default约束
exec sp_unbindefault 'test.name'
--删除default的默认值
drop default ch_default
--这是对这节的练习
create table test1 (
id int identity(1,1) primary key ,
sex varchar(4) not null,
name varchar(12)
)
select * from test1
exec sp_help test1
--创建规则
create rule check_rule
as
@sex = '男' or @sex = '女'
--把规则应运到
exec sp_bindrule 'check_rule','test1.sex'
exec sp_unbindrule 'test1.sex'
drop rule check_rule
insert into test1 values('男','asd')
select * from test1
create default default_rule
as '曹欢'
exec sp_bindefault 'default_rule','test1.name'
exec sp_unbindefault 'test1.name'
drop default default_rule
insert into test1 values('男',default)
select * from test1