foreign key problem
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,
Em Monday 15 September 2008 19:05:25 pcreso@pcreso.com escreveu:
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.
Looks like you need a composite primary key here, i.e. a primary key for the
"category" of the record will have more than one column (such as species and
method of catch).
With that you'll be able to uniquely identify the "event" and then associate
it with the record.
--
Jorge Godoy <jgodoy@gmail.com>
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
Jorge Godoy <jgodoy@gmail.com> 09/17/08 1:36 AM >>>
Em Monday 15 September 2008 19:05:25 pcreso@pcreso.com escreveu:
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.
Looks like you need a composite primary key here, i.e. a primary key for the
"category" of the record will have more than one column (such as species and
method of catch).
With that you'll be able to uniquely identify the "event" and then associate
it with the record.
Thanks Jorge,
There are many catches per event, one for each species/method, so a composite key would be on event/species/method for the catch.
For lengths it would be on event/species (there is no catch weigh method here). There should only be a length record for a matching event/species in the catch (normally constrained via a foreign key) but I cannot create a composite key on catch without including method, so there is no possible unique key on catch to match to the primary key on length.
--
Jorge Godoy <jgodoy@gmail.com>
Import Notes
Resolved by subject fallback