By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,028 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,028 IT Pros & Developers. It's quick & easy.

Referential Integrity failure -- any ideas?

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
In a subform, Access allows a null value for the LinkChild field in a
subform. If your LinkChild field is a Long data type, you should have
noticed that when you create the table the subform is based on, Access
assigns a 0 as the default value for what becomes the LinkChild field. If
you leave the default as 0, you will get an error message if you try to
create a record in the subform with no record in the main form because the
LinkChild field will have a value of 0 and Access does not permit that.
However, if you delete the default value of 0 in the table and then enter a
record in the subform with no record in the main form, Access does not stop
you because Access allows a null value in the LinkChild field. I don't like
to see a 0 in the LinkChild field so I delete the default value of 0. To
prevent the problem you are seeing, I put the following code in the OnEnter
event of the subform control:
If IsNull(Me!NameOfLinkMasterField) Then
MsgBox "Enter Record In main Form First"
Me!NameOfLinkMasterField.SetFocus
End If

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"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.

Nov 13 '05 #3

P: n/a
Thanks to Allen and PC Datasheet for those prompt and eye-opening replies.
I"ve been working with Access for quite a while, but have been, until now,
blissfully ignorant of those facts. Much appreciated!

Dave Macmurchie

"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.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.