Fk Referenced Tables

[kris123]

Hi Friends,

I want to list all the tables which my foriegn-key constrainsts are
renferencing to. I tried to list the dba views that are associated
with constrainsts and

[julian]

Somthing like this:

select table_name from user_constraints where constraint_name in
(select r_constraint_name from user_constraints where constraint_type='R')

[jmodic]

More something like that:

SELECT DISTINCT ac.owner, ac.table_name
FROM ALL_CONSTRAINTS ac
WHERE ac.constraint_type IN ('P','U')
AND EXISTS (SELECT NULL FROM ALL_CONSTRAINTS
            WHERE constraint_type = 'R'
            AND r_owner = ac.owner
            AND r_constraint_name = ac.constraint_name);

[julian]

Originally posted by jmodic
More something like that:

SELECT DISTINCT ac.owner, ac.table_name
FROM ALL_CONSTRAINTS ac
WHERE ac.constraint_type IN ('P','U')
AND EXISTS (SELECT NULL FROM ALL_CONSTRAINTS
            WHERE constraint_type = 'R'
            AND r_owner = ac.owner
            AND r_constraint_name = ac.constraint_name);

Same thing :-) You only list additionally the owner.

[jmodic]

Originally posted by julian
Same thing :-) You only list additionally the owner.

Er, no. You must use ALL_CONSTRAINTS, not USER_CONSTRAINTS - at least in your outer query. Because PK and UK constraints that tables from your schema are referencing might not be in your own schema, no? Otherwise your query might return too few tables, or some of them might simply be the wrong ones.

[julian]

Originally posted by jmodic
Er, no. You must use ALL_CONSTRAINTS, not USER_CONSTRAINTS - at least in your outer query. Because PK and UK constraints that tables from your schema are referencing might not be in your own schema, no? Otherwise your query might return too few tables, or some of them might simply be the wrong ones.

Yes, that's right, there might be interschema constraints. Although I have never seen that in a real database :-)

[JMac]

And always to try to name your FK constraints so that they are mnemonic:

"Table_name/alias_of_FK"_"Table_name/alias_of_PK"_FK

e.g. EMP_DEPTS_FK (or even 'empdeptno_deptsdeptno_fk') for a foreign key on EMP.dept_no referencing DEPTS.dept_no. Then, usually, at a glance you can identify the objects involved.

[abhaysk]

Originally posted by julian
Although I have never seen that in a real database :-)

We do have lots such..

[julian]

Originally posted by abhaysk
We do have lots such..

It's a big world :-)

[jmodic]

Oracle doesn't have "GRANT REFERENCES ON xy TO wz;" just for looking nicer. Some are actualy using this and having interschema constraints is nothing unusual.