DB: To use identity column or not?

Question

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?

Answer 1

Does it matter that it is specific to the Microsoft platform? Is your application meant to be portable, i.e. are you planning to migrate it to mysql/postgres/whatever?

Answer 2

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!)

Answer 3

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.

Answer 4

Use Identity column!

It do separate your "Application Logic" from "Business Logic"

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

Answer 5

This is one of the problems I face with Oracle, and could be anybody that has SqlServer background. I was developing a code generator tool (for stored procedure) for Oracle, and I wanted to detect identity columns. It was a difficult task because oracle corp does not store information on column that uses a particular sequence.

I tried to manage though ALL_SEQUENCES and ALL_TRIGGER_COLS metadata tables.

cheer!

Ojulari Hakeem