Todo el que esté familiarizado con las bases de datos MySQL sabe que un campo que admita valores nulos no puede ser una clave única porque al insertar 2 veces el valor null esto daría error, sin embargo no es descabellado pensar que necesitemos un campo que admita valores nulos pero que en caso de no ser nulo sea único.
Existen varias formas de hacer esto, una del lado del código y otra con las herramientas propias de la base de datos. La más compleja pero más eficiente es la segunda de estas opciones y es sobre la que trata este artículo.
En esto post voy a hablar de la creación de 2 triggers (disparadores) para MySQL que validan que al insertar u actualizar una fila un determinado campo sea nulo o único. La sintaxis de los triggers en MySQL puede ser muy sencilla e intuitiva una vez la conoces pero como todos los lenguajes tiene su particularidad, lo importante es saber que no se pueden tener 2 trigger para un mismo evento en una tabla por tanto si queremos validar 2 campos de este tipo en una misma tabla deberemos hacerlo en el mismo disparador.
No me voy a sentar en la explicación de cómo y cuando se debe crear un trigger porque para eso ya existen manuales muchos más completos en internet, solo voy a reducirme a plasmar el código y explicar algunas características que hacen que cumpla su funcionalidad.
Trigger para evitar que se inserten valores duplicados en un campo
Sintaxis SQL:
DROP TRIGGER IF EXISTS insert_duplicate;
DELIMITER §
CREATE TRIGGER insert_duplicate BEFORE INSERT ON table
FOR EACH ROW
BEGIN
SET @contador = 0;
IF NEW.numero IS NOT NULL THEN
SELECT count(*) INTO @contador FROM table WHERE numero like NEW.numero;
IF @contador > 0 THEN
set @msg = concat('Trigger Error: Trying to insert a duplicate value in numero: ', cast(NEW.numero as char));
signal sqlstate '45000' set message_text = @msg;
END IF;
END IF;
END§
DELIMITER ;
Mi disparador se llama insert_duplicate y se ejecutará antes de insertar cualquier registro en la tabla table. Lo primero que hago es eliminarlo si existe y luego lo creo nuevamente. El funcionamiento del disparador es sencillo:
-
declaro una variable contador que será la encargada de decirme cuantos hay en ese momento.
-
si en nuevo valor no es nulo entonces efectúo la validación, en caso contrario no hago nada.
-
ejecuto una consulta SQL para saber cuantos registros de la tabla ya tienen este valor y lo guardo en la variable contador (es muy importante utilizar esta sintaxis para guardar el valor del resultado dentro de la variable porque en caso contrario nos puede dar un error del tipo Not allowed to return a result set from a trigger)
-
si el valor es mayor que 0 es que ya existe este valor por tanto lanzo una excepción que pare el proceso de insertar dicha fila (lanzar la excepción con signal solo es compatible con MySQL 5.5+).
Nota: al crear el mensaje de error es muy importante convertir el valor de la variable con cast a char.
Trigger para evitar que se actualice una fila con un valor duplicado
Sintaxis SQL:
DROP TRIGGER IF EXISTS update_duplicate;
DELIMITER §
CREATE TRIGGER update_duplicate BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
SET @contador = 0;
IF (NEW.numero <> OLD.numero OR OLD.numero IS NULL) AND NEW.numero IS NOT NULL THEN
SELECT count(*) INTO @contador FROM table WHERE numero like NEW.numero;
IF @contador > 0 THEN
set @msg = concat('Trigger Error: Trying to update a duplicate value in numero: ', cast(NEW.numero as char));
signal sqlstate '45000' set message_text = @msg;
END IF;
END IF;
END§
DELIMITER ;
La única diferencia con el trigger anterior es que en este caso la comprobación la lanzará solo cuando el valor antiguo sea nulo o distinto del nuevo y el nuevo no sea nulo, por lo demás todo es igual.
¿Alguna solución alternativa?
Comentarios