Implementing referential-integrity between master
Hello everybody, I am facing the following problem: I have a master table located in a different database than the detail table. I want to implement referential-integrity relationship between them. I tried to create a synonym of master table in the detail tables database but I failed due to the fact that it is not possible to reference a synonym in the REFERENCES clause of ALTER TABLE ADD CONSTRAINT FOREIGN KEY statement as the link shows: http://www-01.ibm.com/support/knowledgecenter/#!/SSGU8G_12.1.0/com.ibm.sqls.doc/ ids_sqs_1972.htm Is there any way I can implement the integrity other than coding triggers and stored procedures? Thank you for your comments, Juan Roca
The only viable alternative to trigger based integrity checking is to have a local copy of the master table in the same database as the child table. You would have to maintain the copy itself using triggers on the master table or using Change Data Capture. If you want full blown integrity including preventing the deletion of master rows that are still being referenced, then a delete trigger on the master table at a minimum is required regardless of how you maintain the contents of the duplicate master table. The duplicate, BTW, only needs the key columns from the master, not all of the data. You can still use a synonym or remote access to the actual master table for row attribute data. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Fri, Jan 9, 2015 at 7:46 AM, JUAN ROCA <jluis.roca@hotmail.com> wrote: > Hello everybody, > I am facing the following problem: > > I have a master table located in a different database than the detail > table. > I want to implement referential-integrity relationship between them. > I tried to create a synonym of master table in the detail tables database > but > I failed due to the fact that it is not possible to reference a synonym in > the > REFERENCES clause of ALTER TABLE ADD CONSTRAINT FOREIGN KEY statement as > the > link shows: > > > > http://www-01.ibm.com/support/knowledgecenter/#!/SSGU8G_12.1.0/com.ibm.sqls.doc/ ids_sqs_1972.htm > > Is there any way I can implement the integrity other than coding triggers > and > stored procedures? > > Thank you for your comments, > Juan Roca > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158b7cc48dc4b050c38a871