"Ian" <ianbjor@mobileaudio.com> wrote in message
news:43706ccf$1_4@newsfeed.slurp.net...
> 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).
>
You're absolutely right for DB2 on Windows/Linux/Unix, which is what the
original poster is using.

On some platforms, like z/OS, DB2 doesn't automatically create indexes for
you so DB2 (on those platforms) refuses to let you use the data until you
manually create the indexes that support the unique constraint. That's why I
answered the way I did. But I shouldn't have muddied the waters by adding
that factor into the answer; the original poster asked specifically about
his platform and you are right: the indexes get created automatically on DB2
running on Linux platforms.
>
> 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.
>
Rhino