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

r囧r小猫 2022-09-30 04:55 225阅读 0赞

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+'][char1] with nocheck add constraint ['+@manage\_flg1+@key1+@key2+@key3+@key4+@key5+@char9+'][char1] check (['+@char3+'][char1] >=''1900/01/01'' and ['+@char3+'][char1] <=''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][ITEM_CD1_IN_ITEM_CD1] AND [ITEM\_CD2=@IN\_ITEM\_CD2][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][ITEM_CD1_IN_ITEM_CD1] AND [ITEM\_CD2=@IN\_ITEM\_CD2][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][ITEM_CD1_IN_ITEM_CD1] AND [ITEM\_CD2=@IN\_ITEM\_CD2][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][ITEM_CD1_IN_ITEM_CD1] AND [ITEM\_CD2=@IN\_ITEM\_CD2][ITEM_CD2_IN_ITEM_CD2] AND ITEM\_FLG IN('1','2','3')  
     END  
    END    
   RETURN @OUT\_RTN  
END

[char1]: mailto:'%20rel=
[ITEM_CD1_IN_ITEM_CD1]: mailto:ITEM_CD1=@IN_ITEM_CD1
[ITEM_CD2_IN_ITEM_CD2]: mailto:ITEM_CD2=@IN_ITEM_CD2