Adding a Unique Constraint With More Than 16 Columns in MariaDB
When I started writing this post I planned to follow up on my series of posts by creating a unique constraint with more than 16 columns using a MariaDB virtual column the same way I used a MySQL generated column in my most recent post. During my testing I abandoned that plan when I discovered two things:
- MariaDB virtual columns impose a 252 character limit on the expression that defines the column. This works for concatenating lots of columns with very short names like I did in my last post, but in the real world it’s easy to find an example where column names are long enough that a concatenate expression involving more than 16 columns is longer than 252 characters.
- MariaDB doesn’t have the same 16 column limit on indexes; instead it imposes a limit of 32 columns. Thus I can add a unique constraint on 17-32 columns in MariaDB without having to do anything special.
So I can’t use MariaDB virtual columns as a workaround to add a unique constraint on more than 16 columns, but it probably doesn’t matter because I actually don’t need that workaround as long as my unique constraint includes no more than 32 columns.
Read on for code examples.
My original use case of creating a 20 column unique constraint works as-is in MariaDB, so in order to get error 1070 I have to go beyond 32 columns. Since the resulting concatenated column will be larger than 767 bytes, I need to make sure I’m using
ROW_FORMAT=DYNAMIC as I’ve written about in the past.
With those settings in place, I create a table with 40 columns:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
To prove that I don’t need the workaround, I create a unique constraint on 32 columns:
1 2 3 4 5 6
But if I try to add a unique constraint on 33 columns I get an error:
1 2 3 4 5
If I try to create a unique constraint on all 40 columns using a virtual column I get the error about the 252 character expression limit:
1 2 3 4 5 6 7 8 9 10
In the end, the good news is that MariaDB supports my original use case of creating a unique constraint on 20 columns.
As for virtual columns, MariaDB had a big head start on MySQL since it added virtual columns in version 5.2 which went GA more than 4 years ago. MySQL generated columns are still not GA yet, but they appear to have some advantages over MariaDB virtual columns: As far as I know MySQL generated columns do not have a 252 character limit on the generated column expression, and based on the MySQL 5.7.7 labs release it appears that generated columns will allow you to create an index on a virtual (not stored) generated column, which MariaDB does not support.