Foreign keys referring table in other database
Hi ! IDS 11.50.FC6 I need to set up constraints between tables in different databases. According to the documentation the tables used in a foreign key definition must be in the same database. I have also tried to use synonyms and views thar refers the table in the other databas but no success. One idea is to use ER to replicate the tables in question, another would be to write triggers that does a check against synonyms. Any other ideas anybody ? TIA Ulf
You can't do what you want unless you consider using a before insert trigger. WARNING! While you could in theory do this... you really don't want to do this. It will kill your performance. Depending on how static the data that is in the table that you're using as your constraint, you may want to consider replicating the data to the database and the you could use a simple foreign key constraint. Also do you have a use case for this? I can't think of any where you will want to constrain your data against anything which is outside of your data model. Note again, data models do not cross database borders. That's a no no. > From: ulf.akerberg@gmail.com > Subject: Foreign keys referring table in other database > Date: Thu, 6 May 2010 03:54:00 -0700 > To: informix-list@iiug.org > > > > Hi ! > > IDS 11.50.FC6 > > I need to set up constraints between tables in different databases. > According to the documentation the tables used in a foreign key > definition must be in the same database. > I have also tried to use synonyms and views thar refers the table in > the other databas but no success. > > One idea is to use ER to replicate the tables in question, another > would be to write triggers that does a check against synonyms. > > Any other ideas anybody ? > > TIA > > > Ulf > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Hotmail is redefining busy with tools for the New Busy. Get more from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2
Using ER to replicate the independent table to the local server so you can constrain against it is the only really viable idea. Unless you can replicate it at the application level. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 6, 2010 at 6:54 AM, Ulf <ulf.akerberg@gmail.com> wrote: > > > Hi ! > > IDS 11.50.FC6 > > I need to set up constraints between tables in different databases. > According to the documentation the tables used in a foreign key > definition must be in the same database. > I have also tried to use synonyms and views thar refers the table in > the other databas but no success. > > One idea is to use ER to replicate the tables in question, another > would be to write triggers that does a check against synonyms. > > Any other ideas anybody ? > > TIA > > > Ulf > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >