Re: Sequence Numbers as Primary Keys

drolfe_at_eng (David Rolfe) wrote:

snip, stuff covered in an earlier post

>I say this because I have seen several cases where the *only* unique key of a table is
>a sequence number. The designer didn't bother to figure out what the 'real' unique key was
>and what the problems were with it before they stuck a column called SEQNO at the front
>of the table. Until you have a candidate unique key you don't *really* know what the table
>(or entity) is.

Yeah, I'm with you on this one. Sequence numbers are very useful, but sometimes get used as an excuse for bad analysis. i.e. "Course the table has a primary key, it's that sequence". I think it's useful to perhaps talk in terms of primary keys and unique keys. Real world primary keys are not always unique (although very close). A system generated key can guarantee uniqueness of a real world primary key. (and work much faster than a complex 'real world' key)

>> Another example (which we come across with about 30% of our tables) is
>> start date - end date primary keys. The primary key may be (for
>> example) person's name, classification, and a period (start date - end
>> date). The end date is often null and thus can't be used as a primary
>> key. as it contravenes Codd the God's rule about PK's
 

>I get round this by having a system wide constant called C_MAX_END_DATE or
>something like that. It's set to 01-jan-4000. End dates are always not null and the
>constant is used when the end date is open-ended. This works very well in practice as
>Oracle allows you pick a date which is so far in the future that *nobody* could interpret
>it as anything other than meaning that the relationship in question is open ended. It also
>means that you can use indexes properly, which you can't if you have to use NVL on your end
>date column to make sure it comes back.

We tried something like this, but rejected it as more complex than we wanted. All the forms have to decode the max_end_date to null so that it doesn't upset the users and lots of nasty non base table stuff starts rearing it's ugly head. Also on the index point, having too many identical entries upsets the cardinality of the index (typically about 50% of some end dates can be null, so being able to do an indexed search isn't as much of an advantage as usual. Of course, these issues relate just to our data (and our users).

The word 'surrogate' for the use of system generated keys is very useful. System generated keys are used IN PLACE of the real primary key (even if it's an imperfect one), not as the 'real world' primary key.

>While we're on the subject I wish oracle would introduce a DATE RANGE data type. Having
>a start/end date pair in your key is all very well but you have to write additional code
>to watch for overlaps

YES, YES, YES, YES, YES!
If this was implemented, LOADS of code would be able to magically vanish from our applications and enforcing RI would become a much simplified process .

<snip>  

>> Interesting discussion, nice to see somebody supporting the "no
>> sequence nubmers" point of view.
>>
 

>I don't support the "no sequence numbers" point of view or the "only sequence numbers"
>point of view. I'm trying to make the point that both extremes cause problems and that
>the proper course is a some of both. I'm also concerned that people use system generated
>numeric primary keys as a substitute for good analysis.

Yup, didn't mean to imply that. Just that you have an understanding of the disadvantages of system generated keys as well as the advantages.

>David Rolfe,
>SunSoft,
>California.
Received on Fri Oct 06 1995 - 00:00:00 CET