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:

1. 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.
2. 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 innodb_file_format=BARRACUDA, innodb_large_prefix=ON, and ROW_FORMAT=DYNAMIC as I've written about in the past.

With those settings in place, I create a table with 40 columns:

``` MariaDB [test]> CREATE TABLE IF NOT EXISTS even_more_columns (

->   c1 int not null,
->   c2 int not null,
->   c3 int not null,
->   c4 int not null,
->   c5 int not null,
->   c6 int not null,
->   c7 int not null,
->   c8 int not null,
->   c9 int not null,
->   c10 int not null,
->   c11 char(8) not null,
->   c12 char(8) not null,
->   c13 char(8) not null,
->   c14 char(8) not null,
->   c15 char(8) not null,
->   c16 char(8) not null,
->   c17 char(8) not null,
->   c18 char(8) not null,
->   c19 char(8) not null,
->   c20 char(8) not null,
->   c21 int not null,
->   c22 int not null,
->   c23 int not null,
->   c24 int not null,
->   c25 int not null,
->   c26 int not null,
->   c27 int not null,
->   c28 int not null,
->   c29 int not null,
->   c30 int not null,
->   c31 char(8) not null,
->   c32 char(8) not null,
->   c33 char(8) not null,
->   c34 char(8) not null,
->   c35 char(8) not null,
->   c36 char(8) not null,
->   c37 char(8) not null,
->   c38 char(8) not null,
->   c39 char(8) not null,
->   c40 char(8) not null
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Query OK, 0 rows affected (0.02 sec) ```

To prove that I don't need the workaround, I create a unique constraint on 32 columns:

``` MariaDB [test]> alter table even_more_columns

->   add unique index unique_constraint_32col (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,
->     c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,
->   c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32);

Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 ```

But if I try to add a unique constraint on 33 columns I get an error:

``` MariaDB [test]> alter table even_more_columns

->   add unique index unique_constraint_33col (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,
->     c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,
->   c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32,c33);

ERROR 1070 (42000): Too many key parts specified; max 32 parts allowed ```

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:

``` MariaDB [test]> alter table even_more_columns

->   add column all_columns varchar(512) as (
->   concat(c1,'-',c2,'-',c3,'-',c4,'-',c5,'-',c6,'-',c7,'-',c8,'-',c9,'-',c10,'-',
->     c11,'-',c12,'-',c13,'-',c14,'-',c15,'-',c16,'-',c17,'-',c18,'-',c19,'-',c20,'-',
->     c21,'-',c22,'-',c23,'-',c24,'-',c25,'-',c26,'-',c27,'-',c28,'-',c29,'-',c30,'-',
->     c31,'-',c32,'-',c33,'-',c34,'-',c35,'-',c36,'-',c37,'-',c38,'-',c39,'-',c40)
->   ) persistent,
->   add unique index unique_constraint (all_columns);

ERROR 1470 (HY000): String ' concat(c1,'-',c2,'-',c3,'-',c4,'-',c5,'-',c6,'-',c7,'-',c8,'-',c9,'' is too long for VIRTUAL COLUMN EXPRESSION (should be no longer than 252) ```

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.