Check Constraint

I'm having a little trouble enforcing a unique record rule. The problem is
a
DateTime field is used in the unique value, but I only need to the DatePart
(not
the time), otherwise I would use a standard unique constraint.
I need the combination of ResID,RxDayID,RxTimeID, and Just The Date of
DispDateTime to be unique.
The following works for inserts but not updates if a record already exists.
It
does work if there are 2 matching records for an update. I'd rather have th
e
back-end enforce the rules, but if I must I'll build it into the application
.
Is there a function or a method I'm over-looking?
ALTER TABLE [dbo].[ResRxDispensed] ADD
CONSTRAINT [CK_ResRxDispensed_Unique]
CHECK ([dbo].[DispRecordUnique_FN](
[ResID],
[RxDayID],
[RxDoseTimeID],
[DispDateTime]) = 'Y')
Create Function [dbo].[DispRecordUnique_FN](
@.ResID Int,
@.RxDayID Int,
@.RxTimeID Int,
@.ADate DateTime)
Returns VarChar(1)
Begin
Declare @.TheDate DateTime, @.Rtn Varchar(1),@.Cnt Int
Set @.TheDate = Cast(Cast(@.ADate as Char(11)) As DateTime)
Set @.Rtn = 'N'
Select @.Cnt = Count(*)
From ResRxDispensed
Where ResID = @.ResID
And RxDayID = @.RxDayID
And RxDoseTimeID = @.RxTimeID
And (DispDateTime >= @.TheDate And DispDateTime <= (@.TheDate+1))
If (@.Cnt <= 1)
Begin
Set @.Rtn = 'Y'
End
Return @.Rtn
End
TIA,
-Steve-You've overcomplicated the solution IMHO:
1) create a computed column:
alter table dbo.ResRxDispensed
add DispDate as convert(char(8), DispDateTime, 112)
go
2) create the unique constraint:
alter table dbo.ResRxDispensed
add constraint <constraint name>
unique (ResID, RxDayID, RxTimeID, DispDate)
go
If this does not help, please post proper DDL and maybe sample data.
ML
http://milambda.blogspot.com/|||Experiment to see if the year(), month(), day() or datediff() functions
would work here.
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:%23kFjHVZWGHA.2180@.TK2MSFTNGP02.phx.gbl...
> I'm having a little trouble enforcing a unique record rule. The problem
> is a DateTime field is used in the unique value, but I only need to the
> DatePart (not the time), otherwise I would use a standard unique
> constraint.
> I need the combination of ResID,RxDayID,RxTimeID, and Just The Date of
> DispDateTime to be unique.
> The following works for inserts but not updates if a record already
> exists. It does work if there are 2 matching records for an update. I'd
> rather have the back-end enforce the rules, but if I must I'll build it
> into the application. Is there a function or a method I'm over-looking?
> ALTER TABLE [dbo].[ResRxDispensed] ADD
> CONSTRAINT [CK_ResRxDispensed_Unique]
> CHECK ([dbo].[DispRecordUnique_FN](
> [ResID],
> [RxDayID],
> [RxDoseTimeID],
> [DispDateTime]) = 'Y')
>
> Create Function [dbo].[DispRecordUnique_FN](
> @.ResID Int,
> @.RxDayID Int,
> @.RxTimeID Int,
> @.ADate DateTime)
> Returns VarChar(1)
> Begin
> Declare @.TheDate DateTime, @.Rtn Varchar(1),@.Cnt Int
> Set @.TheDate = Cast(Cast(@.ADate as Char(11)) As DateTime)
> Set @.Rtn = 'N'
> Select @.Cnt = Count(*)
> From ResRxDispensed
> Where ResID = @.ResID
> And RxDayID = @.RxDayID
> And RxDoseTimeID = @.RxTimeID
> And (DispDateTime >= @.TheDate And DispDateTime <= (@.TheDate+1))
> If (@.Cnt <= 1)
> Begin
> Set @.Rtn = 'Y'
> End
> Return @.Rtn
> End
> TIA,
> -Steve-
>|||Thanks.
I tried your suggestion but it apparently doesn't work when you use a comput
ed
column in a constraint. When I attempt to add a record I get the errror:
[INSERT failed because the following SET options have incorrect settings:
'ARITHABORT']
Using SET ARITHABORT ON before the insert/update works, but it seems it must
be
issued before each update/insert statement.

> If this does not help, please post proper DDL and maybe sample data.
CREATE TABLE [dbo].[ResRxDispensed] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ResID] [int] NULL ,
[ResRxID] [int] NULL ,
[RxDayID] [int] NULL ,
[RxDoseTimeID] [int] NULL ,
[DispDateTime] [datetime] NULL ,
[Created] [datetime] NULL ,
[StaffID] [int] NULL ,
[DispDate] AS (convert(char(8),[DispDateTime],112))
) ON [PRIMARY]
Insert Into ResRxDispensed
([ResID],[ResRxID],[RxDayID],[RxDoseTime
ID],[DispDateTime])
Values
(1,1,1,1,{ts '2006-04-06 09:30:00'}) ;
The insert should fail if another record has
ResID = 1
RxDayID = 1
RxDoseTimeID = 1
DispDateTime = 2006-04-06 (with any time factor)
Updates should also fail if the update changes the record to match another
record with the criteria.
-Steve-|||
"JT" <someone@.microsoft.com> wrote in message
news:%23sosFSaWGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Experiment to see if the year(), month(), day() or datediff() functions wo
uld
> work here.
I'm not sure I understand. Experiment how? The function
DispRecordUnique_FN()works, but using it in a Check constraint only works on
Inserts, not updates.
-Steve-|||This worked fine for me on SQL 2000.
CREATE TABLE [dbo].[TestTable] (
[ResID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RxDayID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RxDoseTimeID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DispDateTime] [datetime] NOT NULL ,
[OtherData] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DispDate] AS (convert(char(8),[DispDateTime],112))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable] ADD
CONSTRAINT [IX_TestTable] UNIQUE NONCLUSTERED
(
[ResID],
[RxDoseTimeID],
[RxDayID],
[DispDate]
) ON [PRIMARY]
GO
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:ep7wCVaWGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Thanks.
> I tried your suggestion but it apparently doesn't work when you use a
computed
> column in a constraint. When I attempt to add a record I get the errror:
> [INSERT failed because the following SET options have incorrect settings:
> 'ARITHABORT']
> Using SET ARITHABORT ON before the insert/update works, but it seems it
must be
> issued before each update/insert statement.
>
> CREATE TABLE [dbo].[ResRxDispensed] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ResID] [int] NULL ,
> [ResRxID] [int] NULL ,
> [RxDayID] [int] NULL ,
> [RxDoseTimeID] [int] NULL ,
> [DispDateTime] [datetime] NULL ,
> [Created] [datetime] NULL ,
> [StaffID] [int] NULL ,
> [DispDate] AS (convert(char(8),[DispDateTime],112))
> ) ON [PRIMARY]
>
> Insert Into ResRxDispensed
> ([ResID],[ResRxID],[RxDayID],[RxDoseTime
ID],[DispDateTime])
> Values
> (1,1,1,1,{ts '2006-04-06 09:30:00'}) ;
> The insert should fail if another record has
> ResID = 1
> RxDayID = 1
> RxDoseTimeID = 1
> DispDateTime = 2006-04-06 (with any time factor)
> Updates should also fail if the update changes the record to match another
> record with the criteria.
> -Steve-
>
>|||> This worked fine for me on SQL 2000.
Hi Jim,
Thanks. I'm using SQL 2000, but I'm still getting the same error.
Here's the scripts for creating everything I'm using.
CREATE TABLE [dbo].[ResRxDispensed] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ResID] [int] NULL ,
[ResRxID] [int] NULL ,
[RxDayID] [int] NULL ,
[RxDoseTimeID] [int] NULL ,
[DispDateTime] [datetime] NOT NULL ,
[Created] [datetime] NULL ,
[StaffID] [int] NULL ,
[DispDate] AS (convert(char(8),[DispDateTime],112))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ResRxDispensed] WITH NOCHECK ADD
CONSTRAINT [PK_ResRxDispensed] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ResRxDispensed] ADD
CONSTRAINT [DF_ResRxDispensed_DispDateTime] DEFAULT (getdate()) FOR
[DispDateTime],
CONSTRAINT [DF_ResRxDispensed_Created] DEFAULT (getdate()) FOR [Created],
CONSTRAINT [IX_ResRxDispensed] UNIQUE NONCLUSTERED
(
[ResID],
[RxDayID],
[RxDoseTimeID],
[DispDate]
) ON [PRIMARY]
GO
CREATE INDEX [ResRxDispensed_ResID] ON [dbo].[ResRxDispensed]([ResID]) ON
[PRIMARY]
GO
CREATE INDEX [ResRxDispensed_ResRxID] ON [dbo].[ResRxDispensed]([ResRxID]) ON
[PRIMARY]
GO
CREATE INDEX [ResRxDispensed_RxDayID] ON [dbo].[ResRxDispensed]([RxDayID]) ON
[PRIMARY]
GO
CREATE INDEX [ResRxDispensed_RxDoseTimeID] ON
[dbo].[ResRxDispensed]([RxDoseTimeID]) ON [PRIMARY]
GO
-Steve-|||Is there a reason why you can't add this SET option to your stored procedure
s?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:%23Set$paWGHA.2080@.TK2MSFTNGP05.phx.gbl...
> Hi Jim,
> Thanks. I'm using SQL 2000, but I'm still getting the same error.
> Here's the scripts for creating everything I'm using.
> CREATE TABLE [dbo].[ResRxDispensed] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ResID] [int] NULL ,
> [ResRxID] [int] NULL ,
> [RxDayID] [int] NULL ,
> [RxDoseTimeID] [int] NULL ,
> [DispDateTime] [datetime] NOT NULL ,
> [Created] [datetime] NULL ,
> [StaffID] [int] NULL ,
> [DispDate] AS (convert(char(8),[DispDateTime],112))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ResRxDispensed] WITH NOCHECK ADD
> CONSTRAINT [PK_ResRxDispensed] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ResRxDispensed] ADD
> CONSTRAINT [DF_ResRxDispensed_DispDateTime] DEFAULT (getdate()) FOR [DispDateTime],
> CONSTRAINT [DF_ResRxDispensed_Created] DEFAULT (getdate()) FOR [Created],
> CONSTRAINT [IX_ResRxDispensed] UNIQUE NONCLUSTERED
> (
> [ResID],
> [RxDayID],
> [RxDoseTimeID],
> [DispDate]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_ResID] ON [dbo].[ResRxDispensed]([ResID]) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_ResRxID] ON [dbo].[ResRxDispensed]([ResRxID]) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_RxDayID] ON [dbo].[ResRxDispensed]([RxDayID]) ON [PRIMARY]
> GO
> CREATE INDEX [ResRxDispensed_RxDoseTimeID] ON [dbo].[ResRxDispensed]([RxDoseTimeID]) ON [PRIMARY]
> GO
>
> -Steve-
>|||"Tibor Karaszi" wrote
> Is there a reason why you can't add this SET option to your stored procedures?[/co
lor]
I suppose not. I just wanted something that didn't require such special
handling for updates in case I needed to do some manual repair/entry outside
of
the application.
-Steve-|||"Tibor Karaszi" wrote:
> Is there a reason why you can't add this SET option to your stored procedures?[/co
lor]
This procedure, when used, produces the same error.
[INSERT failed because the following SET options have incorrect settings:
'ARITHABORT']
I'm at a loss how to proceed...
-Steve-
Exec NewResRxDispensed_SP 1,1,1,1,{ts '2006-05-13 09:30:00'},24
Create Procedure [dbo].[NewResRxDispensed_SP]
@.ResID int,
@.ResRxID int,
@.RxDayID int,
@.RxDoseTimeID int,
@.DispDateTime datetime,
@.StaffID int
As
SET ARITHABORT ON
Insert Into [ResRxDispensed]
([ResID],
[ResRxID],
[RxDayID],
[RxDoseTimeID],
[DispDateTime],
[StaffID])
Values
(@.ResID,
@.ResRxID,
@.RxDayID,
@.RxDoseTimeID,
@.DispDateTime,
@.StaffID)
/*** Return New Int ID [ID] ***/
Select SCOPE_IDENTITY() As NewID