identity columns

From: John <ru_re_re_move_zel_at_ecl_re_re_move_ipse.net>
Date: Wed, 2 Jan 2002 22:46:54 -0500
Message-ID: <JnQY7.359$8j1.340017305_at_news.netcarrier.net>

There seems to be a lot of talk in this NG about the good, bad and ugly aspects of primary keys - especially Identity columns. I've been working with sql databases for 5-6 years and the last 1.5 years I've spent designing databases from scratch, so I am far from any kind of expert. (lucky if I'm novice <s>)

I'd like to know - are you against the datatype "Identity" or are you really against having an "artificial" key in *every* case?

Here is simplified example from the database I'm currently working on: (I am using a relational DB - specifically MS Sql Server 2k - I have to work within certain constraints - lots of responses say "use OODB", or "use this product", those are not options for me)

I have a table called Employee - the candidate primary key is the Employee's Internet Email Address, but I resorted to using an Identity column called EmployeeId.
Furthermore - I also have a table called "Order" where 1 Employee can create many orders - so - if the Employee primary key is EmailAddress does it make sense to store something that large in the "Order" table? There are many other tables where EmployeeId is a foreign key - am I wrong to think I shouldn't be storing something large like email address all over?

I guess the advantage of using something like "EmailAddress" would be that I don't have a "nonsense" number in my database - however - if EmailAddress is a primary key then I can't change an Employee EmailAddress without changing it *everywhere* - in fact - I'm not sure which Employee attribute could never change.

Granted - I only use Identity columns as a "Last Resort" - for example: An order can be in one of three states: Pending, Approved, Denied So - I have a table called OrderStatus where the primary key is char(1) and the values are 'P', 'A', 'D' It's nice because there is meaning to the primary key - why didn't I do that for Employee? There are 100,000 of them. Names can conflict with each other - I can't guarantee I'll always have an email address, etc. So - I resorted to using an Identity column.

One more question - sometimes there are two columns making a table unique - without creating a single artificial key - how would you *easily* create a foreign key?

Thanks for any and all replies - Received on Thu Jan 03 2002 - 04:46:54 CET