CODEWITHSUNDEEP
sqloracle-databaseforeign-keys
gyorgyabraham
gyorgyabraham

Reputation: 2608

Oracle: check if composite (!) foreign key exists

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?

Upvotes: 0

Views: 1230

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

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.

Upvotes: 1

Related Questions