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 by one or more methods. Thus a given event may have more than one record for the same spaecies, as parts of the catch may be weighed differently.

When we sample a species for measuring, the event is recorded, but as the sample may include individuals from throughout the 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 referential on 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