Cannot add a primary key

• David Poluxt

  SSC-Addicted

  Points: 465

  More actions

  May 21, 2008 at 1:37 pm

  #207573

  Hello all,

  I keep getting this error message when I try to right click a column and set it as primary key:

  Unable to create index 'PK_MyTableName'.

  ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'PK_MyTableName' in the database.

  It was a day of experimenting. Earlier in the day, I had a starter version of MyTableName, which I deleted. I then had another version of MyTableName, which I then renamed to MyTableNameorig; I then created a new version of MyTableName and now I cannot create a primary key for it. ...I'm suspecting this deleting/renaming had something to do with this.

  Any suggestions would be appreciated. Thanks in advance.:)

• tosscrosby

  SSCoach

  Points: 18045

  More actions

  May 21, 2008 at 1:51 pm

  #818850

  Run the following to see if it returns anything. If it does, you have an index by that name! I'm assuming SQL2K. Let us know what you see.

  select * from sysindexes where name = 'PK_MyTableName'

  -- You can't be late until you show up.

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  May 21, 2008 at 2:24 pm

  #818865

  PK_MyTableName is probably the primary key for MyTableNameorig. Renaming a table doesn't rename it's indexes and constraints.

  Greg

• David Poluxt

  SSC-Addicted

  Points: 465

  More actions

  May 27, 2008 at 7:14 pm

  #820916

  Aha! I didn't realize that indexes and assignment of primary keys would not change along with table names. This is, uh, somewhat counter-intuitive.

  I took the path of least resistance, since I was still experimenting:

  I deleted all the tables I had created that day, and then recreated the table with the final structure that I wanted. The problem went away!

  Thank you! I'll know not to fall into *that* trap again...;)

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  May 27, 2008 at 8:46 pm

  #820935

  Yes, it would have been nice if SQL Server had dependent objects use dependent Namespaces, but that never happened.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]