A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

referential integrity and tables



 
 
Thread Tools Display Modes
  #1  
Old November 12th, 2009, 11:26 PM posted to microsoft.public.access.tablesdbdesign
nikka
external usenet poster
 
Posts: 3
Default referential integrity and tables

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


  #2  
Old November 13th, 2009, 02:02 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default referential integrity and tables

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.

"nikka" wrote in message
...
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




  #3  
Old November 13th, 2009, 09:44 AM posted to microsoft.public.access.tablesdbdesign
nikka
external usenet poster
 
Posts: 3
Default referential integrity and tables

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.

"Jeff Boyce" wrote:

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.

"nikka" wrote in message
...
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




.

  #4  
Old November 13th, 2009, 06:33 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default referential integrity and tables

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]
  #5  
Old November 13th, 2009, 07:36 PM posted to microsoft.public.access.tablesdbdesign
nikka
external usenet poster
 
Posts: 3
Default 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]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.