英文:
Is it possible to have a unique tuple constraint in MariaDB that uses specific values?
问题
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 any way to achieve this in a cleaner and more concise way?
英文:
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 ?
答案1
得分: 1
当您将服务器版本升级到10.2+(或任何维护版本),您可以使用Generated Columns与唯一键来实现您的结果:
alter table c
add c_maxversion varchar(13) as (if(maxversion, code, NULL)) unique
因为NULL
不是一个值,所以它没有唯一约束,但唯一值出现在maxversion
= 1的代码上。
参考链接:https://dbfiddle.uk/XKEGsIjy
如果您从头开始解决这个问题,可以像Georg建议的那样,在maxversion
列中使用NULL
而不是0
,然后您只需将code, maxversion
作为唯一索引,而无需使用Generated Columns。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论