和尚和中国风lz已经说了允许为空,建立主键或者唯一索引就不好了吧。create trigger t1 on tb for insert as if exists(select * from tb a where a =(select a from inserted)) rollback print '卡号重复'
unique 或者设置表主键,含两列(个人编号,卡号)
因为NULL代表无意义,不确定值,所以每个NULL值都不同,符合唯一性要求.
create table tb(a char(8),b varchar(10)) go create function mycheck (@b varchar(10)) returns bit as begin declare @x bit if (select count(*) from tb where b=@b)>1 set @x=0 else set @x=1 return @x end goalter table tb add check(dbo.mycheck(b)=1) goinsert tb select 1,2 union all select 2,null union all select 3,3 union all select 5,3 go drop table tb drop function mycheck go
create table tb(a char(8),b varchar(10)) go create function mycheck (@b varchar(10)) returns bit as begin declare @x bit if (select count(*) from tb where b=@b)>1 set @x=0 else set @x=1 return @x end goalter table tb add check(dbo.mycheck(b)=1) goinsert tb select 1,2 union all select 2,null union all select 3,3 union all select 5,3 go drop table tb drop function mycheck go
CREATE table xdgao_test_three ( columnA int not null unique nonclustered, columnB char(6), columnC nvarchar(50), columnD decimal(10,2) )
create function ISRepleacate(@KH nvarchar(100)) returns bit as begin if (@KH is null) --可为空 return 1 else if not exists(select 1 from 表 where KH=@kh) return 1 else return 0 end alter table 表 add constraint check_KH check(dbo.isRepleacate(KH)=1)
on tb
for insert
as
if exists(select * from tb a where a =(select a from inserted))
rollback
print '卡号重复'
或者设置表主键,含两列(个人编号,卡号)
go
create function mycheck
(@b varchar(10))
returns bit
as
begin
declare @x bit
if (select count(*) from tb where b=@b)>1
set @x=0
else
set @x=1
return @x
end
goalter table tb add check(dbo.mycheck(b)=1)
goinsert tb select 1,2
union all select 2,null
union all select 3,3
union all select 5,3
go
drop table tb
drop function mycheck
go
go
create function mycheck
(@b varchar(10))
returns bit
as
begin
declare @x bit
if (select count(*) from tb where b=@b)>1
set @x=0
else
set @x=1
return @x
end
goalter table tb add check(dbo.mycheck(b)=1)
goinsert tb select 1,2
union all select 2,null
union all select 3,3
union all select 5,3
go
drop table tb
drop function mycheck
go
INSERT 语句与 COLUMN CHECK 约束 'CK__tb__b__44FF419A' 冲突。该冲突发生于数据库 'workdemo',表 'tb', column 'b'。
语句已终止。
(
columnA int not null unique nonclustered,
columnB char(6),
columnC nvarchar(50),
columnD decimal(10,2)
)
returns bit
as
begin
if (@KH is null) --可为空
return 1
else if not exists(select 1 from 表 where KH=@kh)
return 1
else
return 0
end
alter table 表
add constraint check_KH check(dbo.isRepleacate(KH)=1)