Fk Referenced Tables

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

Somthing like this:

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

More something like that:

Code:

---

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);

---

Quote:

---

Originally posted by jmodic
More something like that:

Code:

---

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.

Quote:

---

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.

Quote:

---

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 :-)

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.

Quote:

---

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

---

We do have lots such.. :p

Quote:

---

Originally posted by abhaysk
We do have lots such.. :p

---

It's a big world :-)

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.

Quote:

---

Originally posted by 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.

---

Doesn't Oracle really have REFERENCES ON xy TO wz;" just for looking nicer :-)

Now seriously: there are many features in Oracle just for the sake of many features.

Thaks to all of you friends...
But i want the list of foriegn constraint and its referenced table...
something like:

FORIEGN_CONSTRAINT REFERENCED_TABLE REFERENCED_COLUMN
------------------

Code:

---

SELECT uc.constraint_name AS fk_constraint,
      ac.owner AS ref_owner,
      ac.table_name AS ref_table,
      acc.column_name AS ref_column
  FROM ALL_CONSTRAINTS ac,
      ALL_CONS_COLUMNS acc,
      USER_CONSTRAINTS uc
 WHERE ac.constraint_type IN ('P','U')
  AND ac.owner = acc.owner
  AND ac.constraint_name = acc.constraint_name
  AND uc.constraint_type = 'R'
  AND uc.r_owner = ac.owner
  AND uc.r_constraint_name = ac.constraint_name
 ORDER BY uc.constraint_name, acc.position;

---