-
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
-
Re: Fk Referenced Tables
Somthing like this:
select table_name from user_constraints where constraint_name in
(select r_constraint_name from user_constraints where constraint_type='R')
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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);
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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.
Last edited by jmodic; 06-16-2004 at 07:39 AM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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 :-)
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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.
-
Originally posted by julian
Although I have never seen that in a real database :-)
We do have lots such..
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
We do have lots such..
It's a big world :-)
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|