chee hee wrote:
My company's DBA has given me a table creation script. In the script, he has primary key and unique index on the same fields. For example, we have something like this:
ALTER TABLE config ADD CONSTRAINT pk_config
PRIMARY KEY (config_item, config_value);
CREATE UNIQUE INDEX ui_config ON config (config_item, config_value);
Is it necessary to have the unique index created since we already have PK on those columns?
Helen Borrie answers:
Worse, it is just a bad error to have two identical indexes because it raises the risk that the optimizer will not use EITHER of them. Drop the user index.
It is fine to have an index on the same keys if it is not an identical index. For example, the following would be OK (if you had a need for it) and it should not be defined as UNIQUE since the PK index already enforces the uniqueness of the same key pair:
CREATE INDEX ui_config ON config (config_item, config_value DESC);