Либо ИЛИ ненулевые ограничения в MySQL

Ответ 1

MySQL 5.5 представил SIGNAL, поэтому нам больше не нужен дополнительный столбец в Билле Карвине. Билл указал, что вам также нужен триггер для обновления, поэтому я тоже включил его.

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

Ответ 2

@Sklivvz: тестирование с MySQL 5.0.51a, я нахожу, что он анализирует ограничение CHECK, но не применяет его. Я могу вставить (NULL, NULL) без ошибок. Протестировано как MyISAM, так и InnoDB. Впоследствии использование SHOW CREATE TABLE показывает, что ограничение CHECK не указано в определении таблицы, хотя при определении таблицы не было ошибок.

Это соответствует руководству MySQL, в котором говорится: "Предложение CHECK анализируется, но игнорируется всеми механизмами хранения".

Итак, для MySQL вам придется использовать триггер для принудительного применения этого правила. Единственная проблема заключается в том, что триггеры MySQL не могут вызвать ошибку или прервать операцию INSERT. Одна вещь, которую вы можете сделать в триггере, чтобы вызвать ошибку, - установить столбец NOT NULL в 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

Вам также нужен аналогичный триггер ПЕРЕД ОБНОВЛЕНИЕМ.

Ответ 3

Это не ответ на ваш вопрос, а дополнительная информация.

Когда вы работаете с несколькими столбцами и проверяете, все ли они null или один не является нулевым, я обычно использую COALESCE() - он короткий, читаемый и легко поддерживаемый, если список растет:

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

Это может использоваться в вашем триггере.

Ответ 4

Это стандартный синтаксис для такого ограничения, но MySQL блаженно игнорирует ограничение впоследствии

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

Ответ 5

Я сделал что-то подобное в SQL Server, я не уверен, будет ли он работать непосредственно в MySQL, но:

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

По крайней мере, я считаю, что синтаксис.

Однако имейте в виду, что вы не можете создавать контрольные ограничения в таблицах, вы можете проверять столбцы только в одной таблице.