判断sql server 约束 sql server 检查约束

--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表中相关的行－－结束' 
godrop trigger st_delete
delete from student where sno='2014005'
--我这里删除有点小的问题 可能是 语句执行的顺序是 先删除student
--而sc表中的某些数据正好参照student的sno键所以 开始就不能删除-----------------------------------------------------------------------------------
select * from sccreate table sc_back (
 sno varchar(10),
 cno varchar(2) ,
 grade int,
)--备份的触发器
select * from sc_backif 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
goupdate student set sno='2014065' where sno='2014002'
select * from student
select * from sc----------------------------------------------
create rule  gread_rule
as @gb >=0sp_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
godrop trigger grade_update
update sc set grade=111 where sno='2014002'