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

ASM and Database does not auto start in Oracle restart environment (After Server start)

 If you are supporting Any Oracle restart environment and if you notice that your ASM instance and Database instance does not start automati...