MySQL断言

Question

我是MySQL和SQL新手，我刚刚发现MySQL不支持断言。

我有那些桌子：

create table stagione
  (
    nome           varchar(20), 
    biennio        char(9), 
    teatro         varchar(20),
    primary key(nome, biennio),
    foreign key (teatro) references teatro(nome) 
on update cascade on delete set null
  )
  ENGINE=InnoDB;

create table produzione
  (
    produttore     varchar(20), 
    spettacolo     varchar(40),
    primary key(produttore, spettacolo),
    foreign key (produttore) references produttore(nome)
                on update cascade on delete cascade,
    foreign key (spettacolo) references spettacolo(titolo)
                on update cascade on delete cascade

  )
  ENGINE=InnoDB;

create table proposta
  (
   nomeStagione varchar(20),
   biennioStagione char(9),
   spettacolo varchar(40),
   primary key(nomeStagione, biennioStagione, spettacolo),
   foreign key (nomeStagione, biennioStagione) references stagione(nome, biennio)
on update cascade on delete cascade,
   foreign key (spettacolo) references spettacolo(titolo)
 on update cascade on delete cascade
  )
ENGINE=InnoDB;

​

使用mysql，我无法编写断言。是否可以使用一个或多个触发器来模拟以下断言？

crate assertion RA2 check (
    not exists (
      select stagione.teatro, stagione.nome, stagione.biennio, count(*)
      from (stagione join proposta on 
           (stagione.nome = proposta.nomeStagione) and 
           (stagione.biennio = proposta.biennioStagione)) 
           join produzione on 
           (proposta.spettacolo = produzione.spettacolo) and
           (stagione.teatro = produzione.produttore)
      group by stagione.teatro, stagione.nome, stagione.biennio
      having count(*) > 2
    )
  );          

我该怎么写扳机？

Answer 1

如果我正确理解了您的模式，那么您就可以通过这样的触发器强制执行您的规则

CREATE TRIGGER tg_proposta_before_insert
BEFORE INSERT ON proposta
FOR EACH ROW
  SET NEW.nomeStagione = IF(
    (
      SELECT COUNT(*) total
        FROM proposta p JOIN produzione d
          ON p.spettacolo = d.spettacolo
       WHERE p.nomeStagione = NEW.nomeStagione
         AND p.biennioStagione = NEW.biennioStagione
         AND d.produttore = 
             (
               SELECT produttore
                 FROM produzione
                WHERE spettacolo = NEW.spettacolo
                LIMIT 1
             )
    ) < 2,
    NEW.nomeStagione,
    NULL
  );

为了使它能够工作，必须在NOT NULL nomeStagione 列的 proposta 表中对进行约束。您最好对参与主键的所有列都有这样的约束。

一些解释：

1. 它是一个BEFORE触发器，因为它是MySql中唯一可以更改要插入的行的列值的事件。使用NEW关键字访问这些值。
2. 现在，当您尝试将一行插入到proposta中时，它会检查是否已经为您要插入的同一戏剧播放了两次播放，这违反了nomeStagione列上的NOT NULL约束(可以是任何其他列)，从而有效地阻止了该插入的完成。

这是SQLFiddle演示。尝试取消对最后一个insert语句和Build Schema__的注释。你会发现它不会让你插入那一行。这将是本季那家剧院的第三出戏。