Solved: Referential Integrity

[JustJerry]

Ok, I'm back again, and I thought I understood the Referential Integrity when it came to Relationships between tables.

I have a 'details' table I call LINETbl. This table has some fields made up of Foreign keys related to four other tables. For Example: In LINETbl, I have a field called BootID. This field is related to the BootID in the master table of BootTbl. In the relationships window, I set up a one to many relationship for BootID between LINETbl and BootTbl and selected Referential Integrity AND Cascading Updates.

I 'thought' this was to ensure that if for some reason, the information in the Master Table of BootTbl was to ever change, that I could change the information in the BootTbl and the information stored in the related LINETbl would automatically be changed.

However, the Form I am using, which gets it's datasource from a query that includes BootTbl and LINETbl and a couple of other related tables, seems to have a problem. For simplicity, I have one field on my form that is based upon the BootID from the BootTbl. If, I do NOT enter any data in this field, as sometimes I do not need anything there, it tells me "You can not Add or Change a record because a related record is required in BootTbl".

IF I go into the relationships window and deselect the Referential Integrity option, then I do not get this error. I then assume that if part numbers, etc., change in my BootTbl, they obviously will not automatically be updated in the LineTbl as they would with the cascade update option selected. I 'guess' this might be ok as the records will show the parts as ordered at 'that time'.

I am obviously not completely understanding the Referential Integrity thing, and I even have a couple books I have read up on the matter.

I hope I made myself clear enough, cuz I'd sure like a clue as to what I'm missing.

Thank you,

Jerry

[eed]

In the relationships window, I set up a one to many relationship for BootID between LINETbl and BootTbl and selected Referential Integrity AND Cascading Updates.

I 'thought' this was to ensure that if for some reason, the information in the Master Table of BootTbl was to ever change, that I could change the information in the BootTbl and the information stored in the related LINETbl would automatically be changed.


The paragraph quoted above shows a pretty good understanding of Cascade Update.

What you need to remember about Referential Integrity is: for every record in the secondary table (LINETbl), you must have a record in the primary table (BootTbl) where the linked field (BootID) matches.

So, a hypothetical example:
- If you create a BootTbl record with BootID = 3...
...you could (but don't have to) create a LINETbl record with BootID = 3
- If you create a LINETbl record with BootID = 3...
...you MUST ALREADY have created a BootTbl record with BootID = 3

Referential integrity ensures that all records in the secondary table MATCH records in the primary table. A certain value must already exist in the linked field in the primary table BEFORE you can enter it in the linked field in the secondary table.

Then, because the matching records have been clearly indicated and are being consistently monitored by the system, you have the option of turning on Cascade Update. This option says, "Don't just keep track of which records match, but ensure that they continue to match even when I change the record in the primary table." Similarly, Cascade Delete says, "Ensure that the matching record in the secondary table goes away when the matching record in the primary table goes away." These options prevent "floating" records in the second table that don't belong to any primary records.

So, in a nutshell: in order to use referential integrity, the linked field value MUST exist in the first table if it's going to exist in the second table. And, if there is no value in the linked field in the primary table (your post indicated you sometimes do not add a BootID in BootTbl), you cannot enter anything into the other secondary table fields (as you indicated were in your query).

Did that make a little sense, maybe...?

~ eed

[JustJerry]

Yes, that made perfect sense, along with what I was starting to think myself. Guess I just wanted verification to make sure there wasn't some secret I was missing.

Thank you

[xCav8r]

Jerry,

I noticed that you're suffixing your table names with Tbl. Traditionally, Access users employ prefixes in favor of suffixes. The idea is that prefixes make it easier to quickly distinguish between tables, queries, forms, subforms, reports, etc. This is all a matter of preference, of course, but may I ask nonetheless why you've chosen suffixes? Just curious.

[JustJerry]

Well, Ummmm....I really don't have a 'logical' reason as to why. Not having much knowledge of Access, I just started making things up as I went. :)

[geekgirlau]

I tend to use prefixes myself, but the disadvantage is that when in the Database Window you can't use the keyboard to jump to object starting with a letter (for example, you can't type "M" to move to the "Master" table if every table starts with "tbl_").