Flite Careers

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:

  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:

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

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:

1
2
3
4
5
6

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:

1
2
3
4
5

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:

1
2
3
4
5
6
7
8
9
10

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.

MariaDB, MySQL
Comments
Back to Top

Posted by Ike Walker

Comments