DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: creating a unique constraint without a unique index

  1. #1
    8.1.6 recommends creating unique constraints, and not creating unique indexes.

    What's the syntax for making the unique constraint for say colA, and colB in table TableA?

    thanks

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    is that in the documentation? That's funny because if you create a unique constraint, it will make a corresponding index automatically!

    anyway, the syntax is :

    alter table x
    add constraint y
    unique (column list)

  3. #3
    yes I agree it does... from Oracle docs...

    Oracle recommends that you do not explicitly define UNIQUE indexes on tables (CREATE UNIQUE INDEX). In general, it is better to create constraints to enforce uniqueness than it is to use the CREATE UNIQUE INDEX syntax. A constraint's associated index always assumes the name of the constraint; you cannot specify a specific name for a constraint index.


    But then you can't control where the index sits, the initial/next values, etc.

    thanks.

  4. #4
    Join Date
    Aug 2000
    Posts
    194
    But, you can still use the storage and other physical attributes

    Ex.

    ALTER TABLE my_table
    ADD (CONSTRAINT my_uq_cont UNIQUE ( my_col1, my_col2 )
    USING INDEX TABLESPACE myidxts
    STORAGE ( INITIAL 10K NEXT 10K PCTINCREASE 0) INITRANS 3)

    Note:
    Oracle's recommendation may be not to create the index explicitly. But just to create the UNIQUE constraint whic will automatically create the INDEX anyway. (I can not find the exact reason why they recommend this )

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