Extracting Primary and Foreign Keys
Posted on 2006-07-16
This is more of an SQL question really...
I know I can get the foreign keys of a table by accessing the CONSTRAINTS and CONS_COLUMNS tables, and I know I can then get the matching Primary key of the referenced table by using the referenced Constraint.
Trouble is, I don't know how to put this into a single SQL statement such that I can identify which is which when the query runs. I want to identify the output as
Primary Key Table
Primary Key Column
Foreign Key Table
Foreign Key Column
but I don't know how to identify each output pair as either a Primary or Foreign Key (the problem being that both are extracted from the same column). I'm guessing a Decode of some sort is required based on the constraint name, but I don't know how to write it.
Can anyone help please?