Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: Auto Number PROBLEM

    I have a table namely Product with the following identity modifiers:-
    Column- "Num"
    Identity-Yes
    Identity Seed-1
    Identity Increment-1

    My problem is everytime i delete a row in the table Product, the Num column after the deleted row will not automatically replace the deleted Num column's data.
    Let say:

    Num Name
    30 Apple
    31 Orange
    32 Pineapple

    If I delete row with Num 31, the Num column after the deleted row will not change to 31 but remain 32.

    Num Name
    30 Apple
    32 Pineapple

    What can I do to ensure that the Num column (Num 32) will change to Num 31?

    Thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you could do is think very hard about what you seem to want to do

    suppose you have an Orders table, which contains customer orders for all products you've sold

    if you renumber the products in the products table, you must therefore renumber the Orders table too

    this could shut your database down for a few hours

    and why? what is the benefit?

    autonumbers should never be reassigned

    it's not as though you're going to run out of numbers

    you could realistically set your identity seed at 183,527,426 and your increment at 3,743 and you would still not run out of numbers for several centuries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Auto Number PROBLEM

    you can add a new column to your table

    RowNumber

    Id Fruit RowNumber
    31 Apple 100
    32 Banana 101
    33 Pineapple 102

    If you add a new fruit
    RowNumber = Count(*) of the table

    If you delete a fruit
    you update RowNumber=RowNumber-1 for all the fruits over the
    RowNumber of the deleted fruit

    If you delete the Banana

    Update Table Fruits Set RowNumber=RowNumber-1
    Where RowNumber>(Select RowNumber From Fruits Where Id=32)

    Delete Fruits Where Id=32

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Auto Number PROBLEM

    But some would say this is not a GREAT thing to do
    'cause it's not performant.

    But it will work

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would say it is not a great thing to do because of (a) the impact it has on related tables, and (b) the total absence of ROI

    also, not only do you have to update the fruits table, you also have to update the desserts table, the pie table, and any other table which has foreign keys to the fruit table

    and i repeat: what exactly is the benefit of doing it?

    if it is important that there be no gap in numbers, then i strongly suggest that an autonumber is the wrong design choice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    in my case there is no impact on other tables
    because I don't touch the ID number
    but the new independant column ROWNUMBER !!!

Posting Permissions

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