check(num>100000)
check((left(ID,1) between 'a' and 'z' or left(ID,1)between 'A' and 'Z') and
(substring(ID,2,1) between 'a' and 'z' or substring(ID,2,1)between 'A' and 'Z') )
check((left(ID,1) between 'a' and 'z' or left(ID,1)between 'A' and 'Z') and
(substring(ID,2,1) between 'a' and 'z' or substring(ID,2,1)between 'A' and 'Z') )
alter table test add check(cast(TEL as int)>0)insert test (name,TEL) select 'aaa','130056'
go
insert test (name,TEL) select 'aaa','130t56'
go服务器: 消息 245,级别 16,状态 1,行 1
将 varchar 值 '130t56 ' 转换为数据类型为 int 的列时发生语法错误。
--约束num字段为数字类型
create function dbo.f_numeric
(@num char(6))
returns bit
as
begin
declare @i int
set @i=1
while(@i<7)
begin
if ascii(substring(@num,@i,1))between 48 and 57
set @i=@i+1
else
return 0
end
return 1
end
--约束id字段的前两个字符为字母
create function dbo.f_twochar
(@id char(8))
returns bit
as
begin
declare @i bit
if ascii(lower(substring(@id,1,1)))between 97 and 122
if ascii(lower(substring(@id,2,1)))between 97 and 122
set @i=1
else set @i=0
else
set @i=0
return(@i)
end
--约束tel字段为数字
create function dbo.f_tel_numeric
(@tel char(12))
returns bit
as
begin
declare @i int
set @i=len(@tel)
while(@i>0)
begin
if ascii(substring(rtrim(@tel),@i,1))between 48 and 57
set @i=@i-1
else
return 0
end
return 1
end
--创建含有这些约束的表
create table tb(num char(6) check(dbo.f_numeric(num)=1)
,[id] char(8) check(dbo.f_twochar([id])=1)
,tel char(12) check(dbo.f_tel_numeric(tel)=1))
--插入数据
insert tb
select '706815','adadfadf','7065189'-- 成功
insert tb
select '7068159','a1dddddd','456789'--违反了id的约束
/*
INSERT 语句与 COLUMN CHECK 约束 'CK__tb__id__014935CB' 冲突。该冲突发生于数据库 'test',表 'tb', column 'id'。
语句已终止。
*/
--------------------------------------------
num like '[0-9][0-9][0-9][0-9][0-9][0-9]'2.字段ID为CHAR(8)类型,怎样约束前两位字符为字母
----------------------------------------------
id like '[a-z][a-z]%'3.字段TEL为CHAR(12)类型,怎样约束为数字字符(长度不一定)
------------------------------------------------------
isnumeric(tel)=1