Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Post Unanswered: Simple SQL Explanations Needed

    SQL> CREATE TABLE EMP1(ENO NUMBER(3),EID NUMBER(3),
    2 ENAME VARCHAR2(20), CONSTRAINT MYCON PRIMARY KEY (ENO,EID));

    Table created.

    SQL> DESC EMP1
    Name Null? Type
    ----------------------------------------- -------- -----------------------
    ENO NOT NULL NUMBER(3)
    EID NOT NULL NUMBER(3)
    ENAME VARCHAR2(20)

    SQL> SPOOL OFF

    QUESTIONS RELATED TO THE ABOVE WORK

    1) How can we access the keys present in the table for each column seperately?
    2) What is the identifier / Character that identifies that the column contains the key
    of which type?

    Thanks in advance for your kind help

    Balamrugan

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It seems that your table actually has two keys: one called ENO and one called EID. Why would you want both?

    But your PRIMARY KEY definition combines the two columns into a single key (ENO,EID) which means that the following data would be allowed:
    Code:
    ENO EID
    --- ---
      1   1
      1   2
      1   3
      2   1
      2   2
      2   3
    If you really need two keys, then only one of them can be the primary key, the other can only be a UNIQUE constraint:
    Code:
    CREATE TABLE EMP1
    (ENO NUMBER(3)
    ,EID NUMBER(3) NOT NULL
    ,ENAME VARCHAR2(20)
    ,CONSTRAINT EMP1_PK PRIMARY KEY (ENO)
    ,CONSTRAINT EMP1_UK UNIQUE (EID)
    );
    Note that I have added NOT NULL to EID since UNIQUE constraints do not prevent nulls (as primary keys do).

    I don't really understand either of your questions, but I suspect they may be based on a misunderstanding of keys that I hope to have cleared up somewhat.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As of your second question: are you trying to find out constraints on the table, their type (primary key, referential, ...) and columns involved? If so, you might try something like this:
    Code:
    select c.constraint_name, c.constraint_type, f.column_name
    from user_constraints c, user_cons_columns f
    where f.table_name = c.table_name
      and c.table_name = '&table_name'
      and f.constraint_name = c.constraint_name
    order by c.constraint_name, f.position;
    Constraint types are abbreviated, where P = primary key, U = unique key, R = referential constraint, C = check constraint.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Or if you only want to list the "indexes" (keys):
    Code:
    Select C.Table_Name, C.Constraint_Name Key_Name
         , Decode(Constraint_Type,'P', 'Primary Key', 'Unique Key') Key_Type
         , Position
         , Column_Name
      From User_Constraints C, User_Cons_Columns F
     Where C.Constraint_Type In ('P','U')
       And F.Table_Name = C.Table_Name
       And F.Constraint_Name = C.Constraint_Name
    Union
    Select I.Table_Name, I.Index_Name
         , 'Index'
         , Column_Position
         , K.Column_Name
      From User_Indexes I, User_Ind_Columns K
     Where K.Table_Name = I.Table_Name
       And K.Index_Name = I.Index_Name
       And I.Index_Name Not In (
           Select Constraint_Name From User_Constraints C
            Where C.Table_Name = I.Table_Name)
     Order by 1, 2, 4
    /

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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