Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Primary - foreign key relationships between different schemas

937454Feb 9 2013 — edited Feb 11 2013
Hi,
My db version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

I have two schemas A, B withing same database D.

I create a table emp in A
I create a table dept in B as below
In schema A
create table emp (
    eno number(2),
    ename varchar2(10),
    deptno number(2));
alter table emp
add constraint pk_emp
primary key (eno);

In schema B
create table dept (
    deptno number(2),
    dname varchar2(15));
alter table dept
add constraint pk_dept
primary key (deptno);
Now when I try to establish a relation between these two tables from schema A, I get an error
alter table emp
add constraint fk_emp_to_dept
foreign key (deptno)
references B.dept (deptno);

error:
ORA-01031: insufficient privileges
I have some questions,
What privileges should I give the schema A, B to establish these table relations?
Is this a good approach, to have related tables spread across schemas? We have several tables and thought it might be good to segregate tables into different schemas?
But are there challenegs with this approach? performance? coding complexity? Or is it ok?
Please advice.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 11 2013
Added on Feb 9 2013
7 comments
3,167 views