Re: Another foreign key question

They are your unfriendly recursive relationships. The same table is used for both the child and the parent.

Ie. An employee table, with a field: manager Each employee has a manager, who is also an employee.

The table is self-joined. Imagine doing the same for the employees and the manager. You could report either way:

Employee Manager

Manager          Employees
x                1
                 2
                 etc

But you have to join the table to itself to match the row with the employee, with the row with the manager's info.

This is the one area of the Oracle data dictionary that is obvious. I spent a long time with it myself.

Ciao,

Argosy

In article <8eck70$d3a$1_at_nnrp1.deja.com>,   laulau823_at_my-deja.com wrote:
> Hello all,
>
> I have posted a question about finding the referenced column of a
> foreign key a few days ago. Someone (by Michel Cadot) posted the
> following SQL statement can do the task:
>
> select cons1.table_name c1, cons1.constraint_name c2, cons2.table_name
> c3, col1.column_name c4, col2.column_name c5
> from user_cons_columns col2, user_cons_columns col1, user_constraints
> cons2, user_constraints cons1
> where col2.position = col1.position
> and (col2.owner= cons2.owner and col2.constraint_name =
> cons2.constraint_name)
> and (col1.owner = cons1.owner and col1.constraint_name =
> cons1.constraint_name)
> and (cons2.owner = cons1.r_owner and cons2.constraint_name =
> cons1.r_constraint_name)
> and cons1.constraint_type = 'R'
> and cons1.table_name in ('T1','T2')
> order by cons1.table_name, cons1.constraint_name, col2.position
>
> However, I am not fully understand the exact meaning of this
> statement. I don't know why it is necessary to use two same tables
> (col2, col1 and cons2 and cons1). Could anyone explain why it is
> necessary to use two same tables in this query to find the referenced
> column? Is it possible to find the tables which have the foreign keys
> reference to the current table (i.e. find the child table of the
> current table)?
>
> Thanks,
> David
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 28 2000 - 00:00:00 CDT