mysql非空约束,如何在mySql中的多个列上定义条件非空约束？

I have a table in my database. This table contains several columns say A,B,C,D,E,F and G. I want to define a constraint in a way that at least one of D,E or F should not be null.

Is this possible?

I am using MySql.

Thanks

解决方案

Unfortunately, MySQL does not support CHECK constraints. It parses them and then silently discards the constraint, just like it does for foreign key constraints on a MyISAM table. It doesn't even give you a warning about the unsupported constraint type, which I think is a bad design decision on their part.

Here's a solution using a trigger:

mysql> DELIMITER //

mysql> CREATE TRIGGER check_one_not_null BEFORE INSERT ON mytable FOR EACH ROW

IF COALESCE(NEW.D, NEW.E, NEW.F) IS NULL

THEN SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'One of D, E, or F must have a non-null value.';

END IF //

You should also create a similar trigger BEFORE UPDATE on the same table.

See http://dev.mysql.com/doc/refman/5.6/en/signal.html for more information on the SIGNAL statement to raise exceptions in MySQL triggers or stored routines.