Flite Careers

A Workaround for MySQL ERROR 1070

As documented in the Reference Manual MySQL supports a maximum of 16 columns per index. That’s more than sufficient for most index use cases, but what about unique constraints? If I create a fact table with more than 16 dimension columns in my star schema, and then try to add an index to enforce a unique constraint across all of the dimension columns, then I’ll get this error:

1

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

For multi-column unique indexes, internally MySQL concatenates all of the column values together in a single hyphen-delimited string for comparison. Thus I can simulate a multi-column unique index by adding an extra column that stores the concatenated column values, and adding a unique index on that column.

Read on for details…

I could populate the new column in my application code, but for the sake of this blog post I’ll use MySQL triggers instead.

Here’s a table with 20 columns:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE TABLE IF NOT EXISTS lots_of_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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As previously discussed, I can’t add a unique index on all 20 columns:

1
2
3

mysql> alter table lots_of_columns 
    ->   add unique index unique_constraint (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20);
ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

Proceeding with the workaround, I add a new column with a unique index on it. The column needs to be long enough to store the concatenated values. For my test table each INT column value can be up to 11 chars long, each CHAR column value can be up to 8 characters long, and the hyphens will account for 19 characters, so the new column needs to store up to (10 * 11) + (10 * 8) + 19 = 209 characters. I’ll make it 255:

1
2
3

alter table lots_of_columns 
  add column all_columns varchar(255) not null default '',
  add unique index unique_constraint (all_columns);

I need two triggers to populate the new column, a BEFORE INSERT trigger and an AFTER INSERT trigger. Here they are:

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

delimiter $$

drop trigger if exists tr_b_ins_lots_of_columns $$

create trigger tr_b_ins_lots_of_columns before insert on lots_of_columns 
for each row 
begin
  set new.all_columns = concat(
    new.c1,'-',new.c2,'-',new.c3,'-',new.c4,'-',new.c5,'-',
    new.c6,'-',new.c7,'-',new.c8,'-',new.c9,'-',new.c10,'-',
    new.c11,'-',new.c12,'-',new.c13,'-',new.c14,'-',new.c15,'-',
    new.c16,'-',new.c17,'-',new.c18,'-',new.c19,'-',new.c20
);
end $$

drop trigger if exists tr_b_upd_lots_of_columns $$

create trigger tr_b_upd_lots_of_columns before update on lots_of_columns 
for each row 
begin
  set new.all_columns = concat(
    new.c1,'-',new.c2,'-',new.c3,'-',new.c4,'-',new.c5,'-',
    new.c6,'-',new.c7,'-',new.c8,'-',new.c9,'-',new.c10,'-',
    new.c11,'-',new.c12,'-',new.c13,'-',new.c14,'-',new.c15,'-',
    new.c16,'-',new.c17,'-',new.c18,'-',new.c19,'-',new.c20
);
end $$

delimiter ;

To test the unique index I try to insert two duplicate rows:

1
2
3
4
5
6
7

mysql> insert into lots_of_columns (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20) 
    -> values (1,2,3,4,5,6,7,8,9,10,'a','b','c','d','e','f','g','h','i','j');
Query OK, 1 row affected (0.00 sec)

mysql> insert into lots_of_columns (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20) 
    -> values (1,2,3,4,5,6,7,8,9,10,'a','b','c','d','e','f','g','h','i','j');
ERROR 1062 (23000): Duplicate entry '1-2-3-4-5-6-7-8-9-10-a-b-c-d-e-f-g-h-i-j' for key 'unique_index'

The second insert failed as desired. Now let me test an update:

1
2
3
4
5
6

mysql> insert into lots_of_columns (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20) 
    -> values (1000,2,3,4,5,6,7,8,9,10,'a','b','c','d','e','f','g','h','i','j');
Query OK, 1 row affected (0.00 sec)

mysql> update lots_of_columns set c1 = 1 where c1 = 1000;
ERROR 1062 (23000): Duplicate entry '1-2-3-4-5-6-7-8-9-10-a-b-c-d-e-f-g-h-i-j' for key 'unique_index'

Again the unique constraint was properly enforced.

MySQL
Comments
Back to Top

Posted by Ike Walker

Comments