Primary key quandary

Our database is fairly small (for Oracle) and the main table has a mere 26 million rows in it.

This table is partitioned by a date but the primary key does not need this date and is generated by the data feed. Now what is the best way to apply a primary key that will allow greatest flexibility? (A bit ambiguous?!!)

The data is static once added to the database and all attributes are set by an external program (ie it creates the data and user update on this external app, once data is delted from the external app it is moved from a buffer space to this database). The data is partitioned by a date as most queries will be on a single partition period and if we do delete data it will be for a partition period at a time (rolling data set idea). Queries will not use the primary key except to link tables.

The primary key does not have this date and so partitioning it is a bit awkward. To add the date means playing with foreign key constraints.

I can't see how this is a unique problem. Surely the same could happen on an order entry system with order headers split by date but order number being the primary key? Received on Tue Jul 30 2002 - 09:41:21 CDT