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.

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.

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.

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.

=?Utf-8?B?QW5uIFQ=?= Ann wrote in
:

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.

If there are no children, then there's no records in the family
table. It's pretty simple, really.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/