Re: What do you do with an ENORMOUS primary key?

In article <aeuj3n$ms5$1_at_lust.ihug.co.nz>, you said (and I quote):

> Absolutely no quarrels with that bit. It's the fact that you can't guarantee
> "true" uniqueness that bothers me. Stick a primary key on medicare number,
> and you know two people can't be inserted into the table sharing the same
> medicare number. Generate a sequence, and they certainly can.

Sure.

>
> Unless I make the sequence the primary key, and slap a unique constraint on
> medicare_number? At which point, I've got two indexes, not one.

Exactly. Two unique indexes on single columns each. As opposed to a unique index for the PK with as many columns as there are levels in a hierarchy. And you'll *still* need more indexes anyways!

>
> I've done it both ways in the past. The pain of doing it using full keys is
> outweighed (in my experience, and in general) by having to ensure true
> uniqueness by other means.
>

Not if you're talking multiple levels. With two or three, you're quite right. The schema I was working in last year had 9 levels. Would have been up the proverbial without a paddle if I didn't use surrogates. Have seen one with > 25. It uses surrogates and packages exclusively, thank God! I've got to interface to it next year...

Natural keys are sustainable at less than a 3-level hierarchy. Anything over that and surrogates come into their own as a much easier solution. Sure, you need two indexes. But they're nice and easy ones. As opposed to concats that you'll need to add to anyway for more complex queries.

And even in a two level hierarchy I still prefer to have a surrogate key. I can then let users change a mistyped key rather than having to cascade an update. I know, "if it needs to be changed then it isn't a primary key". Tell that to the counter girls that mistyped the medicare number...

It's all, once again and at the risk of sounding like a broken record, a question of context.

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam