Referential Integrity with a Linked Lookup Table

[LukeMatt]

Although I feel very comfortable with Access, I still cannot figure out one of its most fundamental aspects.

I have Database A and Database B. They both need to use a lookup table that contains valid locations. I have put this lookup table in Database C.

In Database A and Database B, I have linked to the lookup table.

If I edit an existing value in the lookup table, I want the change to cascade to records in Database A and Database B that contain the pre-edit value.

HOW DO I MAINTAIN REFERENTIAL INTEGRITY????

When I try to define a relationship in Database A or Database B to the lookup table, Access grays out the "Enforce Referential Integrity" checkbox. According to my research on the Internet, Access does not allow referential integrity across a link.

Of course, I can maintain referential integrity by putting a copy of the lookup table in Database A and a copy of the lookup table in Database B (then define a relationship with enforce referential integrity), but if I edit a value in either copy, the copies become out of sync.

Thus, I'm stuck. My kludgy solution...in Database C, I have defined two Form event handlers: "On Current" and "After Update". In "On Current", I save the pre-edit value in a public variable. In "After Update", I run an update query on Database A and Database B that changes every occurrence of the pre-edit value to the post-edit value.

Would some knowledgeable Access expert please tell me the correct way to handle this situation? Thanks.

 

[The_Doc_Man]

If I edit an existing value in the lookup table, I want the change to cascade to records in Database A and Database B that contain the pre-edit value.

Your structure is your enemy here, but something about the way this is worded strikes me as odd. Let me tell you what I think is going on and explain why it doesn't work that way. But if this explanation is not correct because I have misinterpreted your words, chalk up one for "missed communication."

If I have tables A, B, and C in a single database and C is a source of data lookups for a translation of some field in each of A and B, I would never update A or B with the translation. Instead, I would create JOIN queries so that I could do the lookup dynamically when I opened A or B. The translation contained in C would NEVER exist in either A or B. If that is so, then when I update C and next open A or B queries JOINed with C, the updated translation is there instantly.

It sounds to me that you have essentially loaded the translation statically into fields in A and B treated as tables (not as queries). If so, then it is no wonder that the update doesn't occur. This isn't a relational integrity issue. It is an improper relation issue. Once the fields in A or B are updated, they have no link to C at all. There is nothing dynamic about loading fields statically.

 

[gemma-the-husky]

Are you saying you have a single column lookup table with text values, and you want to change a text value?

It's possibly a big design change now, but the real answer is to have a two column lookup table with a (numeric) id column, and a text column. You store the id column in the related table. Then the text value can be changed without any updating.

I would add the new value into your table,

Then do an update statement

update table set somefield = "newvalue" where somefield = "oldvalue"

now you should be able to delete the (now) unused original value.

 

[stopher]

If I have tables A, B, and C in a single database and C is a source of data lookups for a translation of some field in each of A and B, I would never update A or B with the translation. Instead, I would create JOIN queries so that I could do the lookup dynamically when I opened A or B. The translation contained in C would NEVER exist in either A or B. If that is so, then when I update C and next open A or B queries JOINed with C, the updated translation is there instantly.

I might be wrong but the way I read it is the OP wants to implement referential integrity i.e. mandatory participation and the likes.

 

[jdraw]

LukeMatt,

Please tell us more about your intended application. An example with real data would help readers better understand your requirement/question.

It sounds like you frequently have to change a lookup table/reference table value.

 

[stopher]

HOW DO I MAINTAIN REFERENTIAL INTEGRITY????

I would think you could do something like this:

ALTER TABLE b
ADD CONSTRAINT is_in_c
  CHECK (EXISTS (SELECT *
                 FROM  a
                 WHERE b.somevalue = a.somevalue));

So the above check that a value exists in the lookup. For cascades and deletes I think you might be looking at triggers.

 

[LukeMatt]

Thank you for your many responses.

An archaeology researcher has a database that contains a table with one record for each X artifact that she has found. That's Database A. She has another database that contains a table with one record for each Y artifact that she has found. That's Database B.

Database A and Database B **MUST** be kept separate (so let's don't go down the path of combining Database A and Database B into one database ).

One field in Database A and Database B specifies the historic period of the artifact.

Thus, Database C contains a table that lists all valid historic periods. This table is used as a lookup in the "historic period" field of Database A and Database B.

Later, the researcher notices that one historic period in the Database C table is misspelled. She edits the value. This change needs to cascade to the "historic period" field of Database A and Database B in all records where the misspelled value was entered.

I don't know how to automate referential integrity in this case because Access does not allow referential integrity across a link. Database A is linked to the lookup table in Database C, and Database B is linked to the lookup table in Database C. When I try to define a relationship in Database A or Database B to the lookup table in Database C, Access grays out the "Enforce Referential Integrity" checkbox.

My kludgy solution...in Database C, I have defined two Form event handlers: "On Current" and "After Update". In "On Current", I save the pre-edit value in a public variable. In "After Update", I run an update query on Database A and Database B that changes every occurrence of the pre-edit value to the post-edit value.

 

[jdraw]

??? I would think that the historic periods identified in Database C would be in a table with a structure along this line.

[I][COLOR="Blue"][B]tblRefHistoricPeriods[/B][/COLOR][/I]
[U]RefHistPeriodID[/U].................PK
RefHistPeriodStart
RefHistPeriodEnd
RefHistPeriodName
RefHistPeriodDesc
RefHistPeriodOtherFacts

Any relationship linkage between tables would be based on the PK. The whole point being you only have to spell the name in 1 place and you link/relate to it via the PK.

I do not understand the reason to change the spelling in Database A and/or Database B.
Perhaps I'm not following your set up.

For Access to manage referential integrity, the tables would have to be in the same physical database, at least as I understand it.

 

[The_Doc_Man]

I think JDraw is reading this the same way I am reading it.

Question: Do you have ANY control at all over the A and B databases?

Because if so, then it is possible for you to do a database open and COPY your table from C to each of A and B. You could trigger this when you open some particular form or if you have a startup form for each of A and B. THEN, you would not open tables in A or B, you would open QUERIES that joined the relevant tables to the updated COPY of C that appears in both.

The catch is that if you do not have a continuous exploitable connection between A and C or B and C then you CANNOT have relational integrity. The reason is that RI is enforced when you make an update in A or in B and at that point want to test what is in C. But if this isn't continuously open, you just flat can't do that. It HAS to occur at table update time or not at all. From your description, it is not a continuous connection, or at least that is how I'm reading it.

Further, if the change is made to the table in C and C is providing translations of many artifacts to one validation table, the relational arrows are pointing the wrong way. Even if A, B, and C were in the same database, this update wouldn't occur as long as A and B have copies of whatever is stored in C. When you make the copies, you in essence have already violated normalization rules and therefore have invalidated RI's ability to protect you.

 

[gemma-the-husky]

Thank you for your many responses.

An archaeology researcher has a database that contains a table with one record for each X artifact that she has found. That's Database A. She has another database that contains a table with one record for each Y artifact that she has found. That's Database B.

Database A and Database B **MUST** be kept separate (so let's don't go down the path of combining Database A and Database B into one database ).

One field in Database A and Database B specifies the historic period of the artifact.

Thus, Database C contains a table that lists all valid historic periods. This table is used as a lookup in the "historic period" field of Database A and Database B.

Later, the researcher notices that one historic period in the Database C table is misspelled. She edits the value. This change needs to cascade to the "historic period" field of Database A and Database B in all records where the misspelled value was entered.

I don't know how to automate referential integrity in this case because Access does not allow referential integrity across a link. Database A is linked to the lookup table in Database C, and Database B is linked to the lookup table in Database C. When I try to define a relationship in Database A or Database B to the lookup table in Database C, Access grays out the "Enforce Referential Integrity" checkbox.

My kludgy solution...in Database C, I have defined two Form event handlers: "On Current" and "After Update". In "On Current", I save the pre-edit value in a public variable. In "After Update", I run an update query on Database A and Database B that changes every occurrence of the pre-edit value to the post-edit value.

I don't think this is a referential integrity issue directly. I think you just need an update statement to fix the bad values.

As I said before a better way would be to change the data to use a proper indexed lookup table, but that is more complicated, as no doubt it will affect a lot of queries etc

 

[LukeMatt]

Once again, thank you everyone for your replies. I now understand referential integrity in Access...but I don't necessarily like it.

1. For most of my linked tables, I followed gemma-the-husky's advice:

the real answer is to have a two column lookup table with a (numeric) id column, and a text column. You store the id column in the related table. Then the text value can be changed without any updating.

It's logical, but I didn't come up with the idea myself because I thought, "Why should I add an entire field of meaningless data to many tables?"

2. For linked tables where multiple fields are involved, I stuck with my kludgy solution...when a "master data" value changes, I use an UPDATE to change tables that lookup that master data.

3. I guess my biggest complaint, as mentioned by jdraw:

For Access to manage referential integrity, the tables would have to be in the same physical database

jdraw seems to be right. If I pack every table into one physical database, Access will handle all referential integrity. However...

• I can't imagine a huge corporation packing gigabytes of data into one physical database just to get automatic referential integrity.
  
  The fundamental problem seems to be Access' refusal to maintain referential integrity across links. I researched on the Internet, and the only explanation I could find for this refusal is...security concerns.
  
  With more thought, I think the real reason is...to maintain referential integrity automatically, all tables involved must be open, which only occurs with certainty when the tables are in the same physical database.
  
  Musing to myself: "I wonder how Oracle handles referential integrity"
  _
• If I stick all tables into one physical database, what happens when I have two separate physical databases that both need a common table of master data? Back to the same problem of maintaining referential integrity across links.

 

[The_Doc_Man]

It's logical, but I didn't come up with the idea myself because I thought, "Why should I add an entire field of meaningless data to many tables?"

To be fair, if the translation is short, you don't. (By short, I mean "a string of four bytes or less.") When you store the code in A & B and have the link to C, you store a short code in A and/or B and use a query to pull in the translation for you. WHETHER OR NOT you have actual referential integrity between A & C or B & C, you can still do the JOIN. And to be more precise, you are NOT adding a field of meaningless data. You are SUBSTITUTING a longer field with a shorter one. For tables A & B, if they are short, it might not be worth the exercise, but the bigger they get, the more value you get by doing it this way. Remember, it isn't about RI - it is about the ability to do a JOIN so that the QUERY provides the translated field from a common source.

I can't imagine a huge corporation packing gigabytes of data into one physical database just to get automatic referential integrity.

But whether you can or can't imagine it, that is EXACTLY what is done but not necessarily using Access. The "big boys" typically use ORACLE and the ORACLE TOOLS or ORACLE WEB APPS packages for their big databases.

With more thought, I think the real reason is...to maintain referential integrity automatically, all tables involved must be open, which only occurs with certainty when the tables are in the same physical database.

Technically, no in the strict sense of "open table." To maintain RI, the table potentially containing a foreign key must be able to see the index of the table for which that value is potentially the prime key. But no recordset has to be open at the time. If you directly open the child table to create a new record (not using an explicit query), RI still works because the DB Engine takes care of the (implied) lookup.

Musing to myself: "I wonder how Oracle handles referential integrity"

An ORACLE schema involves a list of tables and the files that contain them, because ORACLE inherently allows a multi-container file set. The schema also defines constraints by table name, including RI constraints.

If I stick all tables into one physical database, what happens when I have two separate physical databases that both need a common table of master data? Back to the same problem of maintaining referential integrity across links.

But if you have two separate physical databases then all your tables aren't in the one physical database, are they? By the way, if you did this exact same problem to ORACLE, you would have the same exact difficulty. The only difference between ORACLE and Access in this case is that ORACLE, expecting bigger amounts of data and expecting the need to distribute file accesses across multiple disk spindles for maximum seek efficiency, allows multiple files in a single schema. But then again, using linked files, so does Access.

Seriously, I don't know of ONE current or recent database product that would enforce RI across multiple separate schemas. And I've been working with little and big databases since 1988. This includes having worked at various levels with old DEC Datatrieve, STI OMNIBASE, ShareBase, Informix, Paradox, ORACLE, and Access.

What you are learning is that what you want isn't available so you are going to have to find a way to manage that with some manual programming. Welcome to our world. If that seems harsh, please understand that your post #11 tells me you are listening to but not hearing what we are trying to tell you.

Sometimes we are able to tell people how to solve a problem. Sometimes our answer is "that is the way it works, you will have to devise a workaround solution if that isn't good enough." Well, RI works the way it works.

Now, I'll answer the implied question of why RI is only intra-database. It has nothing to do with what is open. There is a hidden system table called MSysRelationships that links tables and fields to each other. This table exists, one per database file for Access, as a tool for the DB Engine. It does not have a slot for a table being in file #1 or file #2. It is a per-file structure, so RI can only be described intra-file. RI cannot work across files because the pointer that is followed during the RI enforcement check cannot point outside of the file in which it occurs.

NOTE, however, that a JOIN query does not have this restriction. You can still do the JOIN and develop a recordset. It is just that if you do an INNER JOIN across files and you violated RI, some records won't show up. I've done it myself. Works just fine as long as you have reconciliation transactions now and then to identify non-conforming (unmatched) records.

 

[gemma-the-husky]

If I edit an existing value in the lookup table, I want the change to cascade to records in Database A and Database B that contain the pre-edit value.

irrespective of the discussion about the "discretemess" of individual databases, the real problem is because your database schema references significant information that might change.

That's the big issue for me. I never ever design databases where I would need to cascade a change. If I have information in a lookup table that might change - then I would add another surrogate key to that table, and connect my tables based no the surrogate key. That way I can change the meaningful data without it impacting on anything.

So say you have a status table with statuses

Unconfirmed
Oustanding
Complete
In Query

and you are storing these text values in your table.
Now you decide to change "Unconfirmed" to "TBA", and therefore you need to cascade the update to change every instance of "Unconfirmed" to "TBA"

If instead your table has two columns, such as

ID Description
1 Unconfirmed
2 Oustanding
3 Complete
4 In Query

And you just store the numeric value - now you can change the text description for the value without any other changes being required.