Choosing a Table's Primary Key
It is recommended that a single column populated by sequence be used as primary key of a table. This satisfies most of the requirements for an ideal primary key. (Refer http://technet.oracle.com/doc/oracle8i_816/appdev.816/a76939/adg05itg.htm#1429). But there are some problems with such 'artificial' columns:
1. They don't have any functional significance. e.g. a number 10 to identify a department is much less meaningful than the word 'Marketing'. This problem is especially significant with respect to foreign keys. We have to join the two tables (Employee and Department) to get any meaningful result from the information stored in the child table. Why not instead choose something like an acronym of the actual department name - like 'R&D', 'M', 'HR'? This will be immediately meaningful in the child table itself.
2. Because Oracle sequences can not be directly associated with a table column, they usually give rise to inconsistencies across different databases. e.g. in a situation where departments are created in the Department table in different orders in different databases, department number 10 may be 'Marketing' in one database and 'Procurement' in the other unless we take special efforts to maintain this static data. (Maintenance of static data is a different discussion topic in itself. By the way, the 'upsert' facility in 9i is a welcome feature in this regard!) Coming back, such differences are annoying during import/export between databases. They tend to give rise to incorrect mappings across foreign keys that are not (necessarily) reported as errors. Also, the sequences may get 'out-of-sync' with the corresponding column and may produce duplicate values for further inserts not accepted by the table.
3. We will not have the problem of skipped values in the PK column if we do not use a PK based on a sequence. Of course, a code-generated value solves this problem, but the code to generate the value should advisably be in a trigger to avoid concurrency issues. Also, for a large table, code-generated values are inefficient (because they use sort - max()). On the other hand, using a trigger to generate an acronym from a long column value would be more efficient. This acronym can then be used as the PK (or part of the PK).
4. I can not think of any downfall of having a composite PK provided the constituent values are short.
Could able contributors express their opinions on this?
Thanks and Regards.