Re: Selecting a Primary Key for the table.

From: RSH <RSH_Oracle_at_worldnet.att.net>
Date: Tue, 05 Mar 2002 01:06:30 GMT
Message-ID: <qSUg8.15330$gK2.1073423@bgtnsc04-news.ops.worldnet.att.net>

Um,wait a sec here. Has Oracle changed so radically in 9i that one can have more than one Primary Key? Does that not obviate the meaning of the term?

When I learned Oracle at my great-grandfather's knee, you could have only one PK per table, but that key could be comprised of one or more columns, up to what used to be 16, but maybe that changed too.

Good PK construction means not only uniqueness, but usefulness as an index in its own right; so taking as an example (okay it's a lousy example oh ye nitpickers) a table (good place to start...):

EMP_MAST

emp_lnam
emp_fnam
emp_mnam
emp_ssn
emp_salgrade
emp_status (C=current, R=retired, T= temp dis, L=LTD, A=Leave, etc)

etcetera etcetera

One could make a composite Primary Key of the first four columns with reasonable certainty of uniqueness. One might even start the table off with SSN first rather than name. However, using indices / keys works from left to right, so if you often look up EMPs by name and not so often by SSN, lastname, firstname, middle name and THEN SSN would give you a PK and associated index that would help searches; take up space, too!

The time when a sequence number comes in handy is when the collection of data that makes a row unique overwhelms the limits of PK's, or takes an outrageous amount of indexspace, and the table you wish to construct and refer to has huge numbers of rows.

Then, you might wish to consider a process that maintains a table of the columns of interest:

GL_LOOKUP
gl_co_id
gl_unit_id
gl_SOBP (Set of Books Partition, never mind)
gl_jdate

etc
and somewhere, anywhere, have a column like GL_RECORD_ID
and have it populated by a sequence number in your process

so that you aren't carrying the backbreaking burden of keys and indexes on constantly repetitive information for millions of rows

your corresponding table might have

GL_RECORD_ID as its PK
gl_ytd
gl_starting_balance
gl_cur_month

etc

Just don't EVER mess up the sequence numbers or be careless with the logic, or all you will have is a huge pile of useless data, since the sequence number is the "only way in", by navigating through your key field table and arriving at a list of sequence numbers that will be direct hits to retrieve from Big Scary Table.

This works.

Good luck.

RSH. "Ram" <rprakashg_at_hotmail.com> wrote in message news:cd367c26.0203040822.5a4adfd5_at_posting.google.com...
> I have a question regarding selecting a primary key for the table.
> Is it a good design to have multiple primary keys as compared to a one
> primary key auto generated in pre-insert trigger from a sequence and
> creating unique constraints on columns that need to be unique.
>
> Oracle documentation says try a stick with single primary key as far
> as possible. Our DBA's seem to have an alternative point of view. I'd
> like to hear from other DBA's out there.
>
>
> TIA - Ram
Received on Mon Mar 04 2002 - 19:06:30 CST