Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Question Unanswered: 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

    GO

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Posts
    39
    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.
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What does the sproc bcipCreateCommitment do?

    It's in the insert trigger...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Posts
    39
    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

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I got it....


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

    Code:
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2003
    Posts
    39

    Thumbs up

    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

    Code:
    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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...

    Code:
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    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.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2003
    Posts
    39
    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)

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •