0

I want to truncate table tableA, but I have to disable all foreign keys or drop them which referes to tableA before I do this. Could you tell me the way how to get name of tables which have foregin keys to tableA?

Ice
  • 1,783
  • 4
  • 26
  • 52
  • 3
    http://stackoverflow.com/questions/1729996/list-of-foreign-keys-and-the-tables-they-reference here you should find an answer – Kacper Oct 06 '16 at 12:29

2 Answers2

1

OK, so suppose I want to drop the DEPT table from the SCOTT schema. I check and find out it has a primary key named PK_DEPT. Then I run the query below (against the table ALL_CONSTRAINTS) and find the schema and table that references this table.

Just remember that all string values (table names, constraint types etc.) are always UPPER CASE in all catalog tables. This is important when you write the WHERE conditions.

select owner, table_name 
from   all_constraints 
where  constraint_type = 'R' and r_owner = 'SCOTT' and r_constraint_name = 'PK_DEPT';

OWNER                TABLE_NAME
-------------------- --------------------
SCOTT                EMP

1 row selected.
1

A general solution.

List all tables and owners that have a foreign key constraint for table :tablename that are of type P (Primary key) or U (unique constraint)

select owner, table_name, constraint_name
  from all_constraints
 where r_constraint_name in (select constraint_name
             from all_constraints
               where table_name = :tablename 
               and constraint_type in ('P', 'U'));
Olafur Tryggvason
  • 4,761
  • 24
  • 30
  • A more general solution is to write both select statements against ALL_CONSTRAINTS and to include the ref OWNER as well as TABLE_NAME, since referential constraints may go across schemas. –  Oct 06 '16 at 14:30
  • Correct mathguy, changed the sql to reflect owner and use all_constraints – Olafur Tryggvason Oct 06 '16 at 14:35