Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    14

    Unanswered: Violation of PRIMARY KEY constraint

    When I try to insert data into a table (let's just call it MyTable for this post), I suddenly get the following error.

    Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'MyTable'.

    My table does have a primary key field named 'id', which is an auto-incrementing BIGINT. The last record I successfully inserted received 14 in the id field, so I'm assuming the database is trying to assign 15 to the next. Unfortunately, there is already a record with an id of 15; the next available id is 21.

    Is there a way to avoid these primary key collisions?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That is not what is happening.

    Not sure what IS happening, but guarantee it is not as you describe it.

    You don't have SET IDENTITY option turned on, do you?
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please show the exact query that's causing the problem?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2005
    Posts
    14
    Quote Originally Posted by r937
    could you please show the exact query that's causing the problem?
    I tried this Insert statement in the Query Analyzer, and I got the error.

    Code:
    insert into epTherapyProgressNote (dateCreated, PatientID)
    VALUES ('4/10/06', 4302)

  5. #5
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb Try this

    I got this error earlier too

    Try this command
    dbcc checkident(MyTable)

    This should resolve your problem, if not come back.

  6. #6
    Join Date
    Dec 2005
    Posts
    14
    Quote Originally Posted by SPere
    I got this error earlier too

    Try this command
    dbcc checkident(MyTable)

    This should resolve your problem, if not come back.
    I tried this, and it worked. I am able to insert again, starting with ID 21.

    Thanks for your help.

Posting Permissions

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