FOREIGN KEY OVER SYNONYM

Hi all, we have created a synonym over a database like this: database db2; create synonym t1 for db1:t2; it's working fine. However, when we try to define a foreign key over this synonym, we get an error number 556. Any suggestion? -- Francisco Gaspar SISTEL (Servicios Informáticos de Software y Telecomunicaciones, S.L.) C/Callosa de Segura,9 03005 Alicante Tel: 96 592 25 75 - 96 592 99 43 Fax: 96 592 98 40

The foreign key will have to point to the real table, however, you cannot define a foreign key to a table in another database! That is the meaning of sqlcode -556 (see finderr -556 for details) in your context. A foreign key creates a record in sysreferences. This is used to enforce relational integrity, ie preventing deletion of a parent key that has children in the refering table. It must be resident in the sysreferences table in the database that contains the primary key table, but, it is not legal to alter the structure of a "remote" database. If tables in db2 reference tables in db1 then those tables really should reside in db1 instead! Indeed they must for RI purposes, as you have discovered. Art S. Kagel Francisco Gaspar wrote: > > Hi all, > > we have created a synonym over a database like this: > > database db2; > create synonym t1 for db1:t2;> > it's working fine. > > However, when we try to define a foreign key over this synonym, we get > an error number 556. > > Any suggestion? > > -- > Francisco Gaspar > SISTEL (Servicios Informáticos de Software y Telecomunicaciones, S.L.) > C/Callosa de Segura,9 > 03005 Alicante > Tel: 96 592 25 75 - 96 592 99 43 > Fax: 96 592 98 40