asked on

Extracting Primary and Foreign Keys

Hi all,

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

Constraint Name
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?

Many thanks
Gerry

Peter Kwan

select
c1.constraint_name,
decode(c2.constraint_type, 'R', c3.table_name, c1.table_name) pk_table,
decode(c2.constraint_type, 'R', c3.column_name, c1.column_name) pk_col,
decode(c2.constraint_type, 'R', c1.table_name, null) fk_table,
decode(c2.constraint_type, 'R', c1.column_name, null) fk_col
from
user_cons_columns c1, user_constraints c2, user_cons_columns c3
where c1.constraint_name = c2.constraint_name and c2.r_constraint_name = c3.constraint_name (+)

gamesmeister

ASKER

Thanks for that.

However, while this works for single column foreign keys, in the case of multiple column keys, it doesn't tie up the individual columns on either side i.e.

PKTable A, PKColumns 1 & 2
FKTable B, FKColumns 1 & 2

I'm getting results back that point B.1 to A.2 and vice versa

Any idea how to fix that?

Thanks again
Gerry

gamesmeister

ASKER

Update to the previous post:

I'm getting 4 rows back:

A.1 to B.1
A.2 to B.1
A.1 to B.2
A.2 to B.2

Thanks
Gerry

MohanKNair

select a.TABLE_NAME||'.'||c.column_name||' References '||b.table_name val
from all_constraints a, all_constraints b, all_cons_columns c
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and a.owner=b.owner
and a.owner=c.owner
/

SOLUTION

Peter Kwan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gamesmeister

ASKER

Yeah, that works. However, the only thing I would say is that it seems to return many more rows than I need, but that said, I can use code to filter the unwanted rows out (it seems to be returning all columns in a selected table, not just those related to a foreign key).

Mohan, thanks for that. Your statement only picks up the rows I need, but it doesn't give me the primary key column(s). Is it possible to add that to the statement?

Many thanks
Gerry

MohanKNair

select a.TABLE_NAME||'('||
rtrim(max(decode(c.position,1,c.column_name))||','||
max(decode(c.position,2,c.column_name))||','||
max(decode(c.position,3,c.column_name))||','||
max(decode(c.position,4,c.column_name)),',')||') References '||
b.table_name||'('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name)),',')||')'
from all_constraints a, all_constraints b, all_cons_columns c, all_cons_columns d
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and b.CONSTRAINT_TYPE='P'
and a.constraint_name='BOX_OFFICE_DAILY_FK1'
and a.owner=b.owner
and a.owner=c.owner
and a.owner=d.owner
and a.owner='WB2DEV'
group by a.table_name, b.table_name
/

ASKER CERTIFIED SOLUTION

MohanKNair

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gamesmeister

ASKER

Outstanding, thank you both very much