Post reply

Refresh primary key

  • August 14, 2003 at 2:41 am

    #84118

    hi

    Could someone help me in following:

    I have a system data table where I add system data, e.g. Title table have Mr, Miss, Mrs, Dr etc. the table has column called ID and its set to primary key. if i want to add new data 'Ms' between e.g. 'Mr' and 'Miss' it wouldn't refresh ID column and give me:

    1. Mr, 3. Ms, 2. Miss

    Is there any way i can refresh the ID column so it will like:

    1. Mr, 2. Ms, 3. Miss

    Many thanks

  • August 14, 2003 at 2:52 am

    #469880

    Hi Khalidhussain,

    quote:

    hi

    Could someone help me in following:

    I have a system data table where I add system data, e.g. Title table have Mr, Miss, Mrs, Dr etc. the table has column called ID and its set to primary key. if i want to add new data 'Ms' between e.g. 'Mr' and 'Miss' it wouldn't refresh ID column and give me:

    1. Mr, 3. Ms, 2. Miss

    Is there any way i can refresh the ID column so it will like:

    1. Mr, 2. Ms, 3. Miss

    two methods I can think of for this:

    1. Maybe you can add a second int field 'Sorting Order', place there in the values in which the data should be sorted. Might be useful ?!? with respect for future enhancements.

    2. DROP PrimaryKey constraint (and maybe IDENTITY property off the field, e.g. make it a simple int, change the values the way you like, add back PrimaryKey again (and IDENTITY).

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • August 14, 2003 at 3:10 am

    #469883

    thank you, it works.

  • August 14, 2003 at 3:31 am

    #469885

    What about moving the data to a temporary table, resorting it into the order you want it, truncating the Title table and then re-inserting the data?

  • August 14, 2003 at 3:44 am

    #469887

    Hi rachelc,

    quote:

    What about moving the data to a temporary table, resorting it into the order you want it, truncating the Title table and then re-inserting the data?

    works also.

    Another approach, same result.

    More useful than mine when there are many records to be refreshed

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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