Re: Selecting a Primary Key for the table.

I assume you mean 'single-column' primary key and 'multiple-column' primary key.

There have been two historical arguments against using surrogate primary keys - i.e. generating unique meaningless numbers to replace primary keys:

1. You are not supposed to change the contents of primary keys, but eventually someone always wants to, so use meaningless keys and the problem goes away. This is alternatively phrased as "it's hard to get the right definition for a primary key". In either version the argument is bad. If there is a natural key it will be useful as the natural key, and if you make a mistake in analysis, you will have to pay for it.
2. It saves space. This had some relevance when space was at a premium, and likely to be the most significant single factor to affect performance. That didn't make it correct, though. Nowadays, if you are using very large data sets, it is positively dangerous as it prohibits partitioning with locally managed indexes - which may be the most critical implementation choice for performance of high volume, or high throughput systems.

However, I'm always willing to compromise - and whilst I strongly avoid synthetic keys, I have been known to suggest that am partially synthetic key, to connect child rows back to their parent can be effect on both space and performance.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Ram wrote in message ...


>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