Rarely does an app need the constraint about an
UNIQUE. Uniqueness is not guaranteed.
Sometimes it is best to turn
PRIMARY KEY(id), -- (id is auto_increment)
PRIMARY KEY(foo, id),
The purpose is to cluster the rows based on
foo because most of the access is done with
WHERE foo = constant.
INDEX(id) is all that
AUTO_INCREMENT requires. It does not require uniqueness; it does not require
id to the PK on the assumption that
foo is not, itself, unique. The combination
foo, id is unique (assuming you are not playing games with
id), thereby satisfying the uniqueness requirement of
Back to your question -- Sure, go ahead and add the id. It will require
INDEX(id) or INDEX(id, ...)
Implementation insight... When a table is opened, the auto_inc value is initialized by doing
SELECT MAX(id) FROM tbl. If that table is partitioned, the code already knows how to check
MAX(id) from each partition; then it can easily get the overall
MySQL 8.0 has a slight variation on that -- it saves the auto_inc value somewhere else so that after a server restart, you will not reuse any ids that got lost in what might have been a crash that necessitated rollbacks.
DELETEing the last id just before a graceful shutdown is another issue covered.
BTW -- See if you can use the above trick instead of partitioning. I have found very few valid cases for using partitioning. Provide more details on your case; I can probably spot whether it will help or not.