Post reply

Cannot add a primary key

  • 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.:)

  • 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.

  • 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

  • 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...;)

  • 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]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply