Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by William J. Terrell
William J. Terrell
17,403 Points

Filling In IDs with SQL

This is just a little something I'm curious about, for the sake of keeping information clean.

Let's say, for example, that I have a table called "Users". This table has a column for FirstName, LastName, and ID#.

In this table, I have three entries.

FirstName:   LastName   ID#
Bob           Jones           1
Jim           Johnson         2
John          Beaux           3

The ID# is generated automatically, each time a new user is added to the database.

So, let's say that Jim Johnson, ID# 2, leaves the company.

His entry in the database is deleted when he terminates his employment, so, when I do a "select all" from the database, the only numbers returned are 1 and 3.

Now, let's say Rachel is hired in Jim's place.

What I would like to know is, how might I go about setting it up to where, when Rachel's information is entered into the database, it gives her an ID# of "2", so there is not a missing number there, or if that is a possibility.

I am still a novice when it comes to SQL, so I don't know if there is something that can be set up in a query when adding, or if this sort of thing would have to be set up on the database itself, but this seemed like one of those questions best asked by providing an example than trying to figure out what string of words to provide Google. :)

Thanks!

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

changed category to databases

1 Answer

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

This isn't really worthwhile.

Automatic values are generated from an internal sequence, so any gaps created by deletions would never get filled. Now you can manually override the automatic numbering and give a new record an explicit ID value, but it's a headache to keep track of these things and there's no advantage to efficiency in doing so.

But there's also a potential disadvantage to re-using the numbers. For example, if you were to join the employees table with last year's transactions, it might look like the new employee was responsible for the old transactions. So for this reason it might be advantageous to not re-use old numbers.

I would go one step further and never delete the old record at all. Instead I would have a column that represents status and one possible value of that column would mean "inactive" or "terminated". Then, you always have the data available to combine with historic event records, but you also know that the record does not reflect a current employee.

William J. Terrell
William J. Terrell
17,403 Points

Thanks!