Results 1 to 11 of 11

Thread: Noob Question

  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Unanswered: Noob Question [RESOLVED - THANKS]

    Relative newcomer to Oracle. Where does Oracle store information about the Primary Keys for its tables? Specifically, how can I determine, by table, what column(s) are used for the Primary key?

    Any assistance is sincerely appreciated.
    Last edited by doofusboy; 07-10-03 at 13:48.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Look in the system view 'user constraints'. ie

    select * from user_constraints where table_name = 'xxxxx'

    etc.

    Hth
    Bill

  3. #3
    Join Date
    Jul 2003
    Posts
    24
    Thanks Bill, but already looked there. It doesn't tell me what column(s) in the table make up the Primary Key. Here's the table def:

    SQL> desc user_constraints
    Name Null? Type
    ----------------------------------------- -------- --------------
    OWNER NOT NULL VARCHAR2(30)
    CONSTRAINT_NAME NOT NULL VARCHAR2(30)
    CONSTRAINT_TYPE VARCHAR2(1)
    TABLE_NAME NOT NULL VARCHAR2(30)
    SEARCH_CONDITION LONG
    R_OWNER VARCHAR2(30)
    R_CONSTRAINT_NAME VARCHAR2(30)
    DELETE_RULE VARCHAR2(9)
    STATUS VARCHAR2(8)
    DEFERRABLE VARCHAR2(14)
    DEFERRED VARCHAR2(9)
    VALIDATED VARCHAR2(13)
    GENERATED VARCHAR2(14)
    BAD VARCHAR2(3)
    RELY VARCHAR2(4)
    LAST_CHANGE DATE

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try USER_CONS_COLUMNS. The best way to learn abouth these tables is get something like TOAD (free version available) and just browse the views belong to sys.

    Alan

  5. #5
    Join Date
    Jul 2003
    Posts
    24

    Talking

    THANKS AlanP !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Helps me a lot; exactly what I was looking for.

    Anywhere I can learn the names of these sys tables???? I know a few but never the one I need it seems. Have Toad tool......will it tell me the name of these sys tables?

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Just use schema browser and select the SYS user and the views tab

    Alan

  7. #7
    Join Date
    Jul 2003
    Posts
    24
    Sorry to keep bothering ya.....but how do I select SYS user?

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    top left hand corner, the drop down combo box.

    Alan

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    yo, goto quest.com and download TOAD for free.

    It is a great GUI tool to look up constraints and many other things.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Jul 2003
    Posts
    24

    Talking

    Originally posted by AlanP
    top left hand corner, the drop down combo box.

    Alan

    Thanks again AlanP........that last assist will be INVALUABLE!!!!!!


    ....first day here and I like this site already!

  11. #11
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    You can also check the system view all_objects etc.

    select * from all_objects where owner = 'SYS'

    select * from dba_all_objects where owner = 'SYS'

    Hth
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •