Making this an issue I have been asking the pyroCMS men because while searching through there source I observed this news_groups table for that news module has 2 indexes around the "slug" column .
There's no reason in developing a non-unique index and it'll only degrade write performance up for grabs (since two identical indexes will have to be maintained).
It's interesting that
Oracle reacts more consistently such situations.
Should you instruct
Oracle to produce a plain index on the column already indexed with
UNIQUE index (or the other way around), it'll throw a mistake.
However, should you produce a plain index after which produce a
UNIQUE CONSTRAINT (using
ALTER TABLE ADD CONSTRAINT), it'll produce the constraint and can make use of the existing plain index to enforce it.
No, it does not seem sensible to possess 2 indexes on a single column.