I'm learning indexing and I was messing around and saw that I could create exactly the same index for the same column by just changing the names:
CREATE UNIQUE INDEX idx_employees_device_serial ON employees(device_serial);
CREATE UNIQUE INDEX idx_employees_device_serial_2 ON employees(device_serial);
Is there a way to prevent this from happening? As this has 0 logical reason to be possible.
Bonus question: Is there a reason to add a manual UNIQUE INDEX to a column that already has a UNIQUE constraint? Thanks!
First of all, you are right, you should avoid having multiple INDEX referring to the same columns, as it will lower your performances and add nothing usefull.
If you have the same INDEX twice with two different names, most of DBMS (maybe all) will let you create anything asked, since the INDEX name is the key making it unique.
BUT your code should not be managing your database structure, so there is no reason for indexes to change, except for very specific needs. All you have to do is create the right indexes when you setup your database (or let your ORM do the initialization for you).
Bonus response : UNIQUE constraint will let you end up with an INDEX on your column. You will have both, the column unicity and the search performances of the INDEX.