MySQL conditional Insert for checking Single-value and Multi-value keys-CodePudding

I have a MySQL table with Columns (id, key, value). Keys are predefined strings. As an example key can be color, height, width, etc,...values can be single-valued or multi-valued.

Ex:-

height and width are single value keys
color is a multi-value key (For an object there can be multiple colors)

values for multi-value keys are stored in the database as a single string (with comma separated values)

My Problem is How can I enforce single value keys to be single-valued while inserting.

Ex:-

For the height key and width key value must be single-valued
For the color key value can be multi-valued

How Can I Enforce this condition while inserting? What will be the best way?

Edit :

Single valued attributes can be String ,Int Or Boolean.
Multi Valued attributes are Strings

CodePudding user response：

To enforce this condition while inserting you can create BEFORE INSERT trigger and make your checks there. As far I understand your logic for "multi-valued" keys you can check whether they are in the database table and for "single-valued" you can check whether the value is a number.

CodePudding user response：

I suppose you can add a CHECK constraint. A minimal example:

create table t(
  id int,
  `key` varchar(100),
  `value` varchar(100),
  check (case
      when `key` in ('width', 'height') then `value` not like '%,%'
  end = 1)
)

It is also possible to use REGEXP clause to check if numbers are in fact numbers.
DB<>Fiddle

Another improvement would be to store the values a JSON (boolean, string, number or array) then use json_type to check the type of value being saved.