Violation of UNIQUE KEY constraint
-
I have a stored procedure that inserts records into the table SchoolServices based on a join of two other tables. SchoolServices has as its primary key an identity field, but it also has a unique index on the fields EmployeeID, School, ServiceDate, and Counter. Counter is an integer field that is incremented within EmployeeID, School, and Date to make the key unique.
When I insert into SchoolServices, I don't know what the value of Counter will be, so I wrote a udf to calculate it. The important logic of the udf is
SELECT @MaxCounter = max(Counter)
FROM SchoolServices
WHERE (EmployeeID = @EmployeeID) AND (School = @School) AND (DATEPART(year, ServiceDate) = @ServiceYear) AND
(DATEPART(month, ServiceDate) = @ServiceMonth) AND (DATEPART(day,ServiceDate) = @ServiceDay)
IF @@Error = 0
BEGIN
IF @MaxCounter is null
SET @NextCounter = 1
ELSE
SET @NextCounter = @MaxCounter + 1
END
ELSE
BEGIN
SET @NextCounter = 0
END
This is what the stored procedure looks like:
CREATE PROCEDURE stp_PopulateWorkdays
@CalendarYear char(4),
@user-id varchar(9),
@ReturnError int output,
@InsertCount int output,
@UpdateCount int output
AS
BEGIN TRAN
INSERT INTO SchoolServices
(EmployeeID, School, ServiceDate, Counter, ServicePeriod, ServiceType, ServiceReason, Status, StatusBy, StatusOn, CreatedBy, CreatedOn, ServiceHours, ServiceDesc, Remarks)
SELECT
rs.EmployeeID, rs.School, w.WorkDate, dbo.fnNextCounter(rs.EmployeeID, rs.School, w.WorkDate), rs.ServicePeriod, rs.ServiceType, rs.ServiceReason, rs.Status, @user-id, GETDATE(), @user-id , GETDATE(), rs.ServiceHours, rs.ServiceDesc, rs.Remarks
FROM RegularSchedule rs INNER JOIN
Workdays w ON rs.DayOfWeek = w.DayOfWeek AND rs.StartDate <= w.WorkDate AND rs.EndDate >= w.WorkDate
WHERE rs.CalendarYear = @CalendarYear and rs.CompletedDate is null
Select @ReturnError = @@Error, @InsertCount = @@RowCount0
If @ReturnError > 0
begin
ROLLBACK
Return
end
UPDATE RegularSchedule
SET CompletedDate = getdate()
WHERE CalendarYear = @CalendarYear and CompletedDate is null
Select @ReturnError = @@Error, @UpdateCount = @@RowCount
If @ReturnError > 0
begin
ROLLBACK
Return
end
COMMIT TRAN
Return
So what's the problem? When I run the stored procedure, in production, I get the following error message:
Violation of UNIQUE KEY constraint 'IX_SchoolServices'. Cannot insert duplicate key in object 'SchoolServices'.
I realize that my problem is most likely that the UDF can't calculate the correct counter because at the time it's looking at SchoolServices, the UDF can't see the rows that the stored procedure has created because they haven't been committed to the table yet. So I'm thinking that I'm going to have to rewrite this using a cursor, but I'm sure as heck not doing that in production, so the first thing I need to do is copy all of this data to test. I cleaned out all of the tables, exported the data from production to test, made sure the SP and UDF matched production, and then ran the SP, just to make sure that I got the same error. And, go figure, I didn't. In fact, I didn't get any error at all. All of the rows were created correctly, including the computed Counter, with no unique key violation in sight. So now I'm thinking that somehow, in my test environment, my UDF is aware of the as-yet-uncommitted transactions, and can therefore compute Counter correctly, but the same is not true in production.
So my questions are:
1. Am I correct in my theory about the scope issues of uncommitted transactions?
2. How can I encourage my UDF in production to be aware of the as-yet-uncommitted transactions in SchoolServices?
Thanks so much for your help.
MattieNH
-
How about avoiding the udf and using this select ?
SELECT rs.EmployeeID, rs.School, w.WorkDate
, isnull((select max([counter]) + 1
FROM SchoolServices
WHERE EmployeeID = rs.EmployeeID AND School = rs.School AND ServiceDate = w.WorkDate )
, 0 )
, rs.ServicePeriod, rs.ServiceType, rs.ServiceReason, rs.Status
, @user-id, GETDATE(), @user-id , GETDATE(), rs.ServiceHours, rs.ServiceDesc, rs.Remarks
FROM RegularSchedule rs
INNER JOIN Workdays w
ON rs.DayOfWeek = w.DayOfWeek
AND rs.StartDate <= w.WorkDate
AND rs.EndDate >= w.WorkDate
WHERE rs.CalendarYear = @CalendarYear
and rs.CompletedDate is null
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks so much for the reply.
While I like the replacement of the UDF, I still get the same violation of unique key constraint message, and no inserted rows. (I also get a 'Warning: Null value is eliminated by an aggregate or other SET operation.' message, but that doesn't seem to be causing a problem).
I can't help but think that the fact this works in one database but not in another is a clue to the solution.
MattieNH
-
maybe just perform the select so you can detect and verify the duplicates
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
That's definitely the option of last resort. I'd have already done that, except that it works as written on one of my test servers.
I've been trying to isolate differences between the servers and/or databases to see if I can replicate for this SP only what's making it work in one environment. So far, the only thing I've been able to come up with is that service pack 3 is installed where I get the violation, but I still have the original RTM version where it inserts as required.
Mattie
-
IF [is your sp3 on a multi-processor box ? ] = 1
begin
if [Is it a sql2k standard edition ? ] = 1
begin
if [did you apply the hotfix Q814509 for multiproc boxes ?] = 1
maybe you'll need to do so.
end
end
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Me? The person who took all afternoon to figure out how to identify any difference between the machines?
Seriously though, thanks for giving me something to track down and try.
Mattie
-
Oh well, the answer to your questions about the machine on which the SP is working:
It's configured to handle multi-processing, but it only has one processor at present.
It is standard edition, as opposed to the personal edition where the SP worksthe way I want it to.
It does have the MS03-031 security patch applied that claims to include hotfix 814509. The SQL version is 8.00.818.
Besides the Version difference, the only other difference is the physical memory, which is 1024 on the patched (production) server, and 511 on the unpatched (test) server.
I really appreciate your willingness to keep pondering this issue.
Mattie
-
I have done some testing now.
The problem is that the select which is done to perform then insert, is performed before the actual insert is being done.
This way your new counter always is the same for tuples.
I guess you may want to write a while-proc or cursor so you can perform the inset row by row. Then your counter will be raised with each row. (have a look at the example included)
Is it mandatory to have a running counter for each combinated other part of the unique key set ?
If not you could try to integrate identity
, then this problem does not occur and you can perform the inserts like you do now.e.g. :
set nocount on
create table #Test_1 (Descr varchar(128) not null default(''), DtIsrt datetime not null default getdate())
create table Test_2 (idno int identity(1,1) not null primary key,Descr varchar(128) not null default(''), DtIsrt datetime not null default getdate(), RunNo int not null, DtIsrtT2 datetime not null default getdate())
go
declare @var varchar(128)
declare @ix as int
insert into #Test_1 (Descr) values ('A')
set @var = 'B'
set @ix = 0
while @ix < 10
begin
insert into #Test_1 (Descr) values (@var)
WAITFOR DELAY '00:00:01'
set @ix = @ix + 1
end
insert into #Test_1 (Descr) values ('C')
insert into #Test_1 (Descr) values ('D')
set @var = 'E'
set @ix = 0
while @ix < 5
begin
insert into #Test_1 (Descr) values (@var)
WAITFOR DELAY '00:00:01'
set @ix = @ix + 1
end
select * from #Test_1
begin tran
if 1 = 0 -- use while- or cursor-path
begin
declare @rowcount integer
set @rowcount = 1
while @rowcount > 0
begin
insert into Test_2 (Descr , DtIsrt, RunNo)
SELECT top 1 T1.Descr
, T1.DtIsrt
, isnull((select max(M.RunNo) + 1
FROM Test_2 M
where M.Descr = T1.Descr)
, 1 )
FROM #Test_1 T1
left join Test_2 T2
on T1.Descr = T2.Descr
and T1.DtIsrt = T2.DtIsrt
where T2.DtIsrt is null
order by T1.Descr, T1.DtIsrt
set @rowcount = @@rowcount
end
end
else
begin
declare @Descr varchar(128) , @DtIsrt datetime
declare csr_1 cursor for
Select Descr , DtIsrt
FROM #Test_1 T1
order by Descr, DtIsrt
open csr_1
-- read first row
FETCH NEXT FROM csr_1
INTO @Descr , @DtIsrt
WHILE @@FETCH_STATUS = 0
BEGIN
insert into Test_2 (Descr , DtIsrt, RunNo)
SELECT @Descr
, @DtIsrt
, isnull((select max(M.RunNo) + 1
FROM Test_2 M
where M.Descr = @Descr)
, 1 )
-- read next
FETCH NEXT FROM csr_1
INTO @Descr , @DtIsrt
end
end
select *
from Test_2
-- commit tran
-- rollback tran
-- clean up testcase
-- drop table #Test_1
-- drop table Test_2
--
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks again for responding. As I've mentioned, using a cursor is definitely my option of last resort, but your code will be a HUGE help if it comes to that.
I'm posting the DDL for the tables and the function at issue, in case it is environmental. Here it is, in all its glory.
/****** Object: Default dbo.Empty String Script Date: 1/24/2003 3:03:37 PM ******/
create default [Empty String] as ''
GO
/****** Object: Default dbo.Empty Number Script Date: 1/24/2003 3:03:37 PM ******/
create default [Empty Number] as 0
GO
/****** Object: Table [dbo].[RegularSchedule] Script Date: 12/23/2004 11:04:55 AM ******/
CREATE TABLE [dbo].[RegularSchedule] (
[CalendarYear] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[EmployeeID] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[School] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DayOfWeek] [tinyint] NOT NULL ,
[ServicePeriod] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ServiceType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ServiceReason] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Status] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ServiceHours] [numeric](3, 1) NOT NULL ,
[ServiceDesc] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Remarks] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompletedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SchoolServices] Script Date: 12/23/2004 11:04:56 AM ******/
CREATE TABLE [dbo].[SchoolServices] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[School] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ServiceDate] [datetime] NOT NULL ,
[Counter] [tinyint] NOT NULL ,
[ServicePeriod] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ServiceType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ServiceReason] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClaimedMiles] [numeric](5, 1) NOT NULL ,
[ServiceHours] [numeric](3, 1) NOT NULL ,
[ServiceDesc] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Remarks] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServiceAmt] [numeric](5, 2) NOT NULL ,
[MileageAmt] [numeric](5, 2) NOT NULL ,
[MileageTaxableInd] [bit] NOT NULL ,
[TollsAmt] [numeric](5, 2) NOT NULL ,
[FY] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayPeriod] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchedulingUID] [int] NULL ,
[CreditedSchool] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StatusBy] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StatusOn] [datetime] NULL ,
[CreatedBy] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedOn] [datetime] NULL ,
[ModifiedBy] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModifiedOn] [datetime] NULL ,
[CancelledBy] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CancelledOn] [datetime] NULL ,
[CancelledDesc] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuthorizedBy] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuthorizedOn] [datetime] NULL ,
[PaidOn] [datetime] NULL ,
[BilledCourt] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TravelFrom] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Workdays] Script Date: 12/23/2004 11:04:56 AM ******/
CREATE TABLE [dbo].[Workdays] (
[CalendarYear] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WorkDate] [smalldatetime] NOT NULL ,
[DayOfWeek] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RegularSchedule] WITH NOCHECK ADD
CONSTRAINT [PK_RegularSchedule] PRIMARY KEY CLUSTERED
(
[CalendarYear],
[StartDate],
[EmployeeID],
[DayOfWeek]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[SchoolServices] WITH NOCHECK ADD
CONSTRAINT [PK_SchoolServices] PRIMARY KEY CLUSTERED
(
[UID]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[Workdays] WITH NOCHECK ADD
CONSTRAINT [PK_Workdays] PRIMARY KEY CLUSTERED
(
[CalendarYear],
[WorkDate]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[SchoolServices] WITH NOCHECK ADD
CONSTRAINT [IX_SchoolServices] UNIQUE NONCLUSTERED
(
[EmployeeID],
[School],
[ServiceDate],
[Counter]
 
ON [PRIMARY]GO
setuser
GO
EXEC sp_bindefault N'[dbo].[Empty Number]', N'[SchoolServices].[ClaimedMiles]'
GO
EXEC sp_bindefault N'[dbo].[Empty Number]', N'[SchoolServices].[MileageAmt]'
GO
EXEC sp_bindefault N'[dbo].[Empty Number]', N'[SchoolServices].[MileageTaxableInd]'
GO
EXEC sp_bindefault N'[dbo].[Empty Number]', N'[SchoolServices].[ServiceAmt]'
GO
EXEC sp_bindefault N'[dbo].[Empty Number]', N'[SchoolServices].[ServiceHours]'
GO
EXEC sp_bindefault N'[dbo].[Empty Number]', N'[SchoolServices].[TollsAmt]'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: User Defined Function dbo.fnNextCounter Script Date: 12/23/2004 11:08:26 AM ******/
CREATE FUNCTION dbo.fnNextCounter ( @EmployeeID char(9), @School char(4), @ServiceDate datetime)
RETURNS integer AS
BEGIN
DECLARE @MaxCounter int
DECLARE @NextCounter int
DECLARE @ServiceYear char(4)
DECLARE @ServiceMonth varchar(2)
DECLARE @ServiceDay varchar(2)
SET @ServiceYear = year(@ServiceDate)
SET @ServiceMonth = month(@ServiceDate)
SET @ServiceDay = day(@ServiceDate)
SELECT @MaxCounter = max(Counter)
FROM SchoolServices
WHERE (EmployeeID = @EmployeeID) AND (School = @School) AND (DATEPART(year, ServiceDate) = @ServiceYear) AND
(DATEPART(month, ServiceDate) = @ServiceMonth) AND (DATEPART(day,ServiceDate) = @ServiceDay)
IF @@Error = 0
BEGIN
IF @MaxCounter is null
SET @NextCounter = 1
ELSE
SET @NextCounter = @MaxCounter + 1
END
ELSE
BEGIN
SET @NextCounter = 0
END
RETURN @NextCounter
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
setuser
GO
Again, thanks to everyone for their help.
Mattie
-
If the only purpose of the counter is to make the index on employeeid, school and date unique why not use the identity field as part of the index to preserve uniqueness?
Terri
TerriTo speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.] -
That's an excellent point. Unfortunately, it's not my design, and changing it is out of my control.
Mattie
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply