-
Feb 20th, 2012, 05:37 AM
#1
Thread Starter
Addicted Member
[RESOLVED] SQL Server 2008 Table question?
Morning,
I've got a student table which i add and delete from. My primary key is StudentID.
the primary key ID numbers are 1,2,4,6,8. The missing 3,5,7 ID numbers are records that i have deleted. How can make sure that these ID numbers are re-arranged once ive deleted a record?
so for eg, when i delete ID number 5 from 1,2,3,4,5,6 i would like the table to reset the ID numbers so that once 5 is deleted, ID number 6 would become 5 and so on.
AlL responses will be appreciated.
ta
-
Feb 20th, 2012, 06:03 AM
#2
Re: SQL Server 2008 Table question?
Noone uses databases that way. Don't do it. Once you assign an ID to a record. That is the ID for that record and it should stay the ID for that record. If you delete a record then the ID for that record should simply remain unassigned.
In fact, most people will tell you that, in many, if not the majority of, cases you should not even delete the record. If you delete a student record then every single record related to that student would have to be deleted as well. It would be as if that student never existed. What sort of learning institution would want to eradicate all trace of their past students?
What exactly do you hope to gain by this reassignment of IDs? Even using the 'int' data type for your ID you have capacity for 2,147,483,647 records in a table. Are you expecting more students than that? If that number may not be enough then you can use 'bigint' instead, which can accommodate 18,446,744,073,709,551,615 records. You'd need a very busy campus for a very long time to have more students than that.
-
Feb 20th, 2012, 06:32 AM
#3
Thread Starter
Addicted Member
Re: SQL Server 2008 Table question?
Originally Posted by jmcilhinney
Noone uses databases that way. Don't do it. Once you assign an ID to a record. That is the ID for that record and it should stay the ID for that record. If you delete a record then the ID for that record should simply remain unassigned.
In fact, most people will tell you that, in many, if not the majority of, cases you should not even delete the record. If you delete a student record then every single record related to that student would have to be deleted as well. It would be as if that student never existed. What sort of learning institution would want to eradicate all trace of their past students?
What exactly do you hope to gain by this reassignment of IDs? Even using the 'int' data type for your ID you have capacity for 2,147,483,647 records in a table. Are you expecting more students than that? If that number may not be enough then you can use 'bigint' instead, which can accommodate 18,446,744,073,709,551,615 records. You'd need a very busy campus for a very long time to have more students than that.
sorry maybe i didnt make it clear in my first post but i had some records inserted for testing purposes and deleted them all. Now when i was adding a new valid record the StudentID number was starting from the last deleted record ID number.
Got it working now. Deleted all records and ran the following:
Code:
dbcc checkident ( student,reseed, 0)
now new records start at StudentID 1.
By the way, you made a good point about not deleting student records. i will instead archive old student records rather than deleting them.
thanks for your response
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|