setup:upgrade SQL error after update from 2.2.8 to 2.3.1
Apparently it is a Magento bug. When comparing current schema to declared schema, constraints and other indexes are handled separately, but the existing index is not dropped before the new UNIQUE
index is created.
A workaround in this case is to drop or¹ rename the existing index in a separate script before performing the update.
alter table wishlist rename index WISHLIST_CUSTOMER_ID to WISHLIST_CUSTOMER_ID_DELETE_ME;
Not quite satisfying but does the job, as long as there's no solution for the core bug.
Update: what actually triggered the bug was that the magento/module-multiple-wishlist
module from Commerce/Enterprise Edition had been disabled. Re-enabling it also "solved" the issue.
¹) dropping it was not possible without also removing the foreign key that depends on it