How to create a parent and Child Table in 2 different schema ?

Schema 1 : User1 (Will store the parent table TAB1)
Schema 2 : User2 (Will store the parent table TAB2)
Oracle Version : 12.1.0.2

1. Create the parent table TAB1 in USER1 schema with a primary key

USER1 SQL> create table tab1 (n1 number primary key);

Table created.

USER1 SQL> desc tab1
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 N1                                                                NOT NULL NUMBER


2. Grant 'REFERENCE on TAB1' privilege to USER2 schema.

USER1 SQL> grant references on tab1 to user2;

Grant succeeded.

USER1 SQL> exit

3. Create the Child Table TAB2 in USER2 Schema and create the foreign key which references User1.TAB1 table.

USER2 SQL> create table tab2 (
n2 number primary key,
n1 number,
constraint fk_tab2 foreign key(n1) references user1.tab1(n1)
 ); 

Table created.

USER2 SQL>

4. Check the parent child relationship from dba_constraints table:

SYS SQL> SET LINES 300
col parent_owner format A8
col child_owner format A8
col parent_table format A10
col child_table format A10
col parent_cons_name format A15
col child_cons_name format A10

SELECT
   SYS SQL>    p.owner parent_owner,
      c.owner child_owner,
      p.table_name Parent_table,
      p.constraint_name parent_cons_name,
SYS SQL>       c.table_name Child_table,
      c.constraint_name child_cons_name
  FROM
      dba_constraints p, dba_constraints c
where p.owner='&parent_Owner'
  and p.table_name='&Parent_Table_Name'
SYS SQL>   and p.constraint_type IN ('P','U')
  and c.constraint_type='R'
  and p.owner=c.r_owner
  aSYS SQL> nd p.constraint_name=c.r_constraint_name
order by 4;
Enter value for parent_owner: USER1
old  10: where p.owner='&parent_Owner'
new  10: where p.owner='USER1'
Enter value for parent_table_name: TAB1
old  11:   and p.table_name='&Parent_Table_Name'
new  11:   and p.table_name='TAB1'

PARENT_O CHILD_OW PARENT_TAB PARENT_CONS_NAM CHILD_TABL CHILD_CONS
-------- -------- ---------- --------------- ---------- ----------
USER1    USER2    TAB1       SYS_C00120730   TAB2       FK_TAB2

SYS SQL>

No comments:

Post a Comment