This discussion is archived
6 Replies Latest reply: Jan 3, 2013 9:07 PM by 652398 RSS

A sentence meaning in Oracle Concept

652398 Newbie
Currently Being Moderated
Oracle Concept:
Page 5-22:

"Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index."

I cannot catch it very clear. Especially "a new index" and "the index they create" stand for what? Could someone explain it? Thanks.
  • 1. Re: A sentence meaning in Oracle Concept
    ranit B Expert
    Currently Being Moderated
    >
    "Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index."
    Few points+ -

    [1]
    Indexes are automatically created on a column when that is defined as a Primary Key column. As you might be knowing, Primary key column values are unique (in other words - Distinct )

    Same can also be achieved by - creating a Unique Index using the Unique_ keyword while creating Index.

    [2]
    But, there's an important thing to note -
    The column where you are trying to put a Unique Index or a Primary Key column, it should have all Unique+ (or Distinct+ ) values.

    Else, it'll throw error.

    Check this workout :
    SQL> create table test_unique as
      2  select 1 x1, 1 x2 from dual UNION ALL
      3  select 2 x1, 1 x2 from dual UNION ALL
      4  select 3 x1, 1 x2 from dual UNION ALL
      5  select 4 x1, 1 x2 from dual UNION ALL
      6  select 5 x1, 1 x2 from dual;
    
    Table created.
    
    -- "Primary Key" 
    SQL> alter table test_unique
      2  add
      3  primary key(x1);
    
    Table altered.
    
    -- "UNIQUE Constraint" 
    SQL> alter table test_unique
      2  add
      3  constraint x2_unq unique(x2);
    constraint x2_unq unique(x2)
               *
    ERROR at line 3:
    ORA-02299: cannot validate (QUALITY.X2_UNQ) - duplicate keys found 
    
    -- "UNIQUE Index" 
    SQL> create UNIQUE index
      2  idx_x2
      3  on test_unique(x2);
    on test_unique(x2)
       *
    ERROR at line 3:
    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found 
    Hope this Helps.
  • 2. Re: A sentence meaning in Oracle Concept
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    bill wrote:
    Oracle Concept:
    Page 5-22:

    "Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index."

    I cannot catch it very clear. Especially "a new index" and "the index they create" stand for what? Could someone explain it? Thanks.
    A primary key or unique constraint needs an index to support the uniqueness test however, provided it starts with the correct columns, that index doesn't have to unique. In trivial (and the most common) cases, Oracle will probably create an exact matching unique index as you add the constraint to the table - but if the constraint is deferrable the index will be created as non-unique, and (deferrable constraint or not) if a suitable index already exists (whether or not it's unique, and whether or not it's an exact match) Oracle may simply associate that index with the new constraint.

    Regards
    Jonathan Lewis
  • 3. Re: A sentence meaning in Oracle Concept
    Richard Foote Employee ACE
    Currently Being Moderated
    Hi Ranit

    Of course, it's possible for an index to not be automatically created (if an index with suitable leading columns already exists), for such an index to be non-unique and for the column to contain duplicate values (for example if the constraint is not validated or is deferrable):
    SQL> create table test_unique as
      2  select 1 x1, 1 x2 from dual UNION ALL
      3  select 2 x1, 1 x2 from dual UNION ALL
      4  select 3 x1, 1 x2 from dual UNION ALL
      5  select 4 x1, 1 x2 from dual UNION ALL
      6  select 5 x1, 1 x2 from dual;
    
    Table created.
    
    SQL> create index x2_not_unique on test_unique(x2);
    
    Index created.
    
    SQL> alter table test_unique add primary key(x2) novalidate;
    
    Table altered.
    
    SQL> select table_name, constraint_type, index_name from user_constraints where table_name='TEST_UNIQUE';
    
    TABLE_NAME                     C INDEX_NAME
    ------------------------------ - ------------------------------
    TEST_UNIQUE                    P X2_NOT_UNIQUE
    
    SQL> select x2 from test_unique;
    
            X2
    ----------
             1
             1
             1
             1
             1
    Hopefully, this will help Bill somewhat with his original question.

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 4. Re: A sentence meaning in Oracle Concept
    652398 Newbie
    Currently Being Moderated
    Thanks.
  • 5. Re: A sentence meaning in Oracle Concept
    652398 Newbie
    Currently Being Moderated
    Thanks.
  • 6. Re: A sentence meaning in Oracle Concept
    652398 Newbie
    Currently Being Moderated
    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points