I have many separate database files that have all the same structure, but hold data from different customers. In each database, I have a series of tables that are used to supply valid values through foreign keys.  These tables and the data they hold are exactly the same in each database.  When I need to update a valid value in one, I have to make that same change in all the other databases.  Is there a way to keep the valid value tables in a separate database and build queries that access these tables from a second database?  The goal is the have a single set of valid value tables, instead of multiple copies.

Unfortunately, Firebird cannot have foreign keys pointing to different databases, and only through EXECUTE STATEMENT is it currently possible to query another database (look at www.firebirdfaq.org/faq16/, note that I do not know whether or not Firebird 3 is any different in this regard).

I'm not certain, but in theory it may be possible to have a trigger that look up the value in a different database using EXECUTE STATEMENT and raises an exception if it doesn't find such a value. Though even if it can be possible, I would not recommend doing it.

It may be possible to have an ON CONNECT trigger that checks if there are new values in a lookup database and import stuff if there are any. Though I've never written an ON CONNECT trigger and don't know about advantages and pitfalls.

What would probably be a safe and common approach, is to put your changes into a script and then have a program that runs this script on all databases. This shouldn't be difficult to create and you may add some 'version control' onto your system at the same time (so that you can check what version is the current for each database before doing updates). If only one special user used by this update program is allowed to update the tables, then it ought to be pretty simple to ascertain all databases have the same content of these tables (well, as long as they're static lookup tables, tables that frequently change may have other issues such that it taking too long time to update all databases). If it is of importance that all databases are updated simultaneously, then Firebird does support multidatabase transactions.

Set