Is it possible to have a unique tuple constraint in MariaDB that uses specific values?

Question

In a MariaDB 10.1 table of 50 columns, I have two columns "code" which is a sequence of 13 random characters and "maxversion" which is a tinyint boolean (0 or 1).

Is it possible to add a unique key or a constraint that says for each code, only one row can have a maxversion = 1? Keep in mind that it should still be allowed to have many rows with the same code and maxversion = 0.

Examples :

This is allowed :

code          | maxversion
123456789abcd | 1
123456789abcd | 0
123456789abcd | 0
123456789abcd | 0
dcba987654321 | 1
dcba987654321 | 0
dcba987654321 | 0

This is not allowed :

code          | maxversion
123456789abcd | 1
123456789abcd | 1
123456789abcd | 0
123456789abcd | 0
dcba987654321 | 1
dcba987654321 | 0
dcba987654321 | 0

I suppose this is possible through triggers, but is there anyway to achieve this in a cleaner and more concise way ?

Answer 1

When you update your server version to 10.2+ (or any maintained version) you have the ability to use Generated Columns with unique keys to achieve your results:

alter table c
 add c_maxversion varchar(13) as (if(maxversion, code, NULL)) unique 

Because NULL isn't a value, there isn't a unique constraint on it, but unique values occur on code where there is a maxversion = 1.

ref: https://dbfiddle.uk/XKEGsIjy

If you were doing this problem from scratch, do what Georg suggested and use NULL instead of 0 in the maxversion column and then you just have code, maxversion as a unique index without needing Generated Columns.