Getting the FKs established between two or more tables

[Pumuky]

Hi all,
I would need to know the way to get the FKs between two or more tables.
Could you help me?

Thank you in advance,
Pumuky

[amitpatel66]

Hi all,
I would need to know the way to get the FKs between two or more tables.
Could you help me?

Thank you in advance,
Pumuky

In order to find the FOREIGN KEY CONSTRAINT between two tables, use below query:

SELECT acc1.table_name "PK TABLE",acc1.column_name "PK COLUMN", acc2.table_name "FK TABLE", acc2.column_name "FK COLUMN"
FROM ALL_CONS_COLUMNS acc1,ALL_CONS_COLUMNS acc2,ALL_CONSTRAINTS ac
WHERE  ac.constraint_name = acc2.constraint_name
AND ac.r_constraint_name = acc1.constraint_name
AND    acc1.table_name = 'DEPT'
AND acc2.table_name = 'EMP'
 

The above query gives you the PK-FK relation ship between EMP and DEPT table

[Pumuky]

Thank you amitpatel66 but I think that I explained bad...

Your query return me all FK from one table and I need the FK name and the column name relationing two tables, I mean I have two tables and I need to know how they are relationated, obtaining their constraints and their column names.
Imagine that the query result is something like this
TABLE1, CONSTRAINT_NAME, COLUMN_NAME1, TABLE2, COLUMN_NAME2

indication that table1 has a FK named 'constraint_name1' for your colum 'column_name1' referencing the column 'column_name2' belonging to the 'table2'


I need this information to complete anothe query in your where condigition....
select * from T1, T2
where T1.Colum1=T2.colum2
and ......

Any idea?

[Pumuky]

Excuse me by the previous email, I have read another query.
Your query work but only in one direction. I complete it with an union and now it look likes that it is working

SELECT acc1.table_name "PK TABLE",acc1.column_name "PK COLUMN", acc2.table_name "FK TABLE", acc2.column_name "FK COLUMN"
FROM ALL_CONS_COLUMNS acc1,ALL_CONS_COLUMNS acc2,ALL_CONSTRAINTS ac
WHERE ac.constraint_name = acc2.constraint_name
AND ac.r_constraint_name = acc1.constraint_name
AND acc1.table_name = 'DEPT'
AND acc2.table_name = 'EMP'
UNION
SELECT acc1.table_name "PK TABLE",acc1.column_name "PK COLUMN", acc2.table_name "FK TABLE", acc2.column_name "FK COLUMN"
FROM ALL_CONS_COLUMNS acc1,ALL_CONS_COLUMNS acc2,ALL_CONSTRAINTS ac
WHERE ac.constraint_name = acc2.constraint_name
AND ac.r_constraint_name = acc1.constraint_name
AND acc1.table_name = 'EMP'
AND acc2.table_name = 'DEPT'


Thanks so much!

[amitpatel66]

[quote=
Imagine that the query result is something like this
TABLE1, CONSTRAINT_NAME, COLUMN_NAME1, TABLE2, COLUMN_NAME2

indication that table1 has a FK named 'constraint_name1' for your colum 'column_name1' referencing the column 'column_name2' belonging to the 'table2'


I need this information to complete anothe query in your where condigition....
select * from T1, T2
where T1.Colum1=T2.colum2
and ......

Any idea?[/QUOTE]

Use Below Query as per your request:

SELECT acc2.table_name TABLE1,acc2.constraint_name CONSTRAINT_NAME,acc2.column_name COLUMN_NAME1,acc1.table_name TABLE2,acc1.column_name COLUMN_NAME2
FROM ALL_CONS_COLUMNS acc1,ALL_CONS_COLUMNS acc2,ALL_CONSTRAINTS ac
WHERE  ac.constraint_name = acc2.constraint_name
AND ac.r_constraint_name = acc1.constraint_name
AND    acc1.table_name = 'DEPT'
AND acc2.table_name = 'EMP'
 

[Pumuky]

Apologies for the inconvienences but It does not work as expected.

This is the FK that I have defined in LOAD_ACTIONS table

CONSTRAINT FK_ACTIONS2LOADS FOREIGN KEY(ACTION_FILENAME,ACTION_CHK_SUM) 
REFERENCES LOADS(FILENAME,CHK_SUM) ON DELETE CASCADE

And when I execute the following query...

SELECT acc1.table_name "PK TABLE",acc1.column_name "PK COLUMN", acc2.table_name "FK TABLE", cc2.column_name "FK COLUMN" 
FROM ALL_CONS_COLUMNS acc1, ALL_CONS_COLUMNS acc2, ALL_CONSTRAINTS ac
WHERE  ac.constraint_name = acc2.constraint_name
AND ac.r_constraint_name = acc1.constraint_name
and acc1.owner=acc2.owner
and acc1.owner='my_schema'
and ac.owner='my_schema'
AND acc1.table_name = 'LOADS'
AND acc2.table_name = 'LOAD_ACTIONS'

I got the follwoing answer...

LOADS    FILENAME    LOAD_ACTIONS    ACTION_FILENAME
LOADS    CHK_SUM    LOAD_ACTIONS    ACTION_FILENAME
LOADS    FILENAME    LOAD_ACTIONS    ACTION_CHK_SUM
LOADS    CHK_SUM    LOAD_ACTIONS    ACTION_CHK_SUM

As you can see the result that I will expect to obtain will be only

LOADS    FILENAME    LOAD_ACTIONS    ACTION_FILENAME
LOADS    CHK_SUM    LOAD_ACTIONS    ACTION_CHK_SUM

I am trying to get it without success.
Have you got any idea about how to get it.

Thanks so much in advance.
Pumuky

[amitpatel66]

Apologies for the inconvienences but It does not work as expected.

This is the FK that I have defined in LOAD_ACTIONS table

CONSTRAINT FK_ACTIONS2LOADS FOREIGN KEY(ACTION_FILENAME,ACTION_CHK_SUM) 
REFERENCES LOADS(FILENAME,CHK_SUM) ON DELETE CASCADE

And when I execute the following query...

SELECT acc1.table_name "PK TABLE",acc1.column_name "PK COLUMN", acc2.table_name "FK TABLE", cc2.column_name "FK COLUMN" 
FROM ALL_CONS_COLUMNS acc1, ALL_CONS_COLUMNS acc2, ALL_CONSTRAINTS ac
WHERE  ac.constraint_name = acc2.constraint_name
AND ac.r_constraint_name = acc1.constraint_name
and acc1.owner=acc2.owner
and acc1.owner='my_schema'
and ac.owner='my_schema'
AND acc1.table_name = 'LOADS'
AND acc2.table_name = 'LOAD_ACTIONS'

I got the follwoing answer...

LOADS    FILENAME    LOAD_ACTIONS    ACTION_FILENAME
LOADS    CHK_SUM    LOAD_ACTIONS    ACTION_FILENAME
LOADS    FILENAME    LOAD_ACTIONS    ACTION_CHK_SUM
LOADS    CHK_SUM    LOAD_ACTIONS    ACTION_CHK_SUM

As you can see the result that I will expect to obtain will be only

LOADS    FILENAME    LOAD_ACTIONS    ACTION_FILENAME
LOADS    CHK_SUM    LOAD_ACTIONS    ACTION_CHK_SUM

I am trying to get it without success.
Have you got any idea about how to get it.

Thanks so much in advance.
Pumuky

Try below Query to get the desired result:

SELECT acc1.table_name TABLE1,acc1.constraint_name CONSTRAINT_NAME,acc1.column_name COLUMN_NAME1,acc2.table_name TABLE2,acc2.column_name COLUMN_NAME2
FROM ALL_CONS_COLUMNS acc1,ALL_CONS_COLUMNS acc2,ALL_CONSTRAINTS ac
WHERE  ac.constraint_name = acc2.constraint_name
AND ac.r_constraint_name = acc1.constraint_name
AND acc1.position = acc2.position
AND acc1.table_name = 'LOADS'
AND acc2.table_name = 'LOAD_ACTION'
 

[Pumuky]

Good man!! now it is working perfectly.

Thank you very much for your quick and precise support.
Pumuky

[amitpatel66]

Good man!! now it is working perfectly.

Thank you very much for your quick and precise support.
Pumuky

You are Welcome!!
Do POST in case if you have any oracle related issues in future!!
Thanks!!