How to bring all Ids of my table in Order

  • Hey Guy Please i have some request.

    i have a table Person with (Person_ID, Person_Name and Person_Age).

    My Person_ID are: 1,2,3,4,5,6 like this:

    ID Name Age

    1A29

    2B30

    3C 40

    4D50

    5E30

    6F30

    I cancel one Person_ID

    now i have in my table 1,2,4,5,6 like this

    ID Name Age

    1A29

    2B30

    4D50

    5E30

    6F30

    How can i bring it in order

    like this 1,2,3,4,5

    Has somebody an Idea?? please

  • Hey Clayman thx for the link but i also check it but it doesn't help

    i try it before that why i ask now in the forum.

    dbcc checkident ('Person',reseed,0) --Don't change

    dbcc checkident ('Person',reseed,2) --Don't change

    dbcc checkident ('Person',reseed,6) --Don't change

    has somebody another idea??

  • You have to be careful with playing with id columns, especially if you have foreign key constraints. Identity gaps are common in all databases, especially in transactional scopes where ID's are inserted and rolled back.

    I would personally leave the ID's as they are as the cause no issue with gaps in them.

    If you want to do this, you will want to take a look at row_number() and then build an update statement based on that to update the ID's

  • thx Anthony that is better. I will cry it.

  • Good point but with a FK in place the op would have probably gotten an error msg while trying to delete? Anyway I'm a bit confused what he is after here..

  • clayman (6/6/2012)


    Good point but with a FK in place the op would have probably gotten an error msg while trying to delete? Anyway I'm a bit confused what he is after here..

    Not nessesarily, there might not of been any referential data for the ID that was deleted so it will allow the delete, but if there was referential data for ID 6 and this is changed to ID 5 then an error is thrown as it will violate referential integrity, unless the FK's have CASCADE UPDATE/DELETE set on them of course.

    I think its a "beautification" exercise where the OP wants all ID's to be sequential which obivously in a real world setup is hard to acheive, as you will need to constantly update reseed and check referential integrity with every transaction, especially when ID's are rolled back. Personally I cant think of a reason to do this, just live with the gaps in the ID its normal behavour.

  • Why do you care if there is a gap in the id's? Is there a business case for this?

  • 1. the name of a table is a collective or plural noun because it models a set of entities. You just told us that your database has only one person!

    Personally I do prefer table names in plural form, but nothing wrong with using singletons. You can use any naming convention as long you stick to it. Nothing against fundamental concepts. More than that! When modeling data, entities should have singular names - at least according to IE, IDEF1X, etc.

    2. We do not record computed data when we can store the values from which the computation is made. That means an SQL programmer would have a birthdate to compute the age of a person.

    We do record computed data when it is appropriate, for example in denormalised database for reporting purposes.

    3. An identifier is an attribute of an entity; it is NOT the position of a row in a table. Go out to your automobile. Read the VIN off of the dashboard. That is an industry standard identifier. You use it at the DMV, insurance policies, etc. Identifiers do not change. Now look at the parking space number in the garage in which your car is currently parked. When you moved your car to another garage or parking space, did the VIN change? This is exactly what you are doing!

    Agree about identifiers, but bad example about cars VIN. I heard about garages (in East European countries) changing VIN's for some reason ... 🙂

    4. Since you don't know anything about RDBMS, then get a copy of the simplest intro book I know --

    http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905

    Or find information to read online for free, save money for nice holiday ... :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

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