Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What do you do with an ENORMOUS primary key?

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

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Fri, 21 Jun 2002 17:02:45 +1000
Message-ID: <aeuj3n$ms5$1@lust.ihug.co.nz>

"Nuno Souto" <nsouto_at_optushome.com.au> wrote in message news:dd5cc559.0206202146.2791645a_at_posting.google.com...
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:<aete2j$dck$1_at_lust.ihug.co.nz>...
>
> > Mmmm. Thanks. Interesting that both you and Neill see a synthetic
primary
> > key looming over the horizon. I don't think that will work, because then
> > there's no guarantee that you won't schedule a rose bush to be pruned
twice
> > in one day -and a poor looking specimen it would be then!! I've never
liked
> > synthetic keys (like Microsoft's autonumber) for just that reason: it's
> > every appearance of primary-keyness, but doesn't actually guarantee
> > uniqueness for your real data. But that's a philosophical discussion
that
> > can run and run, I think!
>
> Surrogate keys greatly simplify the process of indexing and appropriately
> referencing FKs in a multi-level parent-child design.

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.

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.

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.

Regards
HJR
>It's only after you've
> been through the pain of implementing and managing a 10 level hierarchy
done
> without surrogate keys that you fully appreciate their usefulness.
>
> I'd say Nial and I have had our fair share of that and automatically zap
> back to them whenever we see a potential for more than two levels of PK-FK
RI.
>
> At a higher level of complexity of RI, you'll soon realize that using a
> traditional approach means ending up with a bottom entity with a PK made
> up of 9 FKs. Not practical, particularly for index management reasons.
>
> As I'm fond of saying: perfectrly possible, but a RPITA to manage.
>
> There is another thread here about using stored procs instead of triggers
> to manage RI. You'll find as complexity increases stored procs, surrogate
> keys and declarative RI are the only practical way of implementing an
> efficient system.
>
> >
> > But thanks anyway for thinking about it.
> >
>
> No worries. Pleasure.
>
> --
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam
Received on Fri Jun 21 2002 - 02:02:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US