Início / dba / Perguntas / 295021
Accepted
gabtzi
Asked: 2021-06-30 14:43:35 +0800 CST

como faço para restringir as colunas de uma linha específica para ter apenas um valor específico definido?

  • 772

tenho a seguinte tabela

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'

Com os seguintes dados

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

Eu gostaria de adicionar uma restrição para que quando o view_idvalor da coluna for igual catalogrule_rulea modecoluna deve ser igual aenabled

Estou lendo e parece que exigiria uma restrição de verificação, mas não tenho certeza de como criá-la para que todos os outros valores sejam válidos, mas essa condição específica sempre será respeitada.

Portanto, somente se o valor view_idfor catalogrule_ruleentão o modo deve ser definido comoenabled

mariadb check-constraints

1 respostas

  1. Best Answer

    Uma restrição CHECK não permitiria inserir ou atualizar novas linhas que não foram habilitadas no modo como a inserção extra que eu inseri.

    Não definiria como ativado

    select version();

    | versão() |
| :-------------- |
| 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;

    | Subsídios para u_1567721770@localhost |
| :------------------------------------------------- -------------------------------------------------- --------------- |
| CONCEDER USO EM *.* PARA `u_1567721770`@`localhost` IDENTIFICADO PELA SENHA '*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` falhou para `db_1567721770`.`mview_state`

    select * from mview_state;

    state_id | view_id | modo | estado | atualizado | version_id
-------: | :--------------------------------------------------- | :------- | :------ | :------------------ | ---------:
       1 | design_config_dummy | habilitado | inativo | 2021-06-29 22:30:05 | 0
       2 | cliente_dummy | desativado | inativo | 03-03-2021 12:06:23 | 0
       3 | catalog_category_product | habilitado | inativo | 2021-06-29 22:30:05 | 461
       4 | catalog_product_category | habilitado | inativo | 2021-06-29 22:29:03 | 98015
       5 | catalogrule_rule | habilitado | inativo | 2021-06-29 22:30:05 | 0
       6 | catalog_product_attribute | habilitado | inativo | 2021-06-29 22:30:05 | 2310051
       7 | catálogoinventário_estoque | habilitado | inativo | 2021-06-29 22:30:05 | 90478
       8 | inventário | habilitado | inativo | 2021-06-29 22:30:05 | 271476
       9 | catalogrule_product | habilitado | inativo | 2021-06-29 22:29:03 | 2505904
      10 | catalog_product_price | habilitado | trabalhando | 2021-06-29 22:30:05 | 56676992
      11 | amasty_xsearch_category_fulltext | habilitado | inativo | 2021-06-29 22:29:03 | 54687
      12 | catalogsearch_fulltext | habilitado | inativo | 2021-06-29 22:29:03 | 2547077
      33 | amasty_mostviewed_rule_product | habilitado | inativo | 2021-06-29 22:29:03 | 2
      34 | amasty_mostviewed_product_rule | habilitado | inativo | 2021-06-29 22:29:03 | 17980739
      35 | amasty_sorting_bestseller | habilitado | inativo | 2021-06-29 22:29:03 | 0
      36 | amasty_sorting_most_viewed | habilitado | inativo | 2021-06-29 22:29:03 | 0
      37 | amasty_sorting_wished | habilitado | inativo | 2021-06-29 22:29:03 | 0
      38 | amasty_yotpo_review | habilitado | inativo | 2021-06-29 22:30:16 | 0
      43 | amasty_elastic_relevance_rule_rule | habilitado | inativo | 2021-06-29 22:29:03 | 0
      44 | amasty_elastic_relevance_rule_product | habilitado | inativo | 2021-06-29 22:29:03 | 18507058
      45 | amasty_elastic_popup_data | habilitado | inativo | 2021-06-29 22:29:03 | 0
      46 | amasty_xlanding_product_page | habilitado | inativo | 2021-06-29 22:29:03 | 13596598
      47 | amasty_xlanding_page_product | habilitado | inativo | 2021-06-29 22:29:03 | 0
      48 | merchandiser_product_attribute | habilitado | inativo | 2021-06-29 22:29:03 | 3027849

    db<>fique aqui

    • 1

relate perguntas