Null field in junction table

[Ann T]

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 Browne]

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

 

[Fred]

Allen's reply wasn't up when I answered.

 

[Fred]

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

 

[David W. Fenton]

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.