Ensuring Primary Key integrity

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello - I will try to make this complicated problem sound as simple as I can...I have a database that has so much information about the same objects that all of the fields would not fit on one table. So, there are four tables, each linked by the Primary Key number , describing the same object (in this case, the objects are hot springs, and the tables are Isotopes, Trace Elements, Gas, etc.).
It was easy enough to get all of the data that already exists into the table and make sure the appropriate Primary Key number went to the correct hot spring for each table. The problem now is that more data entry is needed, and I cannot be sure that the new number to be entered as the Primary Key for one table will be for the same spring on another table. I need some way to link all four tables on a form that, as a number is entered in one form, the remaining tables are updated so that the same number cannot be used for two or three different springs. I have absolutely no idea how to do this. Does anyone have suggestions? Many thanks in advance!
 
So as I understand your letting your users pick the primary key?

For linking tables togetter you should be using the autonumber to link the
master table to it's child tables

So:
MASTER CHILD CHILD
ID auto- --| ID auto ID auto
--> m_id int m_id int
Number int Number int Number int

Now when someone enters a new child record, all you have to do is make sure
that field m_id gets the value of the autonumber id
Note that the Id's for the child record are often used, but it is good
practice to give them their own ID
Note again that these autonumber ID and m_id fields have no meaning to you
or your users , and thus should not be visible to your users... It's merely
a failsafe function to make sure each record has a unique ID and is pointing
to the right master record..

When you can't fit all information even on 4 tables , this is often a case
of improper table design...

Pam Lewiston said:
Hello - I will try to make this complicated problem sound as simple as I
can...I have a database that has so much information about the same objects
that all of the fields would not fit on one table. So, there are four
tables, each linked by the Primary Key number , describing the same object
(in this case, the objects are hot springs, and the tables are Isotopes,
Trace Elements, Gas, etc.).
It was easy enough to get all of the data that already exists into the
table and make sure the appropriate Primary Key number went to the correct
hot spring for each table. The problem now is that more data entry is
needed, and I cannot be sure that the new number to be entered as the
Primary Key for one table will be for the same spring on another table. I
need some way to link all four tables on a form that, as a number is entered
in one form, the remaining tables are updated so that the same number cannot
be used for two or three different springs. I have absolutely no idea how
to do this. Does anyone have suggestions? Many thanks in advance!
 
Back
Top