Post reply

Can I retrospectively add a primary Key to a table that was build without one?

  • October 3, 2012 at 1:35 pm

    #265387

    As topic.

    << I am a self confessed SQL total noob but trying hard

    I am am helping a couple of people try to fix some major gremlins with a SQL 2005 MMORPG online game. In the process discovered that the main table that contains all the items (the things people can buy, sell, use in the game) shuffles when a new item row is added. The net effect is that the table allocates different attributes/values randomly to some items after each new record entry (after closure of the table).

    col1 contains a purely numeric ID field, I want to make col1 the primary key. It currently has a couple of duplicates (that I now know how to remove/replace etc) and a few gaps in the numeric sequence (which I don't think makes the slightest difference).

    col2 contains an item description field (mixed alpha/numeric and some specials characters including [ ] ( ) %. I thought % was a serious no no in SQL as it was a wild card. Should these characters be removed?

    Bottom line is, this table has no obvious Primary Key so can I add one? How do I trace a PK if one does exist. Cheers in advance.

    Peter

  • October 3, 2012 at 2:06 pm

    #1545202

    You can add a primary key this way:

    alter table dbo.MyTable

    add constraint PK_MyTable primary key (column list);

    Name it whatever you like. I name PK constraints "PK_" and the table name.

    Lack of a primary key won't make data move around from row to row. Unless you mean "the row with ID = 1 still has the same data in the rest of the columns, but now shows up after row ID = 2". Display order won't be fixed by adding a primary key. You have to specify "Order By" when you query it if you want the rows in a particular sequence.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • October 3, 2012 at 2:56 pm

    #1545228

    Many thanks 🙂

Viewing 3 posts - 1 through 3 (of 3 total)

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