[RESOLVED] Unique key + index key

[jansky]

is it possible to have Unique key at same time Index key on a certain field?

[_newbie]

yeah, should be fine.

[jansky]

the current field key is index key on my certain field when i update that to Unique key it drop the index key

is there a way?

[_newbie]

you need to assign a unique index name identifier for each index (normal and unique), but they can both be attached to the same field. somthing along the lines of:

CREATE INDEX index_name_1 ON table_name (field_name)
CREATE UNIQUE INDEX index_name_2 ON same_table_name(same_field_name)

[jansky]

between is adding unique key will not do harm to index key (same field of course)

[dagon]

a unique is identical to an index key except for the unique restraint, it will be used in any query exactly the same way the unique bit only comers into play on insert, so its very unlikely that you would ever benefit from both being set on the same field.

[jansky]

thanks a lot

after creating unique index key should i repair or optimize the table itself?

[Weedpacket]

Optimise the table so that it generates appropriate statistics for the index. In fact you should be vacuuming it regularly.

[jansky]

thanks a lot