Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2005
    Posts
    36

    Unanswered: Violation of PRIMARY KEY constraint

    I know what this error means is that you can not insert duplicate primary keys in the table but the thing is I am checking the rows if they do not exist then insert otherwise don't do any thing these are the lines I am writing in my strored procedure can someone please let me know what I am doing wrong here.

    If not exists
    (Select * From GGP WHERE
    FFECTIVE_DATE =@v_EFFECTIVE_DATE AND
    ASSET_ID= @v_ASSET_ID AND
    ASSET_TYPE = @v_ASSET_TYPE AND
    Value = @v_Value AND
    hour = @v_Hour)

    INSERT INTO GGP
    (ASSET_ID,ASSET_TYPE,Hour,Value,EFFECTIVE_DATE) values(@v_ASSET_ID,@v_ASSET_TYPE,@v_Hour,@v_Value, @v_EFFECTIVE_DATE)



    The exact error is
    Violation of PRIMARY KEY constraint 'PK_SP_GGP'. Cannot insert duplicate key in object

    Thanks in advance.
    Last edited by azshah; 09-21-05 at 09:46.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's an error (probably a typo in the post) in your EXISTS statement. Fancy reposting the correct SQL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2005
    Posts
    36
    Quote Originally Posted by pootle flump
    There's an error (probably a typo in the post) in your EXISTS statement. Fancy reposting the correct SQL?
    yeah sorry I forgot the where after GGP it is a typo I edited it now thanks for pointing it out.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Obviously one of the input variable values does not match one of the table columns you are comparing them to, so you need to find out which one it is.

    Maybe it's a type mismatch, maybe one of your date values is different, but something is causing the logic to determine that there is not a duplicate value when in fact there is, and the subsequent insert attempt violates your PK constraint.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Apr 2005
    Posts
    36
    Actually I have set AssetType, AssetID,Hour and date as the primary keys and you are suggesting that the primary keys values might be same but if the value field is different it will throw this error because I am comparing the value field in the SP and it is not the primary key.


    So I guess the stored procedure is fine it might be one of the values that are giving me the headache.

    Thanks for the reply.
    Last edited by azshah; 09-21-05 at 09:57.

  6. #6
    Join Date
    Apr 2005
    Posts
    36
    Thanks Tom it was the value filed that was giving the problem I changed it to the primary key (now I have got 4 primary keys) and that solved the problem.

    Thanks Again.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You have one primary key, known as a composite key, and your IF NOT EXISTS statement has to check ONLY for duplicate elements of the composite key. Do not include fields that are not part of your primary key in your NOT EXISTS statement.
    Last edited by blindman; 09-21-05 at 10:15.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do do this at all...you are potentially doubling your access to the database, which I think is not a good idea for performances, or best practices.

    Just handle the error.

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int, Col2 int, Col3 char(1), PRIMARY KEY (Col1, Col2))
    GO
    
    INSERT INTO myTable99(Col1,Col2,Col3)
    SELECT 1,1,'a' UNION ALL
    SELECT 1,2,'b' UNION ALL
    SELECT 1,3,'c'
    GO
    
    INSERT INTO myTable99(Col1,Col2,Col3)
    SELECT 1,1,'a'
    
    IF @@ERROR <> 0 GOTO Error
    
    PRINT 'No Capture'
    GOTO Leave
    
    Error:
    
    PRINT 'Capture'
    
    Leave:
    GO
    
    DROP TABLE myTable99
    GO
    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
  •