Referential Integrity across
Schema Borders - Worthwile ?
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;
|
