Re: primary keys of varchar2

In article <DunowB.ABs_at_auto-trol.com> brobes_at_auto-trol.com (Brooke Besser) writes:

   I need some advice on the merits of creating primary keys of type VARCHAR2.    For example, I have a table that has a unique name that is VARCHAR2(20). I    also have a numerical sequence in the table so as to not have to create an    index on the name field. The question is, is this necessary? Are the indexes    for a character field considerably slower (or slower at all) than an index    created on an INTEGER field?

   There is also the issue of inserting these long character strings all over the    database as foreign keys.

   I would appreciate comments from any experienced Oracle users.

--response follows this line--
By its definition, a primary key is *the* attribute by which the record is identified. As the primary (read: only) means of correctly identifying the record, it should be indexed to ensure uniqueness if not for performance.

The numerical sequence field sounds like a surrogate key. Thats fine but what you have now are two candidate keys. One of these needs to be selected as the primary key. For performance reasons, you as the RDBMS designer may elect to use the surrogate key as the primary key and that's cool. The only caveat I must stress is that you defined the varchar(2) field as the primary key. I assume that the business regards this attribute as being the one true "primary key" which by definition must be unique. Therefore to enforce the business requirement of uniqueness of the varchar(2) field, it should be uniquely indexed. But for performance reasons you may wish to use the integer surrogate key as the physical primary key.

That decision dependes upon concerns of disk space saved. The use of the surrogate key shouldn't severely impact performance of queries as both fields are indexed.

I hope I have helped.

Russell Searle
Business Analyst / Data Modeller Received on Wed Jul 17 1996 - 00:00:00 CEST