Ideally, if the primary key for this table needs to be passed to another table as a foreign key, your best solution is to add a numeric column to the table to be used as the actual primary key, use an Oracle SEQUENCE to generate unique values for it, and make a UNIQUE key out of the columns that currently make it its primary key.

This eliminates all the issues presented by the column or columns that frequently change. Use the new numeric column as the foreign key in other tables. This also makes those secondary tables look cleaner, especially when they contain foreign keys to multiple tables.

This is called a SURROGATE PRIMARY KEY, and is a typical and very standard way of designing relational databases. The technique was arrived at to resolve just the issue you are dealing with.

If you need the values in your current primary key when SELECTing from the secondary table(s), you can join to this table via the new primary key column. And accessing a table via its primary key is an extremely fast way to access it, second only to using the ROWID.

Good luck,

Jolora

In the long run, a surrogate PK will give you less trouble. It can be implemented via a sequence or an explicit key your code maintains.

Oracle e-Business Suite itself has surrogate keys all over, it gives the suite much more flexibility.

Jolora has hit it nail on the head. the surrogate primary key using sequence-generated numbers is the best way.

Ideally, once you assign a PK value, whether single-column or composite PK, the values should NEVER change.

What you should probably do is to just create a unique index incorporating the status code if you need the status code as part of the ‘key’ for performance reasons, and leave a column like STATUS_CODE out of any primary key.

Regards:
Ferenc

Just as an aside, it may be worth re-checking the initial analysis. Sometimes (often?) this situation arises when the primary key has been improperly defined. The principle is that the primary key defines exactly the thing-of-interest. Any other data you hold in the table are attributes of the thing-of-interest (TOI). A changing primary key may indicate that the analysis has not properly identified the TOI or that the primary key contains more fields than necessary.

HTH

Thank you all for the answers and expertise. They are very valuable.

Here is a bit background: This J2EE project is partially alive. We just started to do modification and adding modules. I checked the ER-Diagram and the database. There are numbers of issues: frequently changed status_id(two status: Live/Pending) as primary keys in parent table; data redundancies(5 children tables also keep a copy of Live and Pending records as parents do)

This J2EE application is working in prod. It may only have a few thousand records in 5 of the major tables in the future. It sounds there is no impact for now. I do not know, in this situation, if it is necessary to make the major database re-design (use surrogate primary keys instead of frequent changed primary keys, create indexes for search columns, create conjunction tables to reduce data redundancies,etc). I do not know how much impact will be in the FUTURE (such as Oracle 9i+) if we keep the original database structure.

Thank you very much,

Paula Chen

One other thing you may want to consider. Everytime the primary key changes, Oracle will place a pointer where the table should be according to its primary key. That pointer will point to where the table actually exists. If you change enough primary keys you will create a situation where every read will take you twice as long and twice as many IOs as it should. Whenever a primary key could change for any reason, a surogate key should be used.