Re: Foreign Keys on Multiple Databases

Chris, We have run into the same problem recently. We have developed and deployed applications that run on the same instance/same server. In the past our applications shared very little common data and coming from the "stovepipe" days it was easier to keep the application data separate for various reasons. However, today the complexity of the applications we are developing has increased along with the need to share common data. Since we were unable to create foreign keys across dbs our initial approach was to use a combination of synonyms and triggers to enforce referential integrity. Well if you are talking about one table/one contraint that's not so bad, but we were talking multiple tables/multiple constraints on multiple databases. But somehow it still seemed doable. We didn't hit that brick wall until we starting trying to implement database security using Informix v7 Database Roles. A database role, like a foreign key, is a database object and cannot span databases. We were unable to grant users access to multiple databases using Informix v7 capabilities with roles without a lot of heartache. So we relinguished control and went in the direction we should have been in from the beginning which was to merge application data into one database schema. We still have separate databases for tutorial, and read only public data for ease of refreshing the data with little impact to application users. To make a long story short, I would review your long term goals for the users that being supported by your applications to ensure that you are not painted into a corner by the decisions you make today. If I can be assistance with the triggers, let me know. BTW we are using Informix v7.13 on a DG/UX 4.12 platform. Alicia Ray -- ********************************************************************** Alicia Ray Internet : Alicia.Ray@med.osd.mil EDS-D/SIDDOMS Phone : 703-845-3807 5113 Leesburg Pike Skyline 4 Suite 800 Falls Church, VA 22041-3201 **********************************************************************