One-to-many where the many doesn't require the one

[David M C]

I have an employee table and a plant table. Each plant item can be assigned
to an employee, or it can be null. Is there still a one-to-many relationship
between the two tables? The employeeID field is the primary key in the
employee table and the foreign key in the plant table.

With the relationship in place, when creating a new plant record, it has to
have an associated employee record. This is what I'm trying to avoid. Would
creating the relationship, but not enforcing referential integrity be a
good/bad idea?

Thanks

Dave

 

[Roger Carlson]

If you don't enforce referential integrity, you don't HAVE a relationship.
The relationship window can be deceiving in this respect, but it's true. At
best, this tells tables how to autojoin in a query.

Yes, you still have a 1:M relationship. You DO NOT have to put a value in
the many-side table. Techically, a what we normally call a one-to-many can
be written like this:
Each record in the One-Side table is related to Zero or more records in the
Many-Side table.

However, Access does an interesting thing when you create your fields. If
you create a numeric field, it will automatically give it a default value of
0 (zero). This is a problem with a foreign key that links to an autonumber
primary key in another table, because there will *never* be a value of zero
in the primary key. Therefore you get an error if you try to leave that
field blank.

The solution is easy. Go to your table design and remove the 0 from the
Default Value property of your foreign key field.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[David M C]

Thanks, all sorted.

If you don't enforce referential integrity, you don't HAVE a relationship.
The relationship window can be deceiving in this respect, but it's true. At
best, this tells tables how to autojoin in a query.

Yes, you still have a 1:M relationship. You DO NOT have to put a value in
the many-side table. Techically, a what we normally call a one-to-many can
be written like this:
Each record in the One-Side table is related to Zero or more records in the
Many-Side table.

However, Access does an interesting thing when you create your fields. If
you create a numeric field, it will automatically give it a default value of
0 (zero). This is a problem with a foreign key that links to an autonumber
primary key in another table, because there will *never* be a value of zero
in the primary key. Therefore you get an error if you try to leave that
field blank.

The solution is easy. Go to your table design and remove the 0 from the
Default Value property of your foreign key field.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L