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
|
|||
|
|||
Null field in junction table
Is it possible to have a null primary key field when establishing a
many-to-many relationship? I am designing a directory and currently have: Adults table - includes info such as cell phone, email address Children table - includes name and birthday Family (junction) table - AdultID and ChildrenID as primary keys; includes address and family photo The problem is, I believe this design doesn't accommodate adults who don't have children. Must a many-to-many relationship always have primary key field populated in the junction table? If so, is there any way around this problem? Thanks in advance for your help. |
#2
|
|||
|
|||
Null field in junction table
A primary key field cannot be null, but a foreign key can.
In the Family table, you could just use an AutoNumber as primary key. This would allow you to have an entry in the Family table where the ChildID is null, or the AdultID is null (or both.) I'm not sure I understand the purpose of the question, though. If an adult has no children, they would have their own entry in your Adult table, but they would not have any entries in the Family table. Is that a problem? Perhaps you tried to create a query that included both the Adult and Family tables, and found that adults without any family did not show in that query? If so, you need to create an outer join. For details, see: The Query Lost My Records! (Nulls) at: http://allenbrowne.com/casu-02.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ann T" Ann wrote in message ... Is it possible to have a null primary key field when establishing a many-to-many relationship? I am designing a directory and currently have: Adults table - includes info such as cell phone, email address Children table - includes name and birthday Family (junction) table - AdultID and ChildrenID as primary keys; includes address and family photo The problem is, I believe this design doesn't accommodate adults who don't have children. Must a many-to-many relationship always have primary key field populated in the junction table? If so, is there any way around this problem? Thanks in advance for your help. |
#3
|
|||
|
|||
Null field in junction table
Ann,
Your junction table would have a record (only) for each instance of a parent/child relationship. So, when an adult doesn't have any children, there is no record corresponding to that adult in the junction table, and then your question is moot. Hope this helps. Sincerely, Fred "Ann T" wrote: Is it possible to have a null primary key field when establishing a many-to-many relationship? I am designing a directory and currently have: Adults table - includes info such as cell phone, email address Children table - includes name and birthday Family (junction) table - AdultID and ChildrenID as primary keys; includes address and family photo The problem is, I believe this design doesn't accommodate adults who don't have children. Must a many-to-many relationship always have primary key field populated in the junction table? If so, is there any way around this problem? Thanks in advance for your help. |
#4
|
|||
|
|||
Null field in junction table
Allen's reply wasn't up when I answered.
"Ann T" wrote: Is it possible to have a null primary key field when establishing a many-to-many relationship? I am designing a directory and currently have: Adults table - includes info such as cell phone, email address Children table - includes name and birthday Family (junction) table - AdultID and ChildrenID as primary keys; includes address and family photo The problem is, I believe this design doesn't accommodate adults who don't have children. Must a many-to-many relationship always have primary key field populated in the junction table? If so, is there any way around this problem? Thanks in advance for your help. |
Thread Tools | |
Display Modes | |
|
|