SQLserver数据的完整性
--sql约束
/*
（1）提供语义定义完整性约束条件的机制
实体完整性：主键、非空
参照完整性：外键
用户自定义完整性：check、触发器
（2）提供完整性检查方法
一般在：Insert、Update、Delete执行后开始检查，及事务提交后开始检查。
（3）违约处理
拒绝（no action）
级连（级联 Cascade）
*/
/* 几种约束
非空约束（NOT NULL Constraint ）
主键约束（Primary Key Constraint）
外键约束（Foreign Key Constraint）
唯一性约束（Unique Constraint）
检查约束(Check Constraint)
默认值约束（Default Constraint）
*/
--主键约束
--【例1】实体完整性约束。
CREATE TABLE student1 (
sno char (9) PRIMARY key , --//列级约束
sname varchar (10) ,
ssex char (2) ,
sage int NULL ,
sdept char (10) ) --,
--或 PRIMARY key(sno)) --//表级约束
--表级约束
CREATE TABLE sc1 (
sno char(9) ,
cno char(3) ,
grade int,
primary key(sno,cno) -- //表级约束
)
--参照完整性定义
--Foreign key(列名) references table_name（列名）
--注意要参考的列应该和被参考列的长度,数据类型相同
sp_help course;
sp_help student;
--【例2】参照完整性实例 （列级、表级均可）。
CREATE TABLE sc2 (
sno char(9),
cno char(4),
grade int ,
primary key(sno,cno) ,
foreign key(cno) references course(cno),
foreign key(sno) references student(sno)
);
--测试约束
CREATE TABLE sc3 (
sno char(9) ,
cno char(4) ,
grade int ,
primary key(sno,cno) ,
foreign key(sno) references student(sno)
on delete no action --当删除student的记录的时候 会失败
on update cascade --但更新student的记录时 sc3表中的记录也会更新
) ;
--恰当使用级联更新和级联删除 方便数据库在更新时的操作;
insert into student(sno) values('2014019');
insert into sc3 values('2014019','001',100);
delete from student where sno='2014019';
update student set sno='122222' where sno='2014019';
select * from sc3;
--在删除该约束后就可以成功删除了
--当你不给约束起名字的时候 DBMS会自动给该约束起一个名字
--sp_help sc3;
alter table sc3 drop FK__sc3__sno__398D8EEE;
drop table sc3;
------------------------------------------------
--check约束
/*CREATE TABLE student1 (
sno char (9) PRIMARY key ,
sname varchar (10) ,
ssex char (2) check(ssex in('男','女')),
sage int NULL ,
sdept char (10) ,
check(ssex='女' or sname not like 'Ms%')
) */
--测试check约束
--insert into student1 values('100053','heheh','x',19,'is');
--insert into student1 values('190292','Msxiao','男',19,'is');
--insert into student1 values('100023','Masd','男',19,'is');
---------------------------------------------------------------------------
--创建角色
CREATE RULE xb_rule
AS @xb IN ('男','女')
--绑定角色到特定的列
--sp_bindrule xb_rule,'student.ssex'
--测试
--select * from student ;
--insert into student values('2014012','李大海','x',21,'cs');
--insert into student values('2014012','李大海','男',21,'cs');
--delete from student where sno='2014012'
--解除绑定
-- sp_unbindrule 'student.ssex'
---------------------------------------------------------------------
--创建默认值
CREATE DEFAULT df_xf AS 4
--绑定默认值
sp_bindefault df_xf,'course.ccredit'
--解除绑定
sp_unbindefault 'course.ccredit'
select * from course
insert into course (cno,cname) values(8,'物理');
delete from course where cno=8
--解除绑定后才能够删除
drop default df_xf
CREATE DEFAULT df_xf AS 10
sp_bindefault df_xf,'course.cpno'
sp_unbindefault 'course.cpno'
-----------------------------------------------------------------
/*
use mydb
go
if exists(select name from sysobjects where name='sc_iu' and type='tr')
drop trigger sc_iu
go
create trigger sc_iu
on sc
for insert,update
as
print '插入或更新了sc表！\n'
print '插入或更新了sc表！\n'
Go
*/
--insert into sc values('2014005',5,100);
--update sc set grade=1000 where sno='2014005' and cno='5'
--select * from sc
/*
use mydb
go
alter trigger sc_iu
on sc
instead of insert
as
print '在sc表中没有插入记录！'
go
*/
--删除触发器
--drop trigger sc_iu
-------------------------------------------------------------------------------
create trigger st_delete on student
for delete as
declare @bsno char(9)
print '使用delete触发器级联删除sc表中相关的行－开始'
select @bsno=sno from deleted
print '删除的学号：'+@bsno
delete sc where sc.sno=@bsno
print '使用delete触发器级联删除sc表中相关的行－－结束'
go
drop trigger st_delete
delete from student where sno='2014005'
--我这里删除有点小的问题 可能是 语句执行的顺序是 先删除student
--而sc表中的某些数据正好参照student的sno键所以 开始就不能删除
-----------------------------------------------------------------------------------
select * from sc
create table sc_back (
sno varchar(10),
cno varchar(2) ,
grade int,
)
--备份的触发器
select * from sc_back
if exists(select name from sysobjects where name='sc_delete' and type='tr')
drop trigger sc_delete
go
create trigger sc_delete
on sc for delete as
print '使用delete触发器备份删除sc表中相关的行－－开始'
insert into sc_back
select * from deleted
print '使用delete触发器备份删除sc表中相关的行－－结束'
go
delete from sc where sno='2014005'
----------------------------------------------------------------------
--更新触发器
create trigger st_update
on student for update as
if update(sno)
begin
declare @bsno char(9),@asno char(9)
print '使用update触发器级联更新sc表中相关的行－开始'
select @bsno=sno from deleted
print '更新前的学号：'+@bsno
select @asno=sno from inserted
print '更新后的学号：'+@asno
update sc set sno=@asno where sc.sno=@bsno
print '使用update触发器级联更新sc表中相关的行－结束'
end
go
update student set sno='2014065' where sno='2014002'
select * from student
select * from sc
----------------------------------------------
create rule gread_rule
as @gb >=0
sp_bindrule gread_rule , 'sc.grade'
create default credit_default as 4
sp_bindefault credit_default ,'course.ccredit'
select * from course
select * from sc
-------------------------------------------
create trigger grade_update
on sc for update as
if update(grade)
begin
declare @old_grade int,@new_grade int ,@sno_ varchar(10)
select @sno_=sno from deleted
select @old_grade=grade from deleted
print '更新前的成绩：'
print @old_grade
select @new_grade=grade from inserted
print '更新后的成绩'
print @new_grade
if(@new_grade>1.1*@old_grade)
begin
print '成绩升幅太大，更新无效'
update sc set grade=@old_grade where sno=@sno_
end
else
print '更新成功'
end
go
drop trigger grade_update
update sc set grade=111 where sno='2014002'
