Thanks, Advice taken.

This is the first time trying to use foreign keys, always just handled it
through the front-end. I think I'll keep on doing that for now...

Chow
Graham

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <ib-support@yahoogroups.com>
Sent: Thursday, January 17, 2002 2:34 PM
Subject: Re: [ib-support] Fw: Problem creating foreign key


> At 01:48 PM 17-01-02 +0200, you wrote:
> >Thanks for the reply Helen
> >
> >I made the description the primary key and it worked.
> >The problem is that I actually have more fields in the SkyStatus table.
> >Therefore the primary key consists of three fields, Description being one
of
> >them. When I try and create the child table it still gives me the same
> >error.
> >
> >Is there a work around??
>
> A workaround? No. But you really need to have a surrogate primary key on
a table that is being referenced by foreign keys if a row needs more than
one small column to establish its uniqueness; otherwise you will be stuck
with having to have all the columns of the primary key in all the
referencing tables.
>
> Just a word of advice about making foreign key references for lookups on
very small ("control") tables like your SkyStatus (even with the complex key
problem resolved) - don't. An automatic index will be created on the
foreign key column(s) which will have a few identical values (or complexes)
duplicated many times. This gives rise to an index phenomenon known as "low
selectivity". The index (a binary tree) will form long chains of
duplicates, causing index traverses to be slooooooow.
>
> If you want cascade trigger behaviour, code it by hand for this type of
relationship. You'll still need to have this column indexed in the
referencing table, but make it a composite one - use the corresponding
surrogate key of the control table plus the surrogate primary key of this
table (in that order) to avoid the long duplicate chains and give you an
index that works well.
>
> But really-really-really don't use user data (or ANY kind of volatile
data) for keys or your tables will cascade performance to hell and back
(never mind the inevitable data corruption when human intervention clogs up
the arteries of data integrity).
>
> Another word about using non-atomic data (like your Description) as keys -
don't. Make keys completely atomic (independent of any meaning as user
data) and make them as small as they can be to accommodate the limits of
possible record counts. Use Int64 (Dialect 3) or integer and use generators
to populate...never let humans touch them.
>
> regards,
> Helen
>
> All for Open and Open for All
> Firebird Open SQL Database · http://firebirdsql.org
> _______________________________________________________
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>