I have a table Stuff, that has extra information in the table Heighness. Heighness has a foreign key to Stuff and a column level. The column level may have a limitless occurence of the same values per foreign key to Stuff, except only max one occurence of the value topMost per foreign key to Stuff. So that each occurence of Stuff may have only one Heighness element which is counted as topMost. The level column must always be set to a value (Non-Null).
Can MariaDB have constraints for that? Like this:
Stuff-Table
| ID | ... |
+----+-----+
| 3 | ... |
| 4 | ... |
Heighness-Table
| ID | Stuff_id | level |
+----+----------+----------+
| 1 | 3 | mediocre |
| 2 | 3 | mediocre | <-- As it's not "topMost", as many as you want.
| 3 | 3 | topMost | <-- Perfect, only 0 to 1 occurences.
| 4 | 4 | topMost | <-- 2nd occurence absolute, but only 1st per foreign key. Nice.
| 5 | 4 | low |
| 6 | 4 | topMost | <-- 2 occurences per foreign key! Error! MariaDB should block this!
MariaDB 10.6 has unique indices, but not filtered unique indices, checks and unique constraints, it seems that isn't enough and I would've fall back to triggers, what I would like to avoid.
Am I wrong? Is there a combination of UNIQUE and CHECK constraints that I can use?
Create a generated column which is unique for topMost and not mediocre and create a unique index on it:
ALTER TABLE Heighness
ADD uqLevel varbinary(12) AS ( CONCAT(Stuff_id, '-', LEFT(level, 1), IF(level= topMost, Stuff_id, ID)) )
PERSISTENT UNIQUE
This generates for the table:
3-m1
3-m2
3-t3
4-t4
4-l5
4-t4 - This is a duplicate.
A normal FK constaint additionally with ensure Stuff_id is in the Sutff table