Yes, Dave, this is a VERY important issue.
When you create a relation with Referential Integrity, Access stops you from
entering a foreign key value that has no match in the main table's primary
key. It does NOT force you to enter a foreign key value! This is by design,
consistent with database theory, and sometimes very useful (see below).
However, it is a major trap that so *many* people fall into.
To prevent this, you must explicitly set the Required property of your
foreign key field to Yes, so a null is not accepted.
This is one of 6 issues addressed in article:
Common errors with Null
at:
http://members.iinet.net.au/~allenbrowne/casu-12.html
In practice, the records with foreign keys make it into your table when a
user has the main form (invoice) at a new record, and enters a record in the
subform (invoice detail). Setting the Required property of the foreign key
prevents that, but the user does not get the error message until they have
finished entering the subform record and Access then refused to save it. You
might like to use the BeforeInsert event of the form to give them the
message at the beginning:
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
End If
End Sub
As an example of where you might actually want a null foreign key, are there
ever times when you need to write out an invoice, but don't know who the
customer is (sometimes called a cash invoice)? If so, you would want to
allow a record in the Invoice table where the CustomerID is null. In most
cases (like your invoice/invoice details example), a null foreign key is bad
data, and so you need to explicitly block this.
In Jet 4 (Access 2000 and later), there's a little known feature that allows
you to cascade foreign keys to null. As an example, if you have products in
categories, you could set it up so that if you delete a category, any
products in that category are not deleted but have their CategoryID field
set to Null (i.e. they are now uncategorized). Unfortunately, the feature
does not show up in the interface and cannot be programmed with DAO, so you
have to use ADOX code to get this to work.
HTH
--
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.
"Dave" <dm******@island.net> wrote in message
news:_zQod.318869$%k.422@pd7tw2no...
I have always taken it for granted that once RI is in place, no orphan
records can be created, and that RI can't be put in place while orphans
exist, but today I came across a situation where that is not true. I am
having a lot of trouble believing my eyes, so I would be very grateful for
anyone's feedback on similar issues.
The situation is the usual Invoice/Invoice Details one: the db has RI in
place on the relatioinship between Invoices and InvoiceDetails so that
there
should be no InvoiceDetail records without matching Invoice records;
cascading updates and deletes are enabled. Nevertheless, I have found 28
(out of 14459) detail records that refer to non-existent invoices. I had
always thought this was completely impossible, but there it is. I can
clean
these up manually, of course, but I am now concerned about how the
situation
might have arisen, and what I can do to prevent it in the future.
Does anyone have any ideas?
Many thanks.
