Uniqueness check for multiple values in column DB

Question

I know about creating unique index. But My problem is bit different. I want uniqueness check for different states for the same column.

For example. Table contains column Money_Transfer_status, now it can be pending, in_progress, approved, rejected etc.

Now for a given user if there is an in_progress or pending status I do not want to add new money transfer for the user, else it should be okay.

Is there some way in DB I can specify this?

Duplicate. See [here](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql?rq=1). — Ross Presser, Sep 30 '13 at 15:27
@RossPresser, That isn't a duplicate. The referenced question is about multiple columns as part of a unique key. This question is about considering multiple values for one column as being the same when checking for uniqueness. — Katniss, Sep 30 '13 at 15:29

fancyPants · Answer 1 · 2013-09-30T16:32:54.297

You have to create a before insert trigger on your table.

Something like this:

DELIMITER $$
CREATE TRIGGER trigger_name BEFORE INSERT ON your_table 
FOR EACH ROW
BEGIN
IF EXISTS (SELECT 1 FROM your_table WHERE user_id = NEW.user_id AND money_trans_status = 'pending')
THEN
   SIGNAL SQLSTATE '02000'
   SET MESSAGE_TEXT = 'pending money transfer...';
END IF;
END $$

Then your insert statement gets aborted.

Read more about signals here.

EDIT: Two things to note though, it will also behave like this:

insert into your_table values ('valid_value'); -- succeeds as expected
insert into your_table values ('non-valid_value'); -- fails as expected

insert into your_table ('valid_value'), ('non_valid_value'), ('valid_value'); -- everything fails as one row is bad

and you will need at least MySQL version 5.5

Uniqueness check for multiple values in column DB

1 Answers1