Getting the FKs established between two or more tables | Newbie | | Join Date: Sep 2007
Posts: 6
| | |
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
|  | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Getting the FKs established between two or more tables
Quote:
Originally Posted by 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 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
| | Newbie | | Join Date: Sep 2007
Posts: 6
| | | re: Getting the FKs established between two or more tables
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?
| | Newbie | | Join Date: Sep 2007
Posts: 6
| | | re: Getting the FKs established between two or more tables
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!
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Getting the FKs established between two or more tables
[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'
-
| | Newbie | | Join Date: Sep 2007
Posts: 6
| | | re: Getting the FKs established between two or more tables
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
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Getting the FKs established between two or more tables
Quote:
Originally Posted by 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 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'
-
| | Newbie | | Join Date: Sep 2007
Posts: 6
| | | re: Getting the FKs established between two or more tables
Good man!! now it is working perfectly.
Thank you very much for your quick and precise support.
Pumuky
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Getting the FKs established between two or more tables
Quote:
Originally Posted by Pumuky 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!!
|  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,546 network members.
|
