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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|