June 6, 2012 at 5:20 am
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
June 6, 2012 at 5:25 am
June 6, 2012 at 5:30 am
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??
June 6, 2012 at 5:30 am
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
June 6, 2012 at 5:48 am
thx Anthony that is better. I will cry it.
June 6, 2012 at 6:17 am
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..
June 6, 2012 at 6:31 am
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.
June 6, 2012 at 6:38 am
Why do you care if there is a gap in the id's? Is there a business case for this?
June 6, 2012 at 9:27 am
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:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply