Thread: Help with a foreign key with non-unique reference?

Hi,

I need a foreign key (or equivalent) where the referenced table cannot have a unique constraint.

For fisheries surveys we have a table of catch records. For a given event, several species are caught, and are weighed
byone or more methods. Thus a given event may have more than one record for the same spaecies, as parts of the catch
maybe weighed differently. 

When we sample a species for measuring, the event is recorded, but as the sample may include individuals from
throughoutthe catch, it does not necessarily relate to the catch-with-method table. 

It is possible to have catch/sub-catch tables where the catch table no method & stores the aggregated weight of each
sub-catch(with method), but I prefer to avoid this if possible. 

How can I impose a constraint on the table of length data, so that the event/species has teh equivalent of a
referentialon catch(event,species) when I cannot have a unique index on catch(event,species)? 


Thanks,

  Brent Wood

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand

On Tue, 16 Sep 2008, Brent Wood wrote:

> I need a foreign key (or equivalent) where the referenced table cannot
> have a unique constraint.

Well, do you need a full foreign key or just the insert-time check on the
referencing table? Does the referenced table get updates or deletes that
you want to watch for, and do you want those to error or to do the
equivalent of one of the referential actions?

For the insert-time check only if you don't care about deletes or updates
to the referenced table, a trigger that checks for existance is probably
good enough.