sqlserver check约束 从基础到高级应用 总结

 

1. 规定某 字段的数据必须是字母或者数字或者字母

alter table 表名 add constraint 约束名 check(字段 like '%[0-9][a-z]%')

上边是必须有字母和数字的，你可以加个：
or 字段 like '%[0-9]%'
or 字段 like '%[a-z]%'

2. 时间约束 EndTime不能早于BeginTime

EndTime>BeginTime or EndTime=BeginTime

 

3.Int型字段，值减一且 int字段不小于0

  update table1 set a=a-1 
   并增加check约束
   check (a<>0)

4. 一列是CHECK约束：手动或自动；
    一列是Check约束：大于等于0

alter table 表名 add constraint [约束名1] check(字段名1 in ('手动','自动')）
alter table 表名 add constraint [约束名2] check(字段名2〉=0 or check(字段名2〉>0）

5.一个年龄字段设check约束：
alter table stu add constraint CK_stu_age check(age between 15 and 50)

 

6. check 某字段的长度

create proc pro_check_lengh
(
@in_no varchar(20),

@out_rtn smallint output

)
as
  declare @len int
  set @len=len(@in_no)
  if @len='指定的长度'

  begin
    set out_rtn =0
  end
  else
  begin
    set out_rtn =1
  end

 

7.删除SQL约束

1)禁止所有表约束的SQL
select 'alter table '+name+' nocheck constraint all' from sysobjects where type='U'

2)删除所有表数据的SQL
select 'TRUNCATE TABLE '+name from sysobjects where type='U'

3)恢复所有表约束的SQL
select 'alter table '+name+' check constraint all' from sysobjects where type='U'

4)删除某字段的约束
declare @name varchar(100)
--DF为约束名称前缀
select @name=b.name from syscolumns a,sysobjects b where a.id=object_id('表名') and b.id=a.cdefault and a.name='字段名' and b.name like 'DF%'
--删除约束
alter table 表名 drop constraint @name
--为字段添加新默认值和约束
ALTER TABLE 表名 ADD CONSTRAINT @name  DEFAULT (0) FOR [字段名]对字段约束进行更改
--删除约束
ALTER TABLE tablename
Drop CONSTRAINT 约束名
--修改表中已经存在的列的属性（不包括约束，但可以为主键或递增或唯一）
ALTER TABLE tablename
alter column 列名 int not null
--添加列的约束
ALTER TABLE tablename
ADD CONSTRAINT DF_tablename_列名 DEFAULT(0) FOR 列名
--添加范围约束
alter table  tablename  add  check(性别 in ('M','F'))

 

8. 一个邮编的约束: 5位数字

ALTER TABLE dbo.ZIPCodes ADD CONSTRAINT CK_ZIPCodes_ZIPCode CHECK (ZIPCode like '[0-9] [0-9] [0-9] [0-9] [0-9]')

 

9.日期范围约束(动态SQL形式)

alter table '+@char1+' with nocheck add constraint '+@manage_flg1+@key1+@key2+@key3+@key4+@key5+@char9+' check ('+@char3+' >=''1900/01/01'' and '+@char3+' <=''2101/01/01'')'

10.指定区分约束

 商品种类区分
 alter table m_item with nocheck add constraint pdm101600B check(item_flg in('0','1','2','3'))

   商品重要区分
 alter table m_item with nocheck add constraint pdm1011200B check((imp_flg in('0','1')) or (item_flg in ('2','3') and imp_flg ='1'))

11.限定别的表的关联约束（约束+函数）

--约束1： 元図1和元図2要和品目表的商品コード1和商品コード2 存在并且商品区分=机种
 --元図1
 alter table m_item with nocheck add constraint pdm101900C check(past_draw_cd1 = (dbo.FN_PDM_CHECK_M_ITEM(past_draw_cd1,past_draw_cd2,item_flg,'1','1')))
 --元図2
 alter table m_item with nocheck add constraint pdm1011000C check(past_draw_cd2 = (dbo.FN_PDM_CHECK_M_ITEM(past_draw_cd1,past_draw_cd2,item_flg,'2','1')))

--约束2： --関連図番_工番和関連図番_番号要：  制番区分<>3,4的  品目表的商品コード1和商品コード2 存在并且商品区分=部品

 --関連図番_工番
 alter table t_part_cons_history with nocheck add constraint pdm1021400C check(
 (prc_kbn in('3','4') AND rel_draw_cd1 = (dbo.FN_PDM_CHECK_M_ITEM(rel_draw_cd1,rel_draw_cd2,'','1','2'))) or prc_kbn not in('3','4'))
 --関連図番_番号
 alter table t_part_cons_history with nocheck add constraint pdm1021500C check(
 (prc_kbn in('3','4') AND rel_draw_cd2 = (dbo.FN_PDM_CHECK_M_ITEM(rel_draw_cd1,rel_draw_cd2,'','2
 ','2'))) or prc_kbn not in('3','4'))

CREATE  FUNCTION [dbo].[FN_PDM_CHECK_M_ITEM]
(
 @IN_ITEM_CD1 NVARCHAR(5),   --品目コード1
 @IN_ITEM_CD2 NVARCHAR(10),  --品目コード2
 @IN_ITEM_FLG NVARCHAR(1),   --品目区分
 @TYPE_FLG   NVARCHAR(1),    --チェック区分(1:品目コード1をチェック、1以外:品目コード2をチェック)
   @SERVICE_KUBUN   NVARCHAR(1) --品目区分制御（1：品目区分＝1.2.3，1以外：指定の品目区分により）
)
RETURNS NVARCHAR(20)
AS
BEGIN
    DECLARE @OUT_RTN NVARCHAR(20)=''
    --
    IF @SERVICE_KUBUN = '1'
    BEGIN
    
     IF @TYPE_FLG='1'
     BEGIN
      SELECT @OUT_RTN=ITEM_CD1 FROM M_ITEM
      WHERE ITEM_CD1=@IN_ITEM_CD1 AND ITEM_CD2=@IN_ITEM_CD2 AND ITEM_FLG = @IN_ITEM_FLG
     END
     ELSE
     BEGIN
      SELECT @OUT_RTN=ITEM_CD2 FROM M_ITEM
      WHERE ITEM_CD1=@IN_ITEM_CD1 AND ITEM_CD2=@IN_ITEM_CD2 AND ITEM_FLG = @IN_ITEM_FLG
     END
 END
 ELSE
 BEGIN
     IF @TYPE_FLG='1'
     BEGIN
      SELECT @OUT_RTN=ITEM_CD1 FROM M_ITEM
      WHERE ITEM_CD1=@IN_ITEM_CD1 AND ITEM_CD2=@IN_ITEM_CD2 AND ITEM_FLG IN('1','2','3')
     END
     ELSE
     BEGIN
      SELECT @OUT_RTN=ITEM_CD2 FROM M_ITEM
      WHERE ITEM_CD1=@IN_ITEM_CD1 AND ITEM_CD2=@IN_ITEM_CD2 AND ITEM_FLG IN('1','2','3')
     END
    END  
   RETURN @OUT_RTN
END

GO