如何在mySql中的多个列上定义条件非空约束？ [英] How can I define conditional non-null constraints on several columns in mySql?

问题描述

我在我的数据库中有一个表。这个表包含几个列，比如A，B，C，D，E，F和G.我想以D，E或F中的至少一个不应该为空的方式定义一个约束。
这是可能吗？
我使用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

推荐答案

不幸的是，MySQL 不支持CHECK约束。它解析它们，然后默认丢弃约束，就像它对MyISAM表上的外键约束一样。它甚至不给你关于不支持的约束类型的警告，我认为这是一个不良的设计决策。

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.

这里有一个使用触发器的解决方案： / p>

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 //

您还应该创建一个类似的触发器 BEFORE UPDATE 。

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

请参阅 http://dev.mysql.com/doc/refman/5.6/en/signal.html 了解有关 SIGNAL的更多信息用于在MySQL触发器或存储例程中引发异常的语句。

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.

这篇关于如何在mySql中的多个列上定义条件非空约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！