PRIMARY KEY constraint problem

I have an odd problem on something that used to work fine.
I have an SP that inserts a record into a table (Contract) with two keyed fields.

The keys are as follows:

ContractID and SeqID (Sequence)
These two keys make the records unique.

Ex:
ContractID SeqID
12345 1
12345 2
12345 3
etc...

Several weeks of using this procedure have been fine. Suddenly I started getting this error:

Violation of PRIMARY KEY constraint 'PK_contract'. Cannot insert duplicate key in object 'Contract'.
The statement has been terminated.

I verified that the values do not violate the constraints. In fact, I can type the exact information into the table directly without a problem.

Has anybody experienced this before?

Any help would be apprciated!

Here is the code in the SP;

CREATE PROCEDURE bcipNewContractSeq @.ContractID Char(10 )AS

DECLARE @.MaxSeqID int
DECLARE @.NewSeqID int

SELECT @.MaxSeqID = Max(SeqID) from Contract_Live..Contract WHERE ContractID = @.ContractID

SET @.NewSeqID = @.MaxSeqID + 1

--Copy Contract info for new seq with new seqid- record has default start and end dates
INSERT INTO [Contract_Live].[dbo].[Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract_Live].[dbo].[Contract]
WHERE [Contract_Live].[dbo].[Contract] .ContractID = @.ContractID

GOFirst...if it was working and now it's not...

Something changed...there are no mracles..

Did some one add a trigger?

Change the constraint?

Go to EM, right click on the table and script EVERYTHING and post it here|||Originally posted by Brett Kaiser
First...if it was working and now it's not...

Something changed...there are no mracles..

Did some one add a trigger?

Change the constraint?

Go to EM, right click on the table and script EVERYTHING and post it here

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

No trigger changes or constraint chnages (I checked them) . I am the only person modifying this database. Script is attached.|||What does the sproc bcipCreateCommitment do?

It's in the insert trigger...|||Originally posted by Brett Kaiser
What does the sproc bcipCreateCommitment do?

It's in the insert trigger...

Inserts a record into a table (tblCommitment) based in the INSERTED Contract record. Inserts the ContractID and SeqID. Commitment level defaults to 0 and CommitLevelID is the IDENTITY - incremental by 1:

SCRIPT...

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCommitment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCommitment]
GO

CREATE TABLE [dbo].[tblCommitment] (
[CommitLevelID] [int] IDENTITY (1, 1) NOT NULL ,
[ContractID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SeqID] [int] NOT NULL ,
[CommitLevel] [int] NOT NULL ,
[SysDateEntered] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCommitment] WITH NOCHECK ADD
CONSTRAINT [PK_tblCommitment] PRIMARY KEY CLUSTERED
(
[CommitLevelID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCommitment] WITH NOCHECK ADD
CONSTRAINT [DF_tblCommitment_CommitLevel] DEFAULT (0) FOR [CommitLevel],
CONSTRAINT [DF_tblCommitment_SysDateEntered] DEFAULT (getdate()) FOR [SysDateEntered]
GO|||I got it...

No way was this the way it was before....Unless all you ever did was add 1 additional

INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
GO

That code will try and insert n number of rows...all with the same dup key...

It's not trying to insert one that already exists...it's trying to insert many rows at the same time all with dup key...

just before the insert, take the select and add it before, and recompile it...you'll see what I'm saying...

It's a cheesy way, but you could say SELECT DISTINCT to eliminate your woes...|||Originally posted by Brett Kaiser
I got it...

No way was this the way it was before....Unless all you ever did was add 1 additional

INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
GO

That code will try and insert n number of rows...all with the same dup key...

It's not trying to insert one that already exists...it's trying to insert many rows at the same time all with dup key...

just before the insert, take the select and add it before, and recompile it...you'll see what I'm saying...

It's a cheesy way, but you could say SELECT DISTINCT to eliminate your woes...

>>>>>>>>>>>>>>>>>>>

I see it now! You pegged it. "After further review of the play..."
In test it worked fine and I may not have done more than one additional and now in the production where there is more than one record being created it is going to grab more than one. The answer to my problem is to use the previous SeqID in the where clause to pull ONE record only for the copy.

I need to get more sleep...

Thanks for your time to help the SQL'y impaired!

RLM|||Don't mention it...but why SELECT FROM the table at all...except to get the name...

Seems like your table is 2nd normal form though...

You should try to avoid repetitive data...should probably be in a separate table...

Try this...

INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT TOP 1 @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID

And why aren't you using IDENTITY?|||Originally posted by rmetz
>>>>>>>>>>>>>>>>>>>

I need to get more sleep...

RLM

Hi rmetz,
I am curious to know how come it was working in the first place. The same problem might have happened to you before.|||Originally posted by smasanam
Hi rmetz,
I am curious to know how come it was working in the first place. The same problem might have happened to you before.

It worked in the first place because there was only 1 row...

That's the only case scenario it would have worked under...

This underscores the need for extensive testing...

Also, a lot of times I'll put SELECTs in the code so I can step through the results I'm suppose to be expecting...that's how I found out what was up..

(Should've just jumped out at me though...what a scub I am)|||The reason for the redundant data is due to "Inherited Database application". Under normal circumstances (meaning my design) I would not have had this. The table should be broken in two with the Contract table being the "Header" record and the sequence entries in another table as "Contract Details" therefore eliminating the need to cary over the extra baggage...in a"perfect world".

As for testing...I do my best with the amount of time I am given. The app was in a beta test mode when the problem occured so we didn't damage anything too badly. The fix however only took this addition " AND SeqID = @.MaxSeqID" to pull the last unique record for the copy.

BTW, this app is going to be re-written and you can rest assured that proper normalization will be exercised.

Thanks for the eye opener Brett! I should have seen it too. But sometimes you just wind up in a tail chasing rut until someone throws a stick at ya.

Cheers!

>>>>>>>>>>>>>>>>>>>>>>>>>
Originally posted by Brett Kaiser
It worked in the first place because there was only 1 row...

That's the only case scenario it would have worked under...

This underscores the need for extensive testing...

Also, a lot of times I'll put SELECTs in the code so I can step through the results I'm suppose to be expecting...that's how I found out what was up..

(Should've just jumped out at me though...what a scub I am)|||Originally posted by rmetz
for the eye opener Brett! I should have seen it too. But sometimes you just wind up in a tail chasing rut until someone throws a stick at ya.


You telling me?

Hell, There been times...don't get me started...