how do I restrict a specific row's columns to have only a specific value set?

Question

I have the following table

CREATE TABLE `mview_state` (
 `state_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'View State ID',
 `view_id` varchar(255) DEFAULT NULL COMMENT 'View ID',
 `mode` varchar(16) DEFAULT 'disabled' COMMENT 'View Mode',
 `status` varchar(16) DEFAULT 'idle' COMMENT 'View Status',
 `updated` datetime DEFAULT NULL COMMENT 'View updated time',
 `version_id` int(10) unsigned DEFAULT NULL COMMENT 'View Version ID',
 PRIMARY KEY (`state_id`),
 KEY `MVIEW_STATE_VIEW_ID` (`view_id`),
 KEY `MVIEW_STATE_MODE` (`mode`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 COMMENT='View State'

With the following data

https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=036fb3a69c75a92769a9262ec4af292e

I would like to add a constraint so that when the view_id column value is equal to catalogrule_rule the mode column must be equal to enabled

I'm reading and this looks like it would require a check constraint but I'm not sure how to create it so that all other values are valid but this specific condition will always be honored.

So only if the value for view_id is catalogrule_rule then mode must be set to enabled

Answer 1

A CHECK Constraint would you not allow to insert or update new rows that have not enabled in mode like the extra insert that i entered.

It would not set it to enabled

select version();

| version() |
| :-------------- |
| 10.5.11-MariaDB |

create table mview_state
(
 state_id int unsigned auto_increment comment 'View State ID'
 primary key,
 view_id varchar(255) null comment 'View ID',
 mode varchar(16) default 'disabled' null comment 'View Mode' 
 CHECK ((mode = 'enabled' AND view_id = 'catalogrule_rule') OR view_id != 'catalogrule_rule'),
 status varchar(16) default 'idle' null comment 'View Status',
 updated datetime null comment 'View updated time',
 version_id int unsigned null comment 'View Version ID'
)
comment 'View State';
create index MVIEW_STATE_MODE
 on mview_state (mode);
create index MVIEW_STATE_VIEW_ID
 on mview_state (view_id);
 

SHOW GRANTS;

| Grants for u_1567721770@localhost |
| :------------------------------------------------------------------------------------------------------------------ |
| GRANT USAGE ON *.* TO `u_1567721770`@`localhost` IDENTIFIED BY PASSWORD '*2E2C82B9AF9ED55252E4DFF4F775075C1D89A8BF' |

INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (1, 'design_config_dummy', 'enabled', 'idle', '2021-06-29 22:30:05', 0);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (2, 'customer_dummy', 'disabled', 'idle', '2021-03-03 12:06:23', 0);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (3, 'catalog_category_product', 'enabled', 'idle', '2021-06-29 22:30:05', 461);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (4, 'catalog_product_category', 'enabled', 'idle', '2021-06-29 22:29:03', 98015);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (5, 'catalogrule_rule', 'enabled', 'idle', '2021-06-29 22:30:05', 0);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (6, 'catalog_product_attribute', 'enabled', 'idle', '2021-06-29 22:30:05', 2310051);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (7, 'cataloginventory_stock', 'enabled', 'idle', '2021-06-29 22:30:05', 90478);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (8, 'inventory', 'enabled', 'idle', '2021-06-29 22:30:05', 271476);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (9, 'catalogrule_product', 'enabled', 'idle', '2021-06-29 22:29:03', 2505904);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (10, 'catalog_product_price', 'enabled', 'working', '2021-06-29 22:30:05', 56676992);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (11, 'amasty_xsearch_category_fulltext', 'enabled', 'idle', '2021-06-29 22:29:03', 54687);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (12, 'catalogsearch_fulltext', 'enabled', 'idle', '2021-06-29 22:29:03', 2547077);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (33, 'amasty_mostviewed_rule_product', 'enabled', 'idle', '2021-06-29 22:29:03', 2);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (34, 'amasty_mostviewed_product_rule', 'enabled', 'idle', '2021-06-29 22:29:03', 17980739);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (35, 'amasty_sorting_bestseller', 'enabled', 'idle', '2021-06-29 22:29:03', 0);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (36, 'amasty_sorting_most_viewed', 'enabled', 'idle', '2021-06-29 22:29:03', 0);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (37, 'amasty_sorting_wished', 'enabled', 'idle', '2021-06-29 22:29:03', 0);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (38, 'amasty_yotpo_review', 'enabled', 'idle', '2021-06-29 22:30:16', 0);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (43, 'amasty_elastic_relevance_rule_rule', 'enabled', 'idle', '2021-06-29 22:29:03', 0);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (44, 'amasty_elastic_relevance_rule_product', 'enabled', 'idle', '2021-06-29 22:29:03', 18507058);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (45, 'amasty_elastic_popup_data', 'enabled', 'idle', '2021-06-29 22:29:03', 0);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (46, 'amasty_xlanding_product_page', 'enabled', 'idle', '2021-06-29 22:29:03', 13596598);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (47, 'amasty_xlanding_page_product', 'enabled', 'idle', '2021-06-29 22:29:03', 0);
INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (48, 'merchandiser_product_attribute', 'enabled', 'idle', '2021-06-29 22:29:03', 3027849);

INSERT INTO mview_state (state_id, view_id, mode, status, updated, version_id) VALUES (5, 'catalogrule_rule', 'disabled', 'idle', '2021-06-29 22:30:05', 0);

CONSTRAINT `mview_state.mode` failed for `db_1567721770`.`mview_state`

select * from mview_state;

state_id | view_id | mode | status | updated | version_id
-------: | :------------------------------------ | :------- | :------ | :------------------ | ---------:
 1 | design_config_dummy | enabled | idle | 2021年06月29日 22:30:05 | 0
 2 | customer_dummy | disabled | idle | 2021年03月03日 12:06:23 | 0
 3 | catalog_category_product | enabled | idle | 2021年06月29日 22:30:05 | 461
 4 | catalog_product_category | enabled | idle | 2021年06月29日 22:29:03 | 98015
 5 | catalogrule_rule | enabled | idle | 2021年06月29日 22:30:05 | 0
 6 | catalog_product_attribute | enabled | idle | 2021年06月29日 22:30:05 | 2310051
 7 | cataloginventory_stock | enabled | idle | 2021年06月29日 22:30:05 | 90478
 8 | inventory | enabled | idle | 2021年06月29日 22:30:05 | 271476
 9 | catalogrule_product | enabled | idle | 2021年06月29日 22:29:03 | 2505904
 10 | catalog_product_price | enabled | working | 2021年06月29日 22:30:05 | 56676992
 11 | amasty_xsearch_category_fulltext | enabled | idle | 2021年06月29日 22:29:03 | 54687
 12 | catalogsearch_fulltext | enabled | idle | 2021年06月29日 22:29:03 | 2547077
 33 | amasty_mostviewed_rule_product | enabled | idle | 2021年06月29日 22:29:03 | 2
 34 | amasty_mostviewed_product_rule | enabled | idle | 2021年06月29日 22:29:03 | 17980739
 35 | amasty_sorting_bestseller | enabled | idle | 2021年06月29日 22:29:03 | 0
 36 | amasty_sorting_most_viewed | enabled | idle | 2021年06月29日 22:29:03 | 0
 37 | amasty_sorting_wished | enabled | idle | 2021年06月29日 22:29:03 | 0
 38 | amasty_yotpo_review | enabled | idle | 2021年06月29日 22:30:16 | 0
 43 | amasty_elastic_relevance_rule_rule | enabled | idle | 2021年06月29日 22:29:03 | 0
 44 | amasty_elastic_relevance_rule_product | enabled | idle | 2021年06月29日 22:29:03 | 18507058
 45 | amasty_elastic_popup_data | enabled | idle | 2021年06月29日 22:29:03 | 0
 46 | amasty_xlanding_product_page | enabled | idle | 2021年06月29日 22:29:03 | 13596598
 47 | amasty_xlanding_page_product | enabled | idle | 2021年06月29日 22:29:03 | 0
 48 | merchandiser_product_attribute | enabled | idle | 2021年06月29日 22:29:03 | 3027849

db<>fiddle here

• gabtzi: Ah it was so simple. Thank you very much. The example was doing it with a function on the docs that's why I was a bit confused.
• Vérace: @gabtzi you might want to give a meaningful name to the constraint? dbfiddle.uk/…
• Andriy M: The mode column is defined as nullable and your constraint allows inserting 'catalogrule_rule' with a null mode, see here. Perhaps some of these columns, including view_id and mode, simply shouldn't be nullable, and if they weren't, your constraint would work (in which case you could actually simplify it to mode = 'enabled' OR view_id <> 'catalogrule_rule'). However, since they are nullable and the requirement is to allow only 'enabled', the constraint probably needs to be able to prevent the nulls.
• gabtzi: In my case I only needed to ensure that the 'catalogrule_rule' column will always have an enabled mode and the app makes sure it's not null so the above worked but I see your point. It's good to know