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

Referential Integrity Problem

P: n/a
I've inadvertently placed this post in another similar newgroup, and I
apologise if you get it twice.

I'm building a database that consists of frontend and backend. Some
of the lookup tables need to reside in the frontend database. The
data in the lookup fields that relate to the backend tables will
never
change, but a descriptor for items in these fields will. I may need
to update the descriptors periodically, hence the need to keep these
tables in the frontend database so that the new descriptors will be
distributed with frontend updates.

Ideally the lookup fields should be linked to the backend tables with
referential integrity enforced. Because the tables are in 2
different
databases this can't be done.
What options do I have? Given that the lookup fields will always be
under my control and will never change, is enforcing referential
integrity really an issue?

Jun 26 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Since you cannot use Referential Integrity across different files, you will
have to manage it manually.

That means you intercept each insert and edit into the back end table, and
ensure that it matches one of the values in the front end. Similarly, you
must intercept each edit and delete in the front end table to ensure this
leaves no orphan in the back end.

There is still no guarantee that this will work okay. For example, if Fred
inserts a new value (say 93) into his front end, and then assigns that value
as a foreign key value in the back end, when Jenny opens her front end and
has *not* inserted 93 there, you have an orphaned record situation.

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

"Wayne" <cq*******@volcanomail.comwrote in message
news:11**********************@x35g2000prf.googlegr oups.com...
I've inadvertently placed this post in another similar newgroup, and I
apologise if you get it twice.

I'm building a database that consists of frontend and backend. Some
of the lookup tables need to reside in the frontend database. The
data in the lookup fields that relate to the backend tables will
never
change, but a descriptor for items in these fields will. I may need
to update the descriptors periodically, hence the need to keep these
tables in the frontend database so that the new descriptors will be
distributed with frontend updates.

Ideally the lookup fields should be linked to the backend tables with
referential integrity enforced. Because the tables are in 2
different
databases this can't be done.
What options do I have? Given that the lookup fields will always be
under my control and will never change, is enforcing referential
integrity really an issue?
Jun 26 '07 #2

P: n/a
Thanks Allen. You've certainly given me some food for thought!
Jun 26 '07 #3

P: n/a

"Wayne" <cq*******@volcanomail.comwrote in message
news:11**********************@x35g2000prf.googlegr oups.com...
I've inadvertently placed this post in another similar newgroup, and I
apologise if you get it twice.

I'm building a database that consists of frontend and backend. Some
of the lookup tables need to reside in the frontend database. The
data in the lookup fields that relate to the backend tables will
never
change, but a descriptor for items in these fields will. I may need
to update the descriptors periodically, hence the need to keep these
tables in the frontend database so that the new descriptors will be
distributed with frontend updates.

Ideally the lookup fields should be linked to the backend tables with
referential integrity enforced. Because the tables are in 2
different
databases this can't be done.
What options do I have? Given that the lookup fields will always be
under my control and will never change, is enforcing referential
integrity really an issue?
Put the lookup tables in the BE, have the FE update the descriptors when you
update the FE on the user machines.
Jun 26 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.