Sql – how to find foreign keys constraints

foreign keyoracle10gsql

I have a table called abs with primary key absId, this is used as a foreign key in couple of tables which I don't know. Is there a way that I can find out how find the names of those tables in which the primary key absId is referenced? I'm using Oracle 10g as the database.
Thanks.

Best Answer

If ABS is the parent table in the foreign key relationship and the name of the primary key constraint is ABS_PK, you can identify all the foreign key constraints that reference this primary key using the DBA_CONSTRAINTS view. Something like

SELECT owner, constraint_name, table_name
  FROM dba_constraints
 WHERE r_owner = <<owner of ABS_PK>>
   AND r_constraint_name = 'ABS_PK'
   AND constraint_type   = 'R'
Related Topic