Duplicate Key Problem

MySQL & MariaDB Other MySQL® Questions
1

Dear All,
I am quite new to innodb. My problem is a bit funny. I have a table which stores a serial and the status with Id as the primary key which I set. My problem is like this
ID Serial Status
1 12345 b
2 12345 b
3 12345 b
4 12345 b
5 12345 y

What I want to enable is that any time the combination of serial & status as ‘b’ can be duplicate and serial and status ‘y’ cannot be duplicate only one. How can I enforce this integrity in my datatabe. Thanks.

2

You can’t fix it simply by using unique keys. You will need to create trigger which will check on insert/update if there is already row matching your criteria.

3

Dear Peter,
So I have to build my trigger some thing like below rite. I not sure with the trigger syntax
CREATE TRIGGER test1 BEFORE INSERT ON table1
FOR EACH ROW BEGIN
If
Select ID from table1 where serial=‘12345’ and status=‘y’
Else
// allow me to insert my new query
END;

My problem now is how to decide if previously it exist that is using a query as i stated above and also send the serial value ?
I have another solution is that before I do an insert in my program I will run through the database and see if it exist if yes then dont allow this new insert to happen. Which you think is much better the trigger or check before the database entry ? Thanks for your help.