Why does MySQL Workbench let me add unique indexes to primary keys?

Question

I'm using MySQL Workbench 8.0.12

Reproduce:

1. Create a table
2. Create the first column id, select it as a primary key (PK)
3. Under indexes, PRIMARY appears
4. Back under columns, also select id as unique (UQ)
5. Under indexes, id_UNIQUE appears

Confusion:

1. 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.

2. 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?

3. 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?

Answer 1

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?

I'm not sure we can be so unequivocal. It's difficult to say "always" or "never" about anything. There may be an exception case we haven't thought of.

Say for example you are in the middle of refactoring a table. The primary key used to be email but now you want the primary key to be an auto-increment id. So you add the new column, drop the primary key constraint, and apply primary key to the new column:

ALTER TABLE MyTable
  DROP PRIMARY KEY,
  ADD COLUMN id BIGINT AUTO_INCREMENT, 
  ADD PRIMARY KEY (id);

Seems simple enough, right?

But you still want fast lookups by email as unique lookups, and you still want users to be blocked from using duplicate emails. You also want to test to confirm that queries have good enough performance while using a secondary index as they do using the primary key (clustered index). So before you drop the current primary key, you could create a secondary unique index on the same column and use an index hint to test it:

ALTER TABLE MyTable ADD UNIQUE KEY (email);

SELECT ... FROM MyTable USE INDEX (email) WHERE email = '...';

That's a pretty contrived example, but it's not unbelievable. The point is that edge cases do exist, and the MySQL project does not want their tools to block such edge cases unnecessarily.

Answer 2

Answer compiled from comments by:

Akina:

No reason for to restrict index creation when the statement is syntactically correct. The most common issue - CREATE TABLE t (id SERIAL PRIMARY KEY); which creates both UNIQUE index (due to SERIAL definition) and PRIMARY KEY (due to explicit specifying). So excess UNIQUE key must be removed by an additional statement.

3. To make this more confusing, this (upvoted) answer says:

This question deals with SQL Server and cannot be applied to MySQL. Check such moments carefully.

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?

If you have PK then its expression is always unique, and you do not need in "think about UQ on a column that's already PK". Additionally you may be sure that none column mentioned in PK expression definition is NULL (by default all such columns are defined as NOT NULL implicitly if there is no explicit option in column definition - check SHOW CREATE TABLE for this, fiddle).

---

...and Vérace:

or should I just cement in my memory to never think about UQ on a column that's already PK, and move on?

Yes, move on! Typically a UNIQUE index is for a natural primary key - and your PRIMARY KEY is for a surrogate. Purists aren't too keen on this approach, but it's more or less an industry standard.

There are cases where having a surrogate slows down the system. A prime example is with many-to-many mapping tables where a composite PK is demonstrably better.

Just to add a word - the UNIQUE index for a natural primary key should include a NOT NULL for the relevant fields - one can have a UNIQUE index with NULL fields - because each NULL is neither equal to nor not equal to any other NULL or any other value. Take a look at this fiddle.

Would it be good Workbench were to suggest or enforce "best practice"? If you think so, file it as an enhancement at bugs.mysql.com

Another "suggestion": INDEX(a) is redundant and wasteful if you also have INDEX(a,b).

A contrived use case: SELECT id FROM tbl; would be more efficiently executed by scanning the UNIQUE index's BTree instead of scanning the PRIMARY KEY's BTree. The former contains only id, the latter contains all the columns of the table. (If you tack on a WHERE clause the references any other columns, then the PK's BTree will be the optimal.)