Mariadb
如何將特定行的列限制為僅設置特定值？
我有下表
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'有以下數據
https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=036fb3a69c75a92769a9262ec4af292e
我想添加一個約束，以便當
view_id列值等於時catalogrule_rule，mode列必須等於enabled我正在閱讀，這看起來需要一個檢查約束，但我不確定如何創建它，以便所有其他值都有效，但這個特定條件將始終得到尊重。
因此，只有當值為
view_idis時才catalogrule_rule必須將 mode 設置為enabled
CHECK 約束將不允許插入或更新未在模式下啟用的新行，例如我輸入的額外插入。
它不會將其設置為啟用
select 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;| u_1567721770@localhost 的贈款 | | :------------------------------------------------------------------------------------------------------------------ | | 授予使用 *.* 到 `u_1567721770`@`localhost` 由密碼 '*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);`db_1567721770`的約束`mview_state.mode`失敗。`mview_state`select * from mview_state;state_id | view_id | 模式 | 狀態 | 更新 | 版本號 -------: | :------------------------------------ | :------- | :------ | :------------------ | ---------: 1 | design_config_dummy | 啟用 | 閒置 | 2021-06-29 22:30:05 | 0 2 | customer_dummy | 殘疾人| 閒置 | 2021-03-03 12:06:23 | 0 3 | 目錄類別產品 | 啟用 | 閒置 | 2021-06-29 22:30:05 | 461 4 | 目錄產品類別 | 啟用 | 閒置 | 2021-06-29 22:29:03 | 98015 5 | 目錄規則_規則 | 啟用 | 閒置 | 2021-06-29 22:30:05 | 0 6 | 目錄產品屬性 | 啟用 | 閒置 | 2021-06-29 22:30:05 | 2310051 7 | 目錄inventory_stock | 啟用 | 閒置 | 2021-06-29 22:30:05 | 90478 8 | 庫存 | 啟用 | 閒置 | 2021-06-29 22:30:05 | 271476 9 | 目錄規則_產品 | 啟用 | 閒置 | 2021-06-29 22:29:03 | 2505904 10 | 目錄產品價格 | 啟用 | 工作| 2021-06-29 22:30:05 | 56676992 11 | amasty_xsearch_category_fulltext | 啟用 | 閒置 | 2021-06-29 22:29:03 | 54687 12 | 目錄搜尋_全文 | 啟用 | 閒置 | 2021-06-29 22:29:03 | 2547077 33 | amasty_mostviewed_rule_product | 啟用 | 閒置 | 2021-06-29 22:29:03 | 2 34 | amasty_mostviewed_product_rule | 啟用 | 閒置 | 2021-06-29 22:29:03 | 17980739 35 | amasty_sorting_bestseller | 啟用 | 閒置 | 2021-06-29 22:29:03 | 0 36 | amasty_sorting_most_viewed | 啟用 | 閒置 | 2021-06-29 22:29:03 | 0 37 | amasty_sorting_wished | 啟用 | 閒置 | 2021-06-29 22:29:03 | 0 38 | amasty_yotpo_review | 啟用 | 閒置 | 2021-06-29 22:30:16 | 0 43 | amasty_elastic_relevance_rule_rule | 啟用 | 閒置 | 2021-06-29 22:29:03 | 0 44 | amasty_elastic_relevance_rule_product | 啟用 | 閒置 | 2021-06-29 22:29:03 | 18507058 45 | amasty_elastic_popup_data | 啟用 | 閒置 | 2021-06-29 22:29:03 | 0 46 | amasty_xlanding_product_page | 啟用 | 閒置 | 2021-06-29 22:29:03 | 13596598 47 | amasty_xlanding_page_product | 啟用 | 閒置 | 2021-06-29 22:29:03 | 0 48 | Mercer_product_attribute | 啟用 | 閒置 | 2021-06-29 22:29:03 | 3027849db<>在這裡擺弄