Hi everyone,

Is it possible to define a referential constraint using a nickname (federated database table)?

I'm getting the following error:
The nickname "NBM" cannot be referenced in an enforced referential constraint.. SQLCODE=-20269, SQLSTATE= , DRIVER=4.8.86

Does anybody know other ways to enforce a referenctial integrity on a local table using nickname (remote federated table)?

Thank you for help.

Try creating a view over the nickname.

Thanks for the reply, n_i.
I though that it not possible to create a foreign key constraint to a view? Am I wrong here? Anyway I tried creating a view over the nickname and have a foreign key reference to this view. It gives the following error:
"TST.V_TEST" is not allowed in a FOREIGN KEY clause because it does not identify a base table.. SQLCODE=-157, SQLSTATE=42810, DRIVER=4.8.86

It would be if somebody can shed some light on it.

A nickname is just a pointer to a table in some other system. If that remote table is the dependent table, nothing will prevent that other system from inserting a row that would violate the FK. If the remote table would be the parent in a referential constraint, nothing would prevent the other system from removing rows that are needed by local dependent tables. So what's the point of the FK if the system has no chance to enforce it? None.

Using a view as Nick suggested would provide you the capability to query the view and detect inconsistencies yourself (and resolve them somehow) or going the other direction, joining the parent and dependent table in the view so that the view guarantees you'll only see consistent data.

stolze, thanks for the reply.

yes, I understand that a nickname is just a pointer to a remote object.
What I was wondering if there are any other options/workarounds on how to enforce the referential integrity (again by using DB2 itselft and not using views/stored procedures for manual checking) between the local (child) and remote (parent) tables. I'd like to avoid using triggers as they introduce an extra overhead on the processing.

here is the scenario:
database A has a "child" table CHD (with column ref_col_id)
database B has a "parent" table PRT (with column ref_col_id)
An application inserts data into A.CHD. I want to make sure that only records with ref_col_id values that are present in B.PRT are inserted.
The only way to enforce referential integrity by using constraints is to create a mirror copy of B.PRT in database A and reference A.CHD to it.
This brings other issues of storing the data twice and making sure that both A.PRT and B.PRT are always in sync.

I just want to make sure that there is no way to use constraints in this case and the only option is to go with either views/triggers.