| Subject | Re: [ib-support] Fw: Problem creating foreign key |
|---|---|
| Author | Helen Borrie |
| Post date | 2002-01-17T12:34:11Z |
At 01:48 PM 17-01-02 +0200, you wrote:
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
_______________________________________________________
>Thanks for the reply HelenA 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.
>
>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??
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
_______________________________________________________