Re: Identity Column Benefits

Using the Identity column for primary keys is going to be a disaster. The problem with the Identity column is - it does not really belong to your data in the database. It belongs to the table actually. As a result, if at all, when you export and import the same data, either it gives you an error, or much worse, creates new values for the primary key column. This will be disastrous as your foreign keys do not match any longer.

Actually Identity column is not a good database design. Your data should have some innate property that can uniquely identify the data in each row.

If you need to assign sequential numbers to your primary keys (for whatever reasons), install a trigger that takes the identity column as an input and makes a sequential primary key and installs in the primary key column. This way, your primary keys remain intact, even if you export and import data.

A useful feature of Identity column is proxying a rather unwieldy composite primary key (for eaxmple in web applications where you might need to pass the row identification info in a URL) with a identity based candidate key.

In most cases, the Identity column gets misused, this feature should be banished from all RDMSs.

-S.

In article <8PL35.11$SE3.2482_at_typhoon2.ba-dsg.net>,   "Alex" <uinspire_at_bellatlantic.net> wrote:
> Hello,
> I've just taken over the management of a project and I'm a bit
 concerned
> about one of the earlier design decisions that was made. We have
> quotations, stories, poems, etc... stored in a table called "Content".
> There are several tables hanging off of this main table such as
> "ContentSubject", "Subject", "ContentCategory", "Category". The
 primary key
> on the Content table is a field of type varchar that is a unique
 index.
> There is nothing special about this field's data, as it is only used
 as a
> "handle" for the data. The DB we are using is SQL 7.0. My concern
 is that
> I'm used to using the identity property for columns such as this, and
 I had
> never given much thought to doing it any other way. The biggest
 benefit I
> see is that the field is automatically incremented by the DB, rather
 than
> having the developers do it through code. I'm not really looking to
 switch
> to ID properties at this point, but I wanted to know if anyone out
 there had
> any compelling reasons to switch perhaps based on performance of ID
 columns
> when executing queries or joings, or simliar performance concerns
 with a
> field of type varchar. Please let me know.
>
> Thanks,
>
> Alex
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Jul 01 2000 - 00:00:00 CEST