I'm using MySQL Workbench 8.0.12
- Create a table
- Create the first column
id, select it as a primary key (PK)
- Under indexes,
- Back under columns, also select
idas unique (UQ)
- Under indexes,
From reading MySql - Is primary key unique by default?, I understand that the primary key implies uniqueness by default. This is in line with how the guy in this video skips the UQ after checking the PK. Finally, this answer confirms having both PK and UQ on the same column is redundant:
If you declare a column as UNIQUE as well as PRIMARY KEY, MySQL (Workbench?) oddly creates a second index over the same columns. This is truly redundant and only takes up extra storage space and causes a bit of overhead when you insert/update/delete rows. It doesn't give any benefit.
If this truly is detrimental for performance, and from my previous experience with MySQL Workbench double-checking and cross-referencing whatever I had been doing, I would imagine the developers should have made it fool-proof by now to not let users select UQ on top of PK. For example, communicate by disabling the UQ checkbox on a column already having the PK selected. Since they didn't, is it because there could be a scenario where someone may actually need both PK and UQ on the same column?
To make this more confusing, this (upvoted) answer says:
Use UNIQUE CONSTRAINT to state a fact. Use UNIQUE INDEX when you have an index which happens to be unique, for instance because you add the primary key to it.
Which implies that these are two independent states. However, that contradicts the MySQL Workbench behaviour, where checking UQ on a column will automatically create an index, and if you go ahead and delete that index, it automatically deselects the UQ.
So could there be edge cases, or should I just cement in my memory to never think about UQ on a column that's already PK, and move on?