Univocità Attributo | Tips & Tricks

 drop table if exists mytable;
 create table mytable (
 id int(11) not null auto_increment primary key,
 available tinyint(1) not null
 );
 
 drop table if exists errori;
 create table errori (                                                                                                         
 id int not null auto_increment primary key,                                                                         
messaggio varchar(128) default null,                                                                                    
unique key imess (messaggio)
) engine=myisam;


delimiter //
drop trigger if exists verifica//
create trigger verifica before insert on mytable
for each row
begin
   if(select count(*) from mytable where available = 1 > 0 and new.available = 1) then
        call fallito("esiste gia' un record con valore pari a 1");
   end if;
end//
delimiter ;


delimiter //
drop procedure if exists fallito //
create procedure fallito(mess varchar(200))
begin
  insert into errori (messaggio) values (mess);
  insert into errori (messaggio) values (mess);
end//
delimiter ;


insert into mytable (available) values (0);
insert into mytable (available) values (1);
insert into mytable (available) values (0);
insert into mytable (available) values (0);
insert into mytable (available) values (1);