Unique key indices missing

[dmsfiris]

Tables termmeta and postmeta do not update properly using custom "INSERT ... ON DUPLICATE KEY UPDATE" queries. I have found this is because of dublicate entries in those two tables (WordPress version 5.5.1)

This bug can be solved by simply adding unique key indices as follows:

ALTER IGNORE TABLE wp_termmeta ADD UNIQUE (term_id, meta_key);
ALTER IGNORE TABLE wp_postmeta ADD UNIQUE (post_id, meta_key);

[joyously]

Those tables can have duplicate keys, though.

[dmsfiris]

Replying to joyously:

Those tables can have duplicate keys, though.

I'm talking about two-sided unique indices (i.e. both term_id and meta_key).
I don't see why there should be allowed to have entries with the same term_id and meta_key.

[peterwilsoncc]

@dmsfiris Hello and welcome to trac!

As mentioned above, objects (posts, terms, users and comments) can have multiple meta data entries using the same key. Each add_{$object_type}_meta function includes a $unique parameter to indicate whether the meta key is limited to a single entry, the default value is false.

An example use case is the _wp_old_slug meta key/value pair in WordPress posts. If the slug is changed multiple times, all of the old slugs are stored.

I am closing this ticket as wontfix, in this case it's trac's unfriendly term to indicate the db structure is by design.