Oracle primary key and foreign key related knowledge

[Email protected]> CREATE TABLE emp_1 as SELECT * from EMP; --Schedule table created. [Email protected]> CREATE TABLE dept_1 as SELECT * from dept; --table created for main tables. [Email protected]> ALTER TABLE dept_1 add constraint pk_dept_1 primary key (DEPTNO); --primary table Add primary KEY constraint table altered. [Email protected]> ALTER TABLE emp_1 add constraint fk_emp_1 foreign key (DEPTNO) references dept_1 (DEPTNO); Table altered. --Add a FOREIGN KEY constraint

Index key value of the query table:

select a.owner, --PRIMARY Key owner                   a.table_name, --primary key Table                  b.column_name, --primary key Columns                  c.owner, --FOREIGN Key Holders                   c.table_name, --foreign key Table                   d.column_name - -Foreign key column       from user_constraints a  left join user_ cons_columns b    on a.constraint_name = b.constraint_name   left join user_constraints c    on c.r_constraint_name =  a.constraint_name   left join user_cons_columns d    on c.constraint_name =  d.constraint_name where a.constraint_type =  ' P '       and  a.table_name IN  (' dept_1 ',  ' emp_1 ')  --the table that needs to see the primary foreign key relationship      16    ORDER BY a.table_name;OWNER    TABLE_NAME     column_nam owner  table_name  column_nam---------- --------------- -- -------- ---------- --------------- ----------scott    dept_1     DEPTNO     SCOTT  EMP_1   DEPTNO1 row  Selected.

The dept_1 and Emp_1 columns Deptno primary foreign key relationships are discovered through the above.

If you delete dept_1 data, you must perform a full table scan of the schedule emp_1:

The following statements can be queried: those foreign keys are not indexed:

select table_name,        constraint_name,         cname1 | | &NBSP;NVL2 (cname2,  ', '  | |  cname2, null)  | | &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;NVL2 (cname3,  ', '  | |  cname3, null)  | | &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;NVL2 (cname4,  ', '  | |  cname4, null)  | | &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;NVL2 (cname5,  ', '  | |  cname5, null)  | | &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;NVL2 (cname6,  ', '  | |  cname6, null)  | | &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;NVL2 (cname7,  ', '  | |  cname7, null)  | | &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;NVL2 (cname8,  ', '  | |  cname8, null)  COLUMNS   FROM  (select b.table_name,                 b.constraint_name,                 max (DECODE (position, 1, column_name, null))  CNAME1,                 max (DECODE (POSITION,  2, column_name, null))  CNAME2,                 max (DECODE (position, 3, column_name, null))   Cname3,                max ( DECODE (position, 4, column_name, null))  CNAME4,                 max (DECODE (position, 5, column_name, null ))  CNAME5,                 max (DECODE (position, 6, column_name, null))  CNAME6,                 max (DECODE (position, 7, column_name, null))  CNAME7,                 max (DECODE ( Position, 8, column_name, null))  CNAME8,                 count (*)  COL_CNT            FROM  (Select substr (table_name, 1, 30)  table_name,                          substr (constraint_name, 1, 30)  CONSTRAINT_NAME,                          substr (COLUMN_NAME,&NBSP;1,&NBSP;30)  COLUMN_NAME,                         POSITION                    from user_cons_ COLUMNS)  A,                 user_constraints b          where a. constraint_name = b.constraint_name             AND B.CONSTRAINT_TYPE =  ' R '            group by b.table_name, b.constraint_name)  CONS  WHERE COL_CNT  > ALL   (Select count (*)             FROM USER_IND_COLUMNS I          where i.table_name = cons. table_name            and i.column_name  in  (cname1, cname2, cname3, cname4, cname5,        &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;CNAME6,&NBSP;CNAME7,&NBSP;CNAME8)              and i.column_position <= cons. Col_cnt  37           group by i. index_name)  ; Table_name constraint_name      columns--------------- --------------- ----- ---------------emp_1  fk_emp_1      deptno1 row  Selected.

= = = The foreign key above to build the index ===========

Oracle primary key and foreign key related knowledge