Re: [GUSDEV] PK constraints for version tables

[Chris S. <csa...@pc...>]

I can't speak for GUS needs in this particular case, but I've had the  
same issue in other systems. Deborah is correct in that these fields  
are not right for PK. The question is how important it is to preserve  
exact order of the changes to the data. If it's not critical you  
could just drop the PK. If it is (or even if it's not but you believe  
in the rule "every table must have a PK"), then you should use a  
surrogate key from a sequence. In practice, at least, the sequence  
values increase monotonically so changes to a record that occur  
multiple times with the same modification date can still be ordered  
correctly.
I prefer the latter, in general, but I suppose adding a new surrogate  
key field to all version tables in GUS would be a pretty big deal.

Just my thoughts.
-Chris Sarnowski

On Oct 21, 2005, at 11:07 AM, Deborah Pinney wrote:
> I suggest that we drop the uniqueness constraint for PKs of version  
> tables. PKs for these tables consist of the original PK and the  
> modification date. Rapid computation can result in uniqueness  
> copnstraint violations that are not remedied by date format  
> changes. There doesn't seem to be a purpose in having these  
> constraints for any of the version tables and dropping the  
> constraints would not result in the loss of information or change  
> query results. I don't know if there is implicit indexing on these  
> keys but I wonder if this could be handled using explicit indexing.