QuestionAlright, what the heck are identity columns in RDBMSs for?
      – BorgClown, 2011-03-20 at 02:34:33   (8 comments)

On 2011-03-20 at 02:40:04, BorgClown wrote...
*RDBMSs* sigh. Anyway, I keep stumbling on this very common practice among .Net developers, and everywhere I look, I mostly find how to create and manage identity columns but not why should they be used. I understand this should be posted somewhere else, like StackOverflow, but I did it here, so what?
On 2011-03-20 at 15:18:57, Lee J Haywood wrote...
From what I can tell, they're the same as auto-incrementing keys in MySQL (or very similar). Discussionator uses those, for example for the topic ID field. Every time you add a topic a new is created and the RDBMS automatically assigns it a key value that is one higher than that of the last record inserted (i.e. 875 for this topic). They're extremely useful because they're atomic, so you don't have to first select/increment a value and then insert a new record (and possibly store the new highest value) - which would require a transaction. Discussionator doesn't bother to be transactional but works perfectly well all the same.
On 2011-03-20 at 21:31:45, BorgClown wrote...
I've read that autoincrementing fields can skip numbers in special cases. ┬┐would it bother you if DNr had gaps in the topic numbers?
On 2011-03-20 at 22:41:51, BorgClown wrote...
Looking around, I found this article about why they should not be used all the time. Starting to make sense. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-putting-an-identity-column-on-every-table.aspx
On 2011-03-21 at 22:59:21, Lee J Haywood wrote...
It's about key generation, rather than just identification. As the article says, if you already have foreign keys that uniquely identify an entity you should use them. Otherwise, it's typically a pain to generate a unique value for each row - auto-incrementing keys are invaluable then. No-one would notice if there were gaps in the topic IDs. Uninteresting topics are excluded from the Google index anyway, for example. Gaps might be an issue with an audit table I have in another system though, where it'd look like someone had deleted a record to hide something.
On 2011-03-23 at 07:19:01, BorgClown wrote...
How about invoice numbers, for example? Would it be OK to manually generate a unique integer instead of an autoincrementing integer field with a unique constraint? It has to be more efficient to use the autoincrementing field, but I don't quite like it.
On 2011-03-30 at 18:40:21, Lee J Haywood wrote...
You can generate your invoice numbers in a transaction, but it's a pain to avoid some other update coming along and generating the same ID. You end up having to loop when there's a collision / transaction error, and can write quite a lot of code. Auto-incrementing fields, on the other hand, require nothing more than the INSERT you wanted to do in the first place. You can even export the current ID for the table with the table definition/data.
On 2011-04-23 at 18:42:41, DigitalBoss wrote...
Identity columns, or keys (primary or foreign) are used in RDBMSs to insure that each record in a table has something unique about it. A unique identifier. Without unique identifiers, RDBMSs don't work. How can you relate a book to an author, if you can't uniquely identify the author? You know there are different authors with the same name.