Skip to content
June 3, 2011 / doganay

HOW TO CREATE NON UNIQUE INDEX WHILE CREATING UNIQUE CONSTRAINT

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Hi,

If you want to ensure that duplicate values will not exist in a column, then after updating your data and

eliminating duplicates of course, you can add a unique contraint.

But, if you use this column in where clauses very often, you may want to create an index for it.

But if your table is partitioned and your column is not in the partition key list,

then one clever thing left that you can do:

create a non-unique index while creating your unique constraint:

ALTER TABLE mytable
ADD UNIQUE (field1, field2, ...) -- or just 1 field, it's up to you..
USING INDEX;

tray and pray 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: