Either OR non-null constraints in MySQL

问题:

What's the best way to create a non-NULL constraint in MySQL such that fieldA and fieldB can't both be NULL. I don't care if either one is NULL by itself, just as long as the other field has a non-NULL value. And if they both have non-NULL values, then it's even better.

回答1:

MySQL 5.5 introduced SIGNAL, so we don't need the extra column in Bill Karwin's answer any more. Bill pointed out you also need a trigger for update so I've included that too.

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:

This isn't an answer directly to your question, but some additional information.

When dealing with multiple columns and checking if all are null or one is not null, I typically use COALESCE() - it's brief, readable and easily maintainable if the list grows:

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

This can be used in your trigger.

回答3:

@Sklivvz: Testing with MySQL 5.0.51a, I find it parses a CHECK constraint, but does not enforce it. I can insert (NULL, NULL) with no error. Tested both MyISAM and InnoDB. Subsequently using SHOW CREATE TABLE shows that a CHECK constraint is not in the table definition, even though no error was given when I defined the table.

This matches the MySQL manual which says: "The CHECK clause is parsed but ignored by all storage engines."

So for MySQL, you would have to use a trigger to enforce this rule. The only problem is that MySQL triggers have no way of raising an error or aborting an INSERT operation. One thing you can do in the trigger to cause an error is to set a NOT NULL column to 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

You also need a similar trigger BEFORE UPDATE.

回答4:

This is the standard syntax for such a constraint, but MySQL blissfully ignores the constraint afterwards

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

回答5:

I've done something similar in SQL Server, I'm not sure if it will work directly in MySQL, but:

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

At least I believe that's the syntax.

However, keep in mind that you cannot create check constraints across tables, you can only check the columns within one table.