0

Writing the title is hard, so here's the issue.

store_id | supplier_id | supplier_is_active
A        | 2           | 1
A        | 3           | 0
B        | 4           | 0
B        | 5           | 1

I need to make sure that each store_id can only have one active supplier, and I'd like to do this via a table constraint CHECK. But I don't think it's possible to do aggregation within a constraint.

Any thoughts to a clever workaround?

Nate
  • 1
  • 1
    You can do this in other databases with a filtered unique index. In MySQL, you can use a trigger. – Gordon Linoff Dec 18 '19 at 03:05
  • The second answer [here](https://stackoverflow.com/questions/9734920/can-a-mysql-trigger-simulate-a-check-constraint) shows how to emulate a check constraint with a trigger. – Nick Dec 18 '19 at 03:25

0 Answers0