Unique Constraint depending on value of a field

Question

I need to create a unique constraint for my subscription table for not allowing a user to have two active subscriptions at the same time. I created the migration and updated my entity class:

CREATE UNIQUE INDEX unique_user_id_status ON user_subscription_plan (user_id, status)

But I just realised that the status field can have multiple values (active, inactive, processed) so I should probably have this constraint only when two statuses are active, but I can have two processed ones for example. Is it correct? Then how do I do that on the entity class? Any suggestion is highly appreciated.

Answer 1

As you use MySQL, see if function-based index helps.

create table test (user_id int, status varchar(20));

create unique index ui1_test on test
  (user_id, (case when status = 'active' then status end));

It won't let you insert two rows whose status is active, but won't prevent you on inserting as many rows as you want with any other status (you mentioned inactive and processed).

Have a look at fiddle for demonstration.