主页 / dba / 问题 / 295021
Accepted
gabtzi
Asked: 2021-06-30 14:43:35 +0800 CST

如何将特定行的列限制为仅设置特定值？

  • 772

我有下表

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

mariadb check-constraints

1 个回答

  1. Best Answer

    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 | 3027849

    db<>在这里摆弄

    • 1

相关问题