I have the same issue where I want to enforce a foreign key constraint
between tables in different schemas running on distinct Oracle
instances.
My solution was to create database link and synonym for TABLE2 in
SCHEMA1. Oracle won't allow you to define a foreign key constraint over
the database link, but you can use triggers to enforce integrity (see
below).
Of course entites from schema #2 are now defined also in the data map
for schema #1. This is appropriate for me becase these entities belong
logically in the same data domain and I don't ever write to both data
maps in the same operation. Your mileage may vary.
Phil Miller
SQL looks something like this:
-- 8< --
create database linke %LINK_NAME% connect to %SCHEMA% identified by
%USER% using %CONNECTION_STRING%
create synonym %TABLE2% for %TABLE2..LINK_NAME%
CREATE OR REPLACE TRIGGER
%FK_TRIGGER_NAME%
AFTER
INSERT OR UPDATE OF %COLUMN_NAME%
ON %TABLE1%
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
i NUMBER;
BEGIN
IF :NEW.%COLUMN_NAME% > 0 THEN
SELECT COUNT(%PRIMARY_KEY%) INTO i
FROM %TABLE2%
WHERE %PRIMARY_KEY% = :NEW.%COLUMN_NAME% AND ROWNUM <=1;
IF i < 1 THEN
RAISE_APPLICATION_ERROR(-20734, '%Error message%');
END IF;
END IF;
END;
/
> -----Original Message-----
> From: Mike Kienenberger [mailto:mkienen..mail.com]
> Sent: 18 May 2006 23:02
> To: cayenne-use..ncubator.apache.org
> Subject: Error when join table spans datamaps
>
> Here's an odd error that I've hit for the first time:
>
> Caused by: java.sql.SQLException: invalid schema name:
> ENG_WORK_MGMT in statement [SELECT DISTINCT t0.ACTIVITY_YEAR,
> t0.BUDGET_CODE, t0.GL, t0.IS_ACTIVE, t0.NAME, t0.OBJ_TYPE,
> t0.SUB_ACTIVITY, t0.WORK_ORDER, t0.ACTIVITY, t0.ID,
> t0.WORK_TYPE FROM CORE_WORK_MGMT.WORK t0,
> ENG_WORK_MGMT.AUTHORIZATION_DOC__WORK t1 WHERE t0.ID =
> t1.WORK_ID AND (t1.AUTHORIZATION_DOCUMENT_ID = ?)]
>
> It's a join table where one foreign key points to a different
> DataMap which is in a different DataNode/schema.
>
> Any thoughts?
>
This e-mail (and any attachments) is confidential and may contain
personal views which are not the views of the BBC unless specifically
stated.
If you have received it in error, please delete it from your system.
Do not use, copy or disclose the information in any way nor act in
reliance on it and notify the sender immediately. Please note that the
BBC monitors e-mails sent or received.
Further communication will signify your consent to this.
This archive was generated by hypermail 2.0.0 : Fri May 19 2006 - 06:25:36 EDT