You are here:

Oracle/Foreign key field name

Advertisement


Question
How can I find the foreign key field name using a query?

Answer
Good day Sathish

This is what you need

-- Find all columns on the table with a Foreign key
SELECT cols.table_name, cols.column_name, cons.constraint_type, cols.position, cons.status, cons.constraint_name, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = '<TABLE_NAME>'
AND cons.constraint_type = 'R'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

Hope this helps.  If it is not what you need, please let me know and give me an example.

Thanks
Elliot

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Elliot Mak

Expertise

I can answer questions with regarding to Oracle DB (8i, 9i, 10g, 11g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).

Experience

I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.

Education/Credentials
Bachelor of Science in Management System

©2016 About.com. All rights reserved.