Re: How long can/should a primary key be?

>Could anyone tell me about things I should consider when defining a long
>primary key. We have a location table which will be 32 characters in
>length. The key will consist of an account number, and several address
>elements (house number, zip code etc.)

My suggestions would be to make a compound key. Don't store all this imbeded
information in one field. If you do, you'll have to do parsing, etc. to just figure out
portions of the key. I would therefore put each item of information into it's own
column and define the type of column appropriately. Then, define the primary
key to be a compound key -- i.e. if you defined the following: account_nbr number(4),
house_nbr number(7)
zip number(9)
extra varchar2(7),
etc.

... then your primary key would be (account_nbr, house_nbr, zip, extra, ...). Then, if you
truly need all this information stored in one field (for use with old, archaic host based systems
which required you to do it this way) add another field to hold all this inforamation in one
column --- but DON'T make it the primary key. Just put an index on it to allow quick retrieval.

Hope this helps.
Eric Dirst
BSG Alliance/IT
alt. address: eric_dirst_at_notes.bsginc.com

Any opinions expressed here are not necessarily the opinions of my employer. Received on Sat Mar 18 1995 - 19:00:09 CET