7

While designing a table my colleague here says that I should avoid identity column as it is specific to SQL Server and MS Access, But I differ with his views as it makes my coding simpler.

Should I use identity column or not? If not what is best way to create the identity columns from application code?

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
reva
  • 1,477
  • 2
  • 14
  • 23

4 Answers4

10

You can't completely divorce an application from the database vendor. If you do you won't be able to take advantages of whatever features your database provides you.

I'd say use the identity column. If you move over to Oracle (for example), you can use a Sequence. Hardly a big change.

I don't know what technology you're using, but one thing that would help is using a tool such as Hibernate or iBATIS (I think they're both available for Java and .NET) which separates you a bit from the database implementation details. Then if you change database vendor you won't need to change application code, just configuration. (In theory, at least!)

5

Use Identity column!

It does separate your "Application Logic" from "Business Logic."

Let's say you use "email" as primary key (which does make sense in term of "business logic"). You'll get into trouble when that email no longer exists and your user wants to edit your email.

zkan
  • 641
  • 1
  • 12
  • 27
Ekkmanz
  • 472
  • 1
  • 5
  • 15
3

Do not use identity column unless you're fully aware of its pitfalls and has no valid reason to use SEQUENCE or an emulated sequence generator.

Sequences are much more flexible and do not have the disadvantages/restrictions that Identity columns have.

From http://www.jumpingbean.co.za/blogs/mark/identity_autoincrement_fields_and_database_sequences :

The pain with auto-incrementing

Copying over data while preserving identity value

Auto-incrementing keys can become a pain when you need to copy whole tabels and preserve the primary key value. Trying to insert directly into an identity column will result in a error being raised. Typically the vendor provides some statements that allow you to temporarily drop the constraint so you can insert existing values. In MSSQL you can issue the command

"SET IDENTITY_INSERT products ON".

Other vendors will require you to drop the constraint and then re-enable it.

More pain- how to retrieve value of newly inserted rows?

In addition the server usually provides different ways to retrieve the identity column value for a newly inserted row. For MySQL this is the LAST_INSERT_ID() function and for MSSQL it is @@identity eg select @@identity.

MS SQL Server 2011 will support SEQUENCE.

If you use a RDBMS server that doesn't support SEQUENCE (like MSSQL pre-2011 or MySQL), there are two alternatives:

  1. Switch to PostgreSQL. This is the easiest option, really. You get enterprise-grade, fully open source RDBMS server. And you can have commercial support too if you want.
  2. Use a data access library that supports emulating SEQUENCE. JPA 2.0 implementations e.g. EclipseLink, Hibernate make this very trivial for you (TABLE strategy). It's not mutually exclusive with the above. Using JPA 2.0 with PostgreSQL will make your life so much easier than, say, raw JDBC with MySQL.
Hendy Irawan
  • 20,498
  • 11
  • 103
  • 114
  • I think if you add a new table called "IDENTITY" and store the latest ID for each table inside it, it would be pretty simple to say SET [tablename] +1 each time you need a new ID for a table. – BerggreenDK Apr 07 '11 at 11:08
2

As far as i am aware, every slightly serious RDBMS has some sort of unique numbering scheme per table.

  1. Access and SQL Server have identity columns
  2. MySQL has auto increment columns
  3. PostgreSQL has sequences
  4. sqlite has an implicit ROWID column
  5. Oracle has some sort of sequence though I'm not at all familiar with it

I mostly use it, theoretically it's not always a requirement but if you want to maintain referential integrity an int is less to store and easier to compare than a varchar, especially if your foreign keys would be more complex than a single column.

Kris
  • 40,604
  • 9
  • 72
  • 101
  • You're not familiar with sequencers in Oracle, so you decide that "every slightly serious RDBMS has a number scheme per table"? – Dave Van den Eynde Oct 09 '08 at 08:49
  • @Dave, 42 is always a number yet a number is not always 42. – Kris Apr 04 '12 at 20:39
  • yeah, no idea what you mean by that. Last time I checked Oracle had generators, much like PostgreSQL's sequences, which are not per table. – Dave Van den Eynde Apr 05 '12 at 08:57
  • @Dave, It illustrates that a statement, when taken out of context does not equate to the original meaning. Similar to what you did to my post in your first comment. On subject however; if any kind of row number is unique over an entire database, how then can it be not unique over a single table? I will stand by my assertion that Oracle is a serious RDBMS until proven wrong. I may not have used Oracles terms in my post, that's why I added the disclaimer. – Kris Apr 06 '12 at 04:11
  • 1
    I'm not suggesting that Oracle is not a serious RDBMS. I'm only pointing out that your assertion that that "every slightly serious RDBMS has some sort of unique numbering scheme per table" is false, as it is not "per table" in the case of Oracle, PostgreSQL and also Interbase/Firebird. One has to deal with this sort of conceptual differences as well, and not simply argue that every RDBMS as an 'identity column' of some sort. – Dave Van den Eynde Apr 06 '12 at 19:23