creating a unique constraint without a unique index

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

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)

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.

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 )