Re: Referential integrity across schemas with synonyms?

>CREATE SYNONYM TEST_TABLE FOR SERVER.TEST_TABLE;
>
>CREATE TABLE APP_TABLE(
> ID NUMBER NOT NULL PRIMARY KEY,
> REF NUMBER NOT NULL REFERENCES TEST_TABLE(ID)
>);
>
>The CREATE TABLE APP_TABLE complains about "table or view does not exist".

Why don't you just create APP_TABLE to reference SERVER.TEST_TABLE? Unless the users query data dictionary views, they won't know who owns TEST_TABLE (and they probably won't care).

I suspect that the reason the CREATE TABLE fails is that the table being referenced MUST be a table, not a synonym for some other object. Since you didn't qaulify it with the schema, it used the APP01 schema by default. And if you query ALL_TABLES for APP01.TEST_TABLE, you obviously won't find it. Thus the CREATE TABLE command fails.

Hope that helps,
Brian Peasland
peasland_at_msn.com Received on Mon Jun 07 1999 - 20:07:35 CDT