Oracle: check if composite (!) foreign key exists

0
votes

So I know the table name, the two column names that forms the composite primary key and I have to check programmatically (Oracle SQL function) if this foreign key exists. If the foreign key exists, it's name is unpredictable because it's auto-generated (like SYS_C0075474), so I cannot rely on it. I'm aware of the user_constraints table but it seems to lack the necessary information for me. Any suggestions?

1
sqloracleforeign-keys
If I understand you correctly, you are attempting to see if a value is in a table without knowing the value. That would be difficult don't you think? - Dan Bracuk
Why not just create the constraint with a name? That is highly recommended anyway. - a_horse_with_no_name
@a_horse_with_no_name: yeah, but if you are given a DB without known constraint names... :) - gyorgyabraham

1 Answers

1
votes

You appear to need the [user|all|dba]_cons_columns view.

Something like

SELECT constraint_name
  FROM user_cons_columns
 WHERE table_name = <<your table name>>
   AND column_name IN( <<your first column>>, <<your second column>> )
 GROUP BY constraint_name
HAVING COUNT(*) = 2

will give you the name of the constraint that is defined on exactly those two columns.