2

I'm currently utilizing the wp_woocommerce_order_itemmeta table in WordPress (set up similarly to wp_postmeta). This table has four fields:

  • id
  • order_item_id
  • meta_key
  • meta_value

I've got a situation where I need to make sure meta_values for certain meta_keys are unique. E.g. ticket numbers (same ticket number can't be issued twice).

I know it's possible to use multiple-column indexes with unique constraints:

My question is: would adding such a constraint affect performance or have any weird side-effects in WordPress that anyone can think of?

Don Townsend
  • 83
  • 7
  • 1
    **Yes very bad idea** - You should not in any way make that kind of changes in existing database tables that Wordpress / Woocommerce are using. If you set `meta_keys` and/or `meta_values` to be unique in `wp_woocommerce_order_itemmeta`, Woocommerce orders will not work anymore. So you should better create a new custom table where you will be able to do whatever you want. – LoicTheAztec Jun 28 '18 at 23:28
  • 1
    _Woocommerce orders will not work anymore._ Even with a three-column unique constraint? E.g. add a constraint across 'order_item_id', 'meta_key', and 'meta_value'. Although there are some cases where multiple values are desirable for a particular meta_key, and `wc_get_order_item_meta` would get an array of values for a particular meta_key. – Don Townsend Jun 29 '18 at 12:56

1 Answers1

0

"Ticket number" should be its own column, not an "attribute". Then you can apply a uniqueness constraint safely.

Meanwhile, there are multiple inefficiencies in wp_postmeta, as discussed here.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I totally agree that ticket number should be its own column, but one should *not* add additional columns to `wp_postmeta`, as [get_post_meta()](https://developer.wordpress.org/reference/functions/get_post_meta/) would not be able to be used for this additional column. – Don Townsend Jul 02 '18 at 13:50
  • @DonTownsend - Not to `postmeta`; add `ticket_num` to `post`. – Rick James Jul 02 '18 at 14:06