SIMPLE PRIMARY KEYS and FOREIGN KEYS RELATED TASKS

SIMPLE PRIMARY KEYS and FOREIGN KEYS RELATED TASKS


1) How to identify if a particular table has any foreign keys pointing to it’s primary key?

set linesize 200
select a.owner, a.table_name, a.constraint_name
from all_constraints a, all_constraints b
where a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name
and a.r_owner  = b.owner
and b.table_name in ('QP_USER')
and b.owner in ('GALLAUDET');





2) How to DISABLE the foreign keys pointing to the PARENT table?

select 'alter table '||a.owner||'.'||a.table_name||
' DISABLE constraint '||a.constraint_name||';' as "Command to disable fk"
from all_constraints a, all_constraints b
where a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name
and a.r_owner  = b.owner
and b.table_name in ('DEPT')
and b.owner in ('SCOTT');




3) How to ENABLE the foreign keys pointing to the PARENT table?

select 'alter table '||a.owner||'.'||a.table_name||
' ENABLE constraint '||a.constraint_name||';' as "Command to disable fk"
from all_constraints a, all_constraints b
where a.constraint_type = 'R'
and a.r_constraint_name = b.constraint_name
and a.r_owner  = b.owner
and b.table_name in ('DEPT')
and b.owner in ('SCOTT');



4) How to find out foreing keys between parent and child tables in a schema? Query will prompt for the username. 

set linesize 100
set pagesize 200
select p.owner||'.'||pr.table_name||'.'||p.column_name||' matches to '||
  c.owner||'.'||ch.table_name||'.'||c.column_name as "Parent-tbl-matches-2-child-tbl"
from
 all_cons_columns c
, all_cons_columns p
, all_constraints ch
, all_constraints pr
where c.owner='&ENTER_OWNER'
and c.owner=ch.owner
and c.constraint_name=ch.constraint_name
and ch.r_owner=pr.owner
and ch.r_constraint_name=pr.constraint_name
and pr.owner=p.owner
and pr.constraint_name=p.constraint_name
and c.position=p.position
order by pr.table_name;




5) How to find out map foreing keys between parent and child table? Query will prompt for the username and tablename

set linesize 100
set pagesize 200
select p.owner||'.'||pr.table_name||'.'||p.column_name||' matches to '||
  c.owner||'.'||ch.table_name||'.'||c.column_name as "Parent-tbl-matches-2-child-tbl"
from
 all_cons_columns c
, all_cons_columns p
, all_constraints ch
, all_constraints pr
where c.owner='&ENTER_OWNER'
and pr.table_name='&ENTER_TABLE_NAME'
and c.owner=ch.owner
and c.constraint_name=ch.constraint_name
and ch.r_owner=pr.owner
and ch.r_constraint_name=pr.constraint_name
and pr.owner=p.owner
and pr.constraint_name=p.constraint_name
and c.position=p.position
order by pr.table_name;



6) To check which Foreing Key is missing an index, login as user and execute the following:

set linesize 200
col COLUMN_NAME format a70

SELECT * FROM (
 SELECT c.table_name, cc.column_name, cc.position column_position
 FROM   user_constraints c, user_cons_columns cc
 WHERE  c.constraint_name = cc.constraint_name
 AND c.constraint_type = 'R'
 MINUS
 SELECT i.table_name, ic.column_name, ic.column_position
 FROM   user_indexes i, user_ind_columns ic
 WHERE  i.index_name = ic.index_name
 )
 ORDER BY table_name, column_position;


7) Disableall "user" constraints (the same user you login as)

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'ENABLED'
   ORDER BY c.constraint_type DESC, c.last_change DESC)
  LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
  END LOOP;
END;
/



8) Enable all "user" constraints (the same user you login as)

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'DISABLED'
   ORDER BY c.constraint_type)
  LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
  END LOOP;
END;
/