Prevent duplicate keys across two tables

[Peter]

I have two tables, Genus and Species, in a taxonomic database, joined
in a one to many relationship. When the use enters new data, I need to
prevent duplicates of the two names as a whole, but permit duplicates
in the tables individually. That is, I can allow Homo (Genus) Sapiens
(Species), Homo Asinus, Homo Neanderthalis - duplicates in the Genus
table. I can also allow Homo Sapiens, Felix Sapiens, Canis Sapiens -
duplicates in the Species table. But I cannot allow Homo Sapiens to
occur more than once.

Is there any way to build constraints in indices, relationships or any
other "automatic" machinery? I know how to do it in code attached to a
form, no problem, but I prefer to use Access's built-in functionality
whenever possible. However, I don't know of any way to enforce this
across two tables. Am I missing something obvious?

 

[Rick Brandt]

I have two tables, Genus and Species, in a taxonomic database, joined
in a one to many relationship. When the use enters new data, I need to
prevent duplicates of the two names as a whole, but permit duplicates
in the tables individually. That is, I can allow Homo (Genus) Sapiens
(Species), Homo Asinus, Homo Neanderthalis - duplicates in the Genus
table. I can also allow Homo Sapiens, Felix Sapiens, Canis Sapiens -
duplicates in the Species table. But I cannot allow Homo Sapiens to
occur more than once.

Is there any way to build constraints in indices, relationships or any
other "automatic" machinery? I know how to do it in code attached to a
form, no problem, but I prefer to use Access's built-in functionality
whenever possible. However, I don't know of any way to enforce this
across two tables. Am I missing something obvious?

I would expect the field that joins/links the two tables to be the Genus field
and that the Species table would have a PK consisting of both Genus and Species.
That would automatically prevent diuplicates on the combination.

If you are using surrogate primary keys then you still need to create a unique
index in the Species table that includes both the surrogate foreign key and the
Species field. In addition the Genus field in the Genus table needs to be
uniquely indexed to prevent duplicates there.

Referential integrity should prevent an entry in the foreign key field that is
not found in the Genus table and the two-field unique index will prevent
duplicates on the combination.

 

[Peter]

I use numbers for keys rather than the taxonomic text, but you're
right. I created another index in the Species table composed of the
code from the Genus table and the text of the Species and it does
exactly what I need - prevents duplicates of the Genus-Species
combination, but allows duplicates in the Species table. Many thanks,
as I slink off into the corner, kicking myself for not thinking of it
on my own. Thank God for newsgroups.

Cheers,

Pete


Rick Brandt napsal:

 

[John Vinson]

That is, I can allow Homo (Genus) Sapiens
(Species), Homo Asinus, Homo Neanderthalis - duplicates in the Genus
table. I can also allow Homo Sapiens, Felix Sapiens, Canis Sapiens -
duplicates in the Species table.

<SNORK!!!>

Having known all too many Homo Asinus and even a few Felix Sapiens, I
can only say *right on* to your examples!


John W. Vinson[MVP]