How does auto increment work with composite keys in the MyISAM engine?

Question

How does auto increment work with composite keys in the MyISAM engine?

Navigation

#1 by (5 votes)

5

I ended up getting a table to manipulate, I noticed that sometimes the value of auto increment was repeated, the first thing that called me to Attention was that the primary key was composed of two fields, one of which was AI and the other not.

I have an example that reproduces this behavior:

CREATE TABLE t (
   id int(11) NOT NULL AUTO_INCREMENT,
   c2 varchar(45) NOT NULL,
   c3 varchar(45) NOT NULL,
   PRIMARY KEY (c2, id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Inserts

INSERT INTO t(c2, c3) VALUES ('A', 'A'), ('A', 'B'), ('B', 'A'), ('B', 'C'), ('C', 'A'), ('C', 'B'), ('A', 'C')

Select result:

# |id|c2|c3
1 |1 |A |A
2 |1 |B |A
3 |1 |C |A
4 |2 |A |B
5 |2 |B |C
6 |2 |C |B
7 |3 |A |C

What is the logic of auto increment when it is part of a composite primary key?

mysql auto-incremento

asked by anonymous 18.02.2016 / 17:11

1 answer

How does SqlBulkCopy work? Show hint only when the application starts for the first time

score 5 · Answer 1

Solution (if you really need a compound PK with AI field)

The 'problem' is restricted to the MyISAM engine, since InnoDB does not allow this type of construction. This repetition happens only when the auto increment field is not the first of the primary key compound PRIMARY KEY (c2, id) . By inviting the order of the fields auto increment works sequentially as expected.

DROP TABLE t;

CREATE TABLE t (
  id int(11) NOT NULL AUTO_INCREMENT,
  c2 varchar(45) NOT NULL,
  c3 varchar(45) NOT NULL,
  PRIMARY KEY (id, c2)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t(c2, c3) VALUES ('A', 'A'), ('A', 'B'), ('B', 'A'), ('B', 'C'), ('C', 'A'), ('C', 'B'), ('A', 'C');

SELECT * FROM t

Select result:

# |id|c2|c3
1 |1 |A |A
2 |2 |A |B
3 |3 |B |A
4 |4 |B |C
5 |5 |C |A
6 |6 |C |B
7 |7 |A |C

How it works

Before the value of auto increment is determined, a check of the value of the other column (s) (PK) is made, if it exists (see inserts 1, 2 and 7) the column AI will have its value incremented by +1 otherwise its value will be 1, ie increment works by 'group'.