0

This line in my plugin:

$wpdb->query("ALTER TABLE {$wpdb->prefix}postmeta ADD CONSTRAINT postmeta UNIQUE (post_id,meta_key(100))");

gives me the following error:

[Duplicate entry '539-_wp_attachment_image_alt' for key 'postmeta']

I looked for the entry in my database and saw several entries where post_id is 539 and meta_key is _wp_attachment_image_alt, as well as several others with duplicate post_ids and image alts.

My question is this: are duplicate _wp_attachment_image_alt meta keys allowed in WordPress? I am trying to understand whether there was an error on this particular site at some point that duplicated the entries, or if it is a normal behaviour.

I know duplicate meta_keys in general can be inserted into the postmeta table, and I've also got a feeling I should not add and rely on a post_id+meta_key unique key since duplicate meta_keys are allowed in WordPress, right?

Improve this question
12
  • Duplicate entries for attachment image alt text is unusual, but duplicate meta keys is absolutely not unusual, and in some cases expected or recommended. There's nothing to gain from adding this constraint.
    – Jacob Peattie
    Feb 9, 2022 at 10:31
  • I use it to make sure a third-party program that connects to my plugin does not create duplicate entries every time it exports data to website, which happens several times a day.
    – Artem
    Feb 9, 2022 at 10:49
  • Well you’ll break lots of things. WordPress is designed to support multiple meta keys. There’s dedicated functions for it and everything. If you have data that cannot have duplicate values then that’s something you need to enforce in the plugin that creates the data.
    – Jacob Peattie
    Feb 9, 2022 at 11:17
  • @Artem so this is an X Y problem? You should ask about your original problem, not how to fix your solution, you should do this at the plugins support routes, what you're trying to do here breaks a fundamental part of WordPress and will cause damage, it can never be the solution to your problem with the plugin.
    – Tom J Nowell ♦
    Feb 9, 2022 at 11:20
  • 1
    @SallyCJ yes, true! I've seen people try to reinvent a wheel in a mysql way where it wasn't really needed. I do use native functions where possible, otherwise why WordPress ) , and raw MySQL turned out to be handy to deal with 1C:Enterprise, a business program that sends xml files over http. As for my question, it turned out there were two to five (old) copies of every meta key in my database, I preferred to use this solution (in case someone with similar issue comes across this question)
    – Artem
    Feb 10, 2022 at 2:02

1 Answer 1

Reset to default
0

Meta keys are not unique, a post can have multiple meta with the same key by design. Additionally, you should not be modifying WP core tables in your plugin, this is bad practice and can lead to data loss on WP updates.

In particular, the various meta APIs have a 3rd parameter:

get_post_meta( int $post_id, string $key = '', bool $single = false )

If $single is set to false then an array of all key/value pairs is returned. This way you can store multiple values without resorting to serialised arrays. This is also why there are separate update_post_meta and add_post_meta functions.

The same is true of term/comment/user/site meta etc

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.