MySQL:创建具有计算约束的表 [英] MySQL: Creating Table with Calculated Constraint

问题描述

创建表时，是否可以基于两列的计算值来声明约束?

When creating a table, is it possible to declare a constraint based upon the calculated values of two columns?

这是解释我要做什么的伪代码:

Here's the psuedo code explaining what I'm trying to do:

CREATE TABLE employee_comments(
  id int(11),
  user_id int(11),
  franchise_branch_id int(11) default 0,
  corporate_branch_id int(11) default 0,
  primary key id,
  key corp_xor_franch(corporate_branch_id + franchise_branch_id > 0)
)

基本上，用户是在公司级别或特许级别上插入评论.每个评论都可以留空，但必须至少声明一个.

Basically, the user is inserting a comment at either the corporate level or the franchise level. Per comment, either one can be left blank, but at least one MUST be declared.

推荐答案

您最好的选择可能是使用TRIGGER-此处的mysql文档

Your best bet may be to use a TRIGGER - mysql documentation here http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

使用来自>如何在MySql触发器中中止INSERT操作的信息? 类似于此示例的东西应该有帮助

Using the information from How to abort INSERT operation in MySql trigger? something like this example should help

CREATE TRIGGER checkFieldsValid 
BEFORE INSERT ON employee_comments
FOR EACH ROW BEGIN

    DECLARE errmsg VARCHAR(255) DEFAULT "REQUIRED DATA NOT FOUND";

    IF NOT NEW.franchise_branch_id = 0 XOR NEW.corporate_branch_id = 0 THEN

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = errmsg;

    END IF;
END;

这篇关于MySQL:创建具有计算约束的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！