How can I see the FK from Oracle DB? (Oracle 18c)-CodePudding

I created a SQL. It show me an error below this.

ORA-02291: integrity constraint (MYDBS.FK31_1)

I want to see this FK31_1. Is it possible to run SQL to get it?

Please let me know how to do that.

And I give you my love to seeing this question :)

CodePudding user response：

Select from DBA_CONSTRAINTS and DBA_CONS_COLUMNS, if you have access rights. Otherwise try ALL_CONSTRAINTS and ALL_CONS_COLUMNS. Join the columns table twice, so as to get the column(s) of the primary or unique key of the parent table combined with the column(s) of the foreign key of the child table:

select
  fkc.table_name as child_table,
  fkc.column_name as child_table_col,
  pkc.table_name as parent_table,
  pkc.column_name as parent_table_col
from dba_constraints fk
join dba_cons_columns fkc on fkc.owner = fk.owner
                         and fkc.constraint_name = fk.constraint_name
join dba_cons_columns pkc on pkc.owner = fk.r_owner
                         and pkc.constraint_name = fk.r_constraint_name
                         and pkc.position = fkc.position
where fk.owner = 'MYDBS'
and fk.constraint_name = 'FK31_1'
order by fkc.position;

CodePudding user response：

Oracle provides plenty metadata views, which start from ALL_, USER_ and DBA_. In our case we wants ALL_CONSTRAINTS which you can query as

select *
  from ALL_CONSTRAINTS
 where CONSTRAINT_NAME = 'FK31_1'

Edit: As Jeff Holt mentioned in the comment below you can try ALL_CONS_COLUMNS as well:

select *
  from ALL_CONS_COLUMNS 
 where CONSTRAINT_NAME = 'FK31_1'