Rhino wrote:
> "aj" <ronald@mcdonalds.com> wrote in message
> news:11muu256uhhg3f9@news.supernews.com...
>> DB2 WSE 8.1 FP5
>> Red Hat AS 2.1
>>
>> What is the difference between adding a unique constraint like:
>>
>> ALTER TABLE <SCHEMA>.<TABLE> ADD CONSTRAINT CC1131378283225 UNIQUE (
>> <COL1>) ;
>>
>> and adding a unique index like:
>>
>> CREATE UNIQUE INDEX <SCHEMA>.<BLAH> ON <SCHEMA>.<TABLE> (<COL1> ASC)
>> PCTFREE 10 MINPCTUSED 10;
>>
>> Just curious.
>>
>
> They are two sides of the same coin, really. A unique constraint is a rule
> in the database that this column needs to be kept unique (i.e. no duplicate
> values in the column) while a unique index is the way that uniqueness is
> enforced. Whenever you define a column as unique (or as a primary key), you
> will be forced to create a unique index before you can use the table. DB2
> then enforces the uniqueness in the column via the index.

Actually you are not forced into doing anything. If you add a unique
constraint to a table, DB2 will automatically create a unique index if
one does not already exist. (The same way it will automatically create
an index if you alter a table to add a primary key).


Technically, there is no logical difference between the two options
given by the OP. However, there are physical differences:

#1 causes DB2 to automatically create an index (named
<SCHEMA>.<SQL....>) to enforce the constraint

#2 will not show that the table has a unique constraint, even though
the index exists and enforces uniqueness.