referential integrity and tables
Thank you so much!
Directionality was the issue.
Now it works! =)
"John W. Vinson" wrote:
On Fri, 13 Nov 2009 00:44:02 -0800, nikka
wrote:
Yes, you understood my main idea correctly.
I guess, I also want the table #1 to be automatically populated with partial
information and especially keys from both tables #2 and #3.
All three primary keys in each table are of type Number and they are also
LongInteger by the field size. They are indexed and no duplicates are allowed.
They are NOT Autonumbers.
So, when I am inputting record in table #2, because of the defined
relationship, "+" sign appears on the right; when I click the sign, I can
populate rows with that partial information that is supposed to be stored in
table #1 under the same primary key. When I try to save, I get error saying:
"You cannot change a record because related record is required in table #3".
When I do the same thing in table #3, I get another error: "Index or primary
key cannot contain Null value".
However, I can input attributes for table #2 and #3 and save them, but not
the partial information which is supposed to be stored in table #1.
Then, if I go into table #1 and try to manually input that partial
information using, for example, primary key from table #3, there is a message
that tells me that I "cannot add or change a record because a related record
is required in table #2"
If you need more information, give me a hint at which one, I will provide
more.
Sorry for confusion it's my first database experience.
Thank you.
Relationships - even one to one relationships - have directionality. In the
more common one-to-many you must fill in a record in the "One" table before
you can create records in the "Many".
The same applies with one to one relationships: you must fill in a record in
the "parent" table before that parent can have a child.
One other concern - it appears that you may be trying to store "partial
information" redundantly in both Table1 and Table2. The ONLY field that should
exist in both tables is the Primary Key of each; the Primary Key of table2
will also be functioning as a foreign key. If you're trying to store *other*
fields in both tables... don't!!!! Redundancy is redundant, and redundancy is
redundantly BAD.
--
John W. Vinson [MVP]
.
|