1

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.

Brandon Buster
  • 1,195
  • 1
  • 9
  • 12
  • I'm pretty sure MySQL doesn't support this. But see: http://stackoverflow.com/questions/5422729/mysql-computed-column – siride Mar 26 '13 at 13:19
  • Why don't you have just one `branch_id` (which will probably have a `FOREIGN KEY` constraint to `branch`)? – ypercubeᵀᴹ Mar 26 '13 at 13:24
  • how about an insert and an update trigger that would check the values are correct? see trigger documentation here http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html – Youn Elan Mar 26 '13 at 13:44

1 Answers1

2

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

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;
Community
  • 1
  • 1
Simon at The Access Group
  • 6,932
  • 22
  • 45