Wednesday, February 11, 2009

create Foreign key On two diffrent schema table

Some times you may face easy questions which can put into difficulties. Today I got that kind of a question and i am happy so that I get the answer though it take some time find it.

Here is the question which i got from a forum member :

" how to link a table from scott user to the hr user using a foreign key. For an example hr.emp to scott.dept using a foreign key "

In my answer, I assume hr.emp table has a colunm name scott_dept_id . So I create a foreign key on hr.emp (scott_dept_id) by referencing scott.dept (id).

sql> connect scott / pass;
sql> GRANT select,update, delete, references on dept to hr;

Though only references privilege required to create the foreign key

sql> connect hr/password;
sql> alter table emp add constraint fk_test foreign key (scott_dept_id) references scott.dept (id);

So simple ! but hard enough to bother you, if you not know exactly . Thanks to that forum user who lead me to learn this thing .

Post a Comment