表名 kqrb
列名 sj1 , sj2 , sj3 ,sj4列都为char(5) 可以为空我想给以上几列加个约束,让它要么为空,要么符合以下规则:'00:00'
.
.
.
'23:59'
意思就是记录时间啦这个约束怎么写啊。。我没有弄过约束,请告诉我完整的代码。谢谢啊
列名 sj1 , sj2 , sj3 ,sj4列都为char(5) 可以为空我想给以上几列加个约束,让它要么为空,要么符合以下规则:'00:00'
.
.
.
'23:59'
意思就是记录时间啦这个约束怎么写啊。。我没有弄过约束,请告诉我完整的代码。谢谢啊
create table kqrb(
sj1 varchar(40) check(sj1 like '[0-2][0-9]:[0-9][0-9]' or sj1 is null),
sj2 varchar(40) check(sj2 like '[0-2][0-9]:[0-9][0-9]' or sj1 is null),
sj3 varchar(40) check(sj3 like '[0-2][0-9]:[0-9][0-9]' or sj1 is null),
sj4 varchar(40) check(sj4 like '[0-2][0-9]:[0-9][0-9]' or sj1 is null)
)
sj1 varchar(40) ,
sj2 varchar(40) ,
sj3 varchar(40),
sj4 varchar(40)
)
go
create rule R_test
as
@sj like '[0-2][0-9]:[0-9][0-9]' or @sj is null
go
sp_bindrule 'R_test','kqrb.sj1'
go
sp_bindrule 'R_test','kqrb.sj2'
go
sp_bindrule 'R_test','kqrb.sj3'
go
sp_bindrule 'R_test','kqrb.sj4'
sj1 varchar(40) ,
sj2 varchar(40) ,
sj3 varchar(40),
sj4 varchar(40)
)
go
create rule R_test
as
@sj like '[0-2][0-9]:[0-9][0-9]' or @sj is null
go
sp_bindrule 'R_test','kqrb.sj1'
go
sp_bindrule 'R_test','kqrb.sj2'
go
sp_bindrule 'R_test','kqrb.sj3'
go
sp_bindrule 'R_test','kqrb.sj4'
这位大虾,楼主的列的数据类型都是char(5)的啊 ,这个小小的问题
SQL code
create table kqrb(
sj1 char(5) check(sj1 like '[0-2][0-4]:[0-5][0-9]' or sj1 is null),
sj2 char(5) check(sj2 like '[0-2][0-4]:[0-5][0-9]' or sj1 is null),
sj3 char(5) check(sj3 like '[0-2][0-4]:[0-5][0-9]' or sj1 is null),
sj4 char(5) check(sj4 like '[0-2][0-4]:[0-5][0-9]' or sj1 is null)
)
create table kqrb(
sj1 varchar(5) check((isdate(sj1)=1 and sj1>='00:00' and sj1<='23:59') or sj1 is null),
sj2 varchar(5) check((isdate(sj2)=1 and sj2>='00:00' and sj2<='23:59') or sj2 is null),
sj3 varchar(5) check((isdate(sj3)=1 and sj3>='00:00' and sj3<='23:59') or sj3 is null),
sj4 varchar(5) check((isdate(sj4)=1 and sj4>='00:00' and sj4<='23:59') or sj4 is null)
)
insert into kqrb(sj1) select '00:23' --yes
insert into kqrb(sj1) select '23:23' --yes
insert into kqrb(sj1) select '23:aa' --no
insert into kqrb(sj1) select '24:23' --no
www.ilikebook.com.cn有相关的书籍