CODEWITHSUNDEEP
mysql
user2781725
user2781725

Reputation: 31

Uniqueness check for multiple values in column DB

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?

Upvotes: 0

Views: 81

Answers (1)

fancyPants
fancyPants

Reputation: 51878

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

Upvotes: 2

Related Questions