例表:
zcm def rq
100001 0 2008-1-1 12:02:01
100002 1 2008-1-2 02:12:11
上面的rq列想定个约束,所有def=0的行rq年月日不重复就可,所有def=1的行,日期要求 年月 不重复。
高手们,请出手!
zcm def rq
100001 0 2008-1-1 12:02:01
100002 1 2008-1-2 02:12:11
上面的rq列想定个约束,所有def=0的行rq年月日不重复就可,所有def=1的行,日期要求 年月 不重复。
高手们,请出手!
after insert
as
if ( exists( select 1 from inserted i
where def = 0 and exists( select 1 from table1
where zcm <> i.zcm and datediff( d, rq, i.rq ) = 0 ) )
or exists( select 1 from inserted i
where def = 1 and exists( select 1 from table1
where zcm <> i.zcm and datediff( m, rq, i.rq ) = 0 ) ) )
rollback tran
--计算列方法,仅供参考
CREATE TABLE [Table] (
zcm varchar(10) NOT NULL ,
def int not NULL ,
rq datetime,
[Col_rq] AS ( CASE def WHEN 0 THEN convert(varchar(8),rq,112) WHEN 1 THEN convert(varchar(6),rq,112) END )
CONSTRAINT UNQ_rq UNIQUE ( Col_rq )
)
GO
--测试数据
insert [Table] select '100001', 0 , '2008-1-1 12:02:01'
insert [Table] select '100002', 1 , '2008-1-2 02:12:11'
insert [Table] select '100003', 0 , '2008-1-2 12:02:01'
insert [Table] select '100004', 0 , '2008-1-2 02:12:11'
insert [Table] select '100005', 1 , '2008-2-1 12:02:01'
insert [Table] select '100006', 1 , '2008-2-2 02:12:11'
---------------------------------------------------------------------
/*
(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY constraint 'UNQ_rq'. Cannot insert duplicate key in object 'dbo.Table'.
The statement has been terminated.(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 6
Violation of UNIQUE KEY constraint 'UNQ_rq'. Cannot insert duplicate key in object 'dbo.Table'.
The statement has been terminated.
*/
--计算列方法,仅供参考
CREATE TABLE [Table] (
zcm varchar(10) NOT NULL ,
def int not NULL ,
rq datetime,
[Col_rq] AS ( CASE def WHEN 0 THEN convert(varchar(8),rq,112) WHEN 1 THEN convert(varchar(6),rq,112) END )
CONSTRAINT UNQ_rq UNIQUE ( Col_rq )
)
GO
--测试数据
insert [Table] select '100001', 0 , '2008-1-1 12:02:01'
insert [Table] select '100002', 1 , '2008-1-2 02:12:11'
insert [Table] select '100003', 0 , '2008-1-2 12:02:01'
insert [Table] select '100004', 0 , '2008-1-2 02:12:11'
insert [Table] select '100005', 1 , '2008-2-1 12:02:01'
insert [Table] select '100006', 1 , '2008-2-2 02:12:11'
---------------------------------------------------------------------
/*
(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY constraint 'UNQ_rq'. Cannot insert duplicate key in object 'dbo.Table'.
The statement has been terminated.(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 6
Violation of UNIQUE KEY constraint 'UNQ_rq'. Cannot insert duplicate key in object 'dbo.Table'.
The statement has been terminated.
*/