Fk Referenced Tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Fk Referenced Tables

  1. #1
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639

    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

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    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

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 08:39 AM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width