ejemplos - mysql is null or empty

O restricciones OR no nulas en MySQL

Question

¿Cuál es la mejor manera de crear una restricción no nula en MySQL de modo que fieldA y fieldB no puedan ser ambos NULL? No me importa si ninguno de los dos es NULL por sí solo, siempre que el otro campo tenga un valor no NULL. Y si ambos tienen valores no NULL, entonces es aún mejor.

Answer 1

@Sklivvz: Probando con MySQL 5.0.51a, encuentro que analiza una restricción CHECK, pero no la aplica. Puedo insertar (NULL, NULL) sin error. Probado tanto MyISAM como InnoDB. El uso posterior de SHOW CREATE TABLE muestra que una restricción CHECK no está en la definición de la tabla, aunque no se proporcionó ningún error cuando definí la tabla.

Esto coincide con el manual de MySQL que dice: "La cláusula CHECK es analizada pero ignorada por todos los motores de almacenamiento".

Entonces para MySQL, tendría que usar un disparador para hacer cumplir esta regla. El único problema es que los desencadenadores MySQL no tienen forma de generar un error o abortar una operación INSERTAR. Una cosa que puede hacer en el desencadenante para causar un error es establecer una columna NOT NULL en NULL.

CREATE TABLE foo (
  FieldA INT,
  FieldB INT,
  FieldA_or_FieldB TINYINT NOT NULL;
);

DELIMITER //
CREATE TRIGGER FieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SET NEW.FieldA_or_FieldB = NULL;
  ELSE
    SET NEW.FieldA_or_FieldB = 1;
  END IF;
END//

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error

También necesita un disparador similar ANTES DE ACTUALIZAR.

Answer 2

Esta es la sintaxis estándar para tal restricción, pero MySQL felizmente ignora la restricción posterior

ALTER TABLE `generic` 
ADD CONSTRAINT myConstraint 
CHECK (
  `FieldA` IS NOT NULL OR 
  `FieldB` IS NOT NULL
) 

Answer 3

He hecho algo similar en SQL Server, no estoy seguro de si funcionará directamente en MySQL, pero:

ALTER TABLE tableName ADD CONSTRAINT constraintName CHECK ( (fieldA IS NOT NULL) OR (fieldB IS NOT NULL) );

Al menos creo que esa es la sintaxis.

Sin embargo, tenga en cuenta que no puede crear restricciones de verificación en las tablas, solo puede verificar las columnas dentro de una tabla.

Answer 4

Esta no es una respuesta directa a su pregunta, sino cierta información adicional.

Cuando trato con varias columnas y comprobando si todas son nulas o una no es nula, normalmente uso COALESCE() ; es breve, fácil de leer y fácil de leer si la lista crece:

COALESCE(a, b, c, d) IS NULL -- True if all are NULL

COALESCE(a, b, c, d) IS NOT NULL -- True if any one is not null

Esto se puede usar en tu disparador.

Answer 5

MySQL 5.5 introdujo SIGNAL , por lo que ya no necesitamos la columna adicional en la respuesta de Bill Karwin. Bill señaló que también necesita un desencadenador para la actualización, así que lo he incluido también.

CREATE TABLE foo (
  FieldA INT,
  FieldB INT
);

DELIMITER //
CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE ''45000''
    SET MESSAGE_TEXT = ''/'FieldA/' and /'FieldB/' cannot both be null'';
  END IF;
END//
CREATE TRIGGER UpdateFieldABNotNull BEFORE UPDATE ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE ''45000''
    SET MESSAGE_TEXT = ''/'FieldA/' and /'FieldB/' cannot both be null'';
  END IF;
END//
DELIMITER ;

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error
UPDATE foo SET FieldA = NULL; -- gives error