Referential Integrity across Schema Borders - Worthwile ?

Martin Zahn, 03.04.2009

Overview

Oracle allows you to create a Foreign Key Constraint across the schema border using the REFERENCES privilege. As an example lets create the following situation.

The Table DEPT in the Schema JONES is referenced by the Table EMP in Schema Scott.

Create the Tables

CREATE USER jones IDENTIFIED BY tiger
  TEMPORARY TABLESPACE temp
  DEFAULT TABLESPACE users;
GRANT CREATE SESSION TO jones;
GRANT DBA TO jones; -- For simplicity, we give it DBA Privileges

CREATE USER scott IDENTIFIED BY tiger
  TEMPORARY TABLESPACE temp
  DEFAULT TABLESPACE users;
GRANT CREATE SESSION TO scott;
GRANT DBA TO scott; -- For simplicity, we give it DBA Privileges

CREATE TABLE JONES.DEPT
(
  deptno  NUMBER(2),
  dname   VARCHAR2(14 BYTE),
  loc     VARCHAR2(13 BYTE)
);

CREATE UNIQUE INDEX jones.pk_dept ON jones.dept (deptno);

ALTER TABLE jones.dept ADD (
  CONSTRAINT pk_dept
  PRIMARY KEY (deptno);

CREATE TABLE scott.emp
(
  empno     NUMBER(4)  NOT NULL,
  ename     VARCHAR2(10 BYTE),
  job       VARCHAR2(9 BYTE),
  mgr       NUMBER(4),
  hiredate  DATE,
  sal       NUMBER(7,2),
  comm      NUMBER(7,2),
  deptno    NUMBER(2)
);

CREATE UNIQUE INDEX scott.pk_emp ON scott.emp (empno);

ALTER TABLE scott.emp ADD (
  CONSTRAINT pk_scott
  PRIMARY KEY (empno));

Create the Foreign Key across Schema Border

If we try to create the Foreign Key Constraint we will get an ORA-00942

ALTER TABLE scott.emp ADD (
  CONSTRAINT fk_emp_dept
 FOREIGN KEY (deptno)
 REFERENCES jones.dept (deptno));
ORA-00942: table or view does not exist

The reason for this error is, that the User SCOTT cannot «see» the Table DEPT in Schema JONES - he needs the REFERENCES Privilege.

GRANT REFERENCES, UPDATE ON jones.dept TO SCOTT;
Grant succeeded.
ALTER TABLE scott.emp ADD (
  CONSTRAINT fk_emp_dept
 FOREIGN KEY (deptno)
 REFERENCES jones.dept (deptno));
Table altered.

Pitfalls with such a Construct

A Foreign Key which passes over its own Schema Border will imply a Dependency between the Schemas. In the example there is now a Relationship between the two Schemas SCOTT and JONES. That means Schema SCOTT is dependent on Schema JONES. This is often difficult to handle, specially when Schemas are scripted - you will get Errors if the Order is not considered.

When you try to REVOKE the Privilege, you will get ORA-01981

REVOKE ALL ON jones.dept FROM scott;
ORA-01981: CASCADE CONSTRAINTS must be specified to perform this revoke

REVOKE ALL ON jones.dept FROM scott CASCADE CONSTRAINT;
Revoke succeeded.

However whats happen here ?

Revoking SCOTT REFERENCES privilege on JONES.dept causes Oracle to drop the fk_emp_dept constraint, because SCOTT required the privilege to define the constraint.

Conclusion

Oracle allows References between Schemas using the REFERENCES Privilege. However we suggest not to use it due to complexity, dependency and the danger that Foreign Key Constraints are dropped in the «Background» when using CASCADE CONSTRAINT;