MySQL table constraint with 2 values

Question

In my MySQL DB I have a table that stores information about contractor's activities:

`task_id` int(11) DEFAULT '0',
`task_date` date DEFAULT NULL,
`contractor_id` int(11) DEFAULT '0'

Business logic demands that task_id's 5 and 6 are mutually exclusive, and for any given date any given contractor can have only 1 of them. Is it possible to impose some constraint on that table that would enforce this logic?

score 0 · Accepted Answer · edited May 23 '17 at 12:15

0

I don't think adding constraint will solve the problem. You can try adding a trigger before insert on table and check if for that contractor_id you trying to insert, if its new.task_id is equal to 5 either 6 you should get this value and select on the table if there is any registry that goes against your business metrics and avoid it to be added before the insert finishes. I think that this simple explanation will help you go further this.

edited May 23 '17 at 12:15

Community

1
1

answered Feb 15 '16 at 00:33

I was expecting that some complex logic will be required. Thanks for the link, that's exactly what I needed. – Ogeeon Feb 15 '16 at 22:33

MySQL table constraint with 2 values

1 Answers1