MariaDB中是否可以使用特定值创建唯一元组约束?

huangapple go评论60阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年4月6日 22:55:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/75950931.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定