Querying Foreign key tables and columns

[Peewee1476]

Hello all. I'm trying to figure out the proper query to return the tables and columns between a foreign key relationship. For instance, the query I'm using now is:

SELECT A.CONSTRAINT_NAME, A.TABLE_NAME,B.COLUMN_NAME, B.COLUMN_NAME AS REFERENCED_COLUMN_NAME, B.TABLE_NAME AS REFERENCED_TABLE_NAME FROM USER_CONSTRAINTS A JOIN USER_CONS_COLUMNS B ON A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE A.CONSTRAINT_TYPE = 'R' AND (A.TABLE_NAME = @TABLE_NAME);

For example purposes, one of the tables I'm using is named SET_AGENT_LOOKUP_VALS. If I place that into @TABLE_NAME I get:





Unfortunately the COLUMN_NAME and REFERENCED_COLUMN_NAME columns both return the parent column with the foreign key relationship. The COLUMN_NAME column should contain the child column with the foreign key relationship. I currently have a SQL Server query that is returning the proper results. I just need to know it's Oracle equivalent. Here's the SQL Server query:

SELECT OBJECT_NAME(FKEYID) AS TABLE_NAME,COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME, COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME FROM SYSFOREIGNKEYS WHERE OBJECT_NAME(FKEYID)=@TABLE_NAME

The result from that display TABLE_NAME as the child table name, COLUMN_NAME as the child column name, REFERENCED_TABLE_NAME as the parent table name, and REFERENCED_COLUMN_NAME as the parent table name. Any help would be appreciated. I'm new when it comes to Oracle (our program supports both Oracle and SQL Server databases and I often only have to edit SQL Server queries.) Thanks.

[Peewee1476]

Well, I guess I figured it out. This:

SELECT A.TABLE_NAME, a.column_name,c.table_name "ref table",c.column_name "ref column"
FROM user_cons_columns a,user_cons_columns c,user_constraints b
WHERE a.constraint_name=b.constraint_name
AND a.table_name=b.table_name
AND (A.table_name=@TABLE_NAME)
AND b.constraint_type='R'
AND b.r_constraint_name=c.constraint_name;

gives me the results I wanted. That gets the parent tables for the foreign key. I also found that changing the (A.table_name = @TABLE_NAME) to (C.table_name = @TABLE_NAME) yields the child tables for the foreign key. The thing is, I don't really fully UNDERSTAND why this works. I came across the answer by perusing several different forums and eventually joining together a hodge podge of different answers into the above query which somehow gave me my answer. Mostly what I don't understand is why is it that table a and table c both reference user_cons_columns and yet a.column_name and c.column_name return different values?

[PAVB]

Is this forum good or what?

Just by posting your question here you got yourself a solution in about 2 hours and a half.

Nice!

[Peewee1476]

Yeah. I still would like a little clarification of anyone can spare it though.

[Peewee1476]

Bump.

[Peewee1476]

Actually I do have a good reason for bumping this. A problem has arisen. It seems that this Oracle statement above only works properly if the foreign keys between two tables are broken up into separate foreign key constraints. In other words, if there are multiple foreign keys between two tables AND those keys are stated in separate constraints on the parent table, that statement works. However, if there are multiple keys between two tables and those keys are defined in ONE constraint, the above doesn't function the same. Instead, it returns erroneous data.

I have a table named SET_STRAT_MTX. It has 3 foreign key relationships with SET_STRAT_MTXNAMES, which are defined in a single constraint simultaneously. Using the SQL Server query of
"SELECT OBJECT_NAME(FKEYID) AS TABLE_NAME,COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME, COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME FROM SYSFOREIGNKEYS WHERE OBJECT_NAME(FKEYID)='SET_STRAT_MTX'"

It returns the 3 relationships by displaying the connection between the CATEGORY,SUBCAT, and MTXNUM columns in both tables. However, the Oracle statement
"SELECT A.TABLE_NAME, a.column_name,c.table_name as REFERENCED_TABLE_NAME,c.column_name as REFERENCED_COLUMN_NAME FROM user_cons_columns a,user_cons_columns c,user_constraints b WHERE a.constraint_name=b.constraint_name AND a.table_name=b.table_name AND A.TABLE_NAME = 'SET_STRAT_MTX' AND b.constraint_type='R' AND b.r_constraint_name=c.constraint_name"

matches up the column CATEGORY with CATEGORY in the other table, as it should, but it also matches it up with SUBCAT and MTXNUM. Then it does the same thing for the other two columns, given one proper match and two wrong matches. I have no idea why this is happening. Screen shots of the data would probably help; I'm at work now but maybe I can post some shots when I get home.

[Peewee1476]

Here's the results from SQL Server. These are the correct results:


Shot at 2008-05-29


Here's the results from Oracle. Notice how there's extra data that's not correct.


Shot at 2008-05-29

So how can I hone the query to get the same results?