referential integrity and tables

N

nikka

I have several tables.
Let's say that Table1 is Inventory - it is a superclass table and contains
all inventory ids as its primary key and some other general info that applies
to each inventory item.
Table 2 is Car that has primary key that is a foreign key from Inventory
table.
Table 3 is Container that has primary key that is a foreign key from
Inventory table.
Table 2 and Table 3 are subclasses, they are disjoint and total in my design.
I linked primary keys in Table 2 and 3 to the primary key in Table 1, and MS
Access placed 1:1 relationship.
I need it to be 0:1 or 1:1 relationship, so that record for Table 1 is
always there for either table 2 or 3.
As of now, when I'm typing information with primary key "1" in Inventory, it
asks for the records with primary key "1" in both tables Table 2 and 3.

How do I make it, so for example, Table 1 has keys 1,2,3 where key "1" and
"2" are primary keys in Table 2, but key "3" refers to Table 3?

Is there anyway to do it just by leaving primary keys in Table 1 be a
foreign key which is at the same time a primary key in Table 2 and 3 without
creating a new attribute in Tables 2 and 3 which will be a separate foreign
key for Table 1???

Thanks
 
J

Jeff Boyce

I'm a little confused by your description, but it sounds like you want to be
able to use the primary key from your table #1 as a primary key for your
tables #2 and #3. In this instance, even though those (table2,3) primary
keys could be considered to "point back" at table1, they are table 2 & 3
primary keys (not foreign keys).

The one-to-one relationship implies the possibility of a one-to-zero (i.e.,
table 1 might record an inventory item that is NOT a Car, and hence, the ID
doesn't show up in table 2).

Is there a chance you are experiencing technical difficulties with the ID
fields in tables 2 & 3? Do you have them defined as Long Int fields, or are
you trying to have Access generate Autonumbers for them?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
N

nikka

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.
 
J

John W. Vinson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top