microsoft access relationships: referential integrity different databases linked tables

0
votes

As far as my understanding goes, it's not possible to enforce referential activity (and cascade updates) between tables that come from two different database (even if the tables are all in split back-ends) Is this right?

Is it even necessary to enforce referential activity if your tables are set up correctly (IE they are all normalized)? I ask because if the front end is using (let's say) combo boxes that get the row sources from the department table and the control source stores the foreign key (IE the departmentID) in the employee table, why does it matter if referential integrity is enforced?

IF IT DOES MATTER then I absolutely must find a way to enforce referential integrity between tables that come from different database files.. there must be a way...

LASTLY, would a fix be to keep all back-end tables in one single database file? Basically every database uses the Employee table and the Department table.. but the rest of the tables are generally related to only the other tables that are used in a particular front-end database.

1
backendreferential-integritylinked-tables

1 Answers

1
votes

Referential integrity isn't really in reference to lookup tables, it's about parent/child tables or some kind of vital relationship like that, and they should always be in the same database. And your tables should always be designed to best fit the application, whether you use RI or not.

The whole point is that the related table has a key ID that the other table points to, and if that related record is affected then it won't break the relationship. Lookup tables can be structured to have a unique identifier, so if the original record is lost, and a new one is added, even though the key will have changed, the unique identifier won't because you'll just put that back in.

As a general rule, if a record in one table is dependent on a record in another table, you need referential integrity, and they must be in the same database. That way, the database itself does the enforcing, and the programmer doesn't have to.