Difference between unique constraint and unique index?

[aj]

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>.<BLA H> ON <SCHEMA>.<TABLE > (<COL1> ASC)
PCTFREE 10 MINPCTUSED 10;

Just curious.

TIA

aj

[Rhino]

Re: Difference between unique constraint and unique index?


"aj" <ronald@mcdonal ds.com> wrote in message
news:11muu256uh hg3f9@news.supe rnews.com...[color=blue]
> 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>.<BLA H> ON <SCHEMA>.<TABLE > (<COL1> ASC)
> PCTFREE 10 MINPCTUSED 10;
>
> Just curious.
>[/color]

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.

Rhino

[Ian]

Re: Difference between unique constraint and unique index?

Rhino wrote:[color=blue]
> "aj" <ronald@mcdonal ds.com> wrote in message
> news:11muu256uh hg3f9@news.supe rnews.com...[color=green]
>> 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>.<BLA H> ON <SCHEMA>.<TABLE > (<COL1> ASC)
>> PCTFREE 10 MINPCTUSED 10;
>>
>> Just curious.
>>[/color]
>
> 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.[/color]

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.

[_link98@yahoo.com]

Re: Difference between unique constraint and unique index?


aj wrote:[color=blue]
> 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>.<BLA H> ON <SCHEMA>.<TABLE > (<COL1> ASC)
> PCTFREE 10 MINPCTUSED 10;
>
> Just curious.
>
> TIA
>
> aj[/color]

In addition to other replies, there's another difference between the
two.
DB2 allows a "unique index" to contain a single "null" value. So DB2
lets you create a "unique index" on a nullable column.

However, in contrast, if you have a column that has a "unique
constraint" then DB2 forces that column to be "not-null". Db2 requires
any column in either a primary-key or a unique-key to be mandatory (not
null).

[Knut Stolze]

Re: Difference between unique constraint and unique index?

aj wrote:
[color=blue]
> DB2 WSE 8.1 FP5
> Red Hat AS 2.1
>
> What is the difference between adding a unique constraint like:[/color]

A unique index is a physical thing whereas a unique constraint is a data
modeling construct. As was already stated, unique constraint are
implemented by adding a unique index (and additionally requiring the NOT
NULL condition).

But you should also be aware that referential integrity (foreign key
constraints) can only reference unique constraints (or primary keys as a
special case for unique constraints). A foreign key _cannot_ reference a
unique index because it is just that: an index and not a constraint.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Rhino]

Re: Difference between unique constraint and unique index?


"Ian" <ianbjor@mobile audio.com> wrote in message
news:43706ccf$1 _4@newsfeed.slu rp.net...[color=blue]
> Rhino wrote:[color=green]
> > "aj" <ronald@mcdonal ds.com> wrote in message
> > news:11muu256uh hg3f9@news.supe rnews.com...[color=darkred]
> >> 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>.<BLA H> ON <SCHEMA>.<TABLE > (<COL1> ASC)
> >> PCTFREE 10 MINPCTUSED 10;
> >>
> >> Just curious.
> >>[/color]
> >
> > They are two sides of the same coin, really. A unique constraint is a[/color][/color]
rule[color=blue][color=green]
> > in the database that this column needs to be kept unique (i.e. no[/color][/color]
duplicate[color=blue][color=green]
> > 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),[/color][/color]
you[color=blue][color=green]
> > will be forced to create a unique index before you can use the table.[/color][/color]
DB2[color=blue][color=green]
> > then enforces the uniqueness in the column via the index.[/color]
>
> 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).
>[/color]
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.[color=blue]
>
> 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.
>[/color]
Rhino