Rhino wrote:
"aj" <ro****@mcdonalds.com> wrote in message
news:11*************@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.