MySQL assertion

Question

I'm a MySQL and SQL newbie, I just discovered that MySQL doesn't support assertions.

I've those tables:

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;

with mysql I have no way of writing assertions. Is it possible to simulate the following assertion using one or more triggers ?

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
    )
  );          

how should I write that trigger?

Solution

If I understand correctly your schema then you can enforce your rule by a trigger like this

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
  );

In order for it to work you have to have NOT NULL constraint on nomeStagione column in proposta table. You better have such constraints on all columns that are participate in primary keys anyway

Some explanation:

1. It's a BEFORE trigger because it's the only event in MySql in which you can alter values of columns of a row being inserted. You access those values using NEW keyword.
2. Now when you try to insert a row into proposta it checks if there are already two plays for the same theater play of which you're trying to insert it violates NOT NULL constraint on nomeStagione column (it can be any other column) effectively preventing this insert from being completed.

Here is SQLFiddle demo. Try to uncomment the last insert statement and Build Schema. You'll see that it won't let you to insert that row. It would've been a third play for that theater in this season.

Comments

[pratnala]

Question. Doesn't making the primary key enforce the NOT NULL constraint?

[Melanef]

Wouldn't it be better if you used a procedure to check before inserting?