MySQL auto_increment on composite key no longer supported?

Question

I have a server running an old MySQL version (5.5.41) where the following was supported:

create table mytest33
(
    topicnum mediumint, 
    postnum mediumint auto_increment, 
    primary key (topicnum,postnum)
);

So when I insert values into this table, it would go like this:

Topicnum  Postnum
1         1
1         2
2         1

And so forth. So the postnum value would go back to 1, if the topicnum was different.

This is no longer supported - when I try creating the same table in MySQL version 5.6.17 I get an error:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key.

I'm using the MyISAM engine.

How do I get around this?

create table mytest33(topicnum mediumint, postnum mediumint , primary key (topicnum,postnum)); Try this it works fine . — salil vishnu Kapur, Apr 05 '15 at 11:07
Did your original version have a trigger that maintained the ordering? I am not aware that MySQL ever supported this functionality directly. — Gordon Linoff, Apr 05 '15 at 13:32
salil: sure that works, but it doesn't include the auto_increment.. — Ras79, Apr 06 '15 at 15:35
Gordon - no trigger or anything else was set up, no. The query I posted initially worked fine in older versions of MySQL such as 5.5.41. — Ras79, Apr 06 '15 at 15:36

score 0 · Answer 1 · edited May 23 '17 at 11:58

0

According to what i could understand ,here is the command that you need to execute:-

create table mytest33(topicnum mediumint, postnum mediumint auto_increment,index(postnum), primary key (topicnum,postnum));

You can get more information from here:- Mysql Innodb: Autoincrement non-Primary Key.

Hope it helps. Have a good day!

edited May 23 '17 at 11:58

Community

1
1

answered Apr 05 '15 at 11:16

salil vishnu Kapur

660
1
6
29

Thanks, that query works. But it doesn't solve my problem: the value of postnum doesn't follow the topicnum. It just increments by one each time a new row is inserted, regardless of what the topicnum is. Ideally, if two rows are inserted with topicnum 1, and two rows with topicnum 2, the value of postnum should be 1 and 2 for the rows with topicnum 1, and 1 and 2 again for the rows with topicnum 2. This is how it used to work in old versions of MySQL. – Ras79 Apr 06 '15 at 15:40

score 0 · Answer 2 · answered Apr 06 '15 at 03:22

0

The MyISAM Engine has that as a feature, and it is still supported. InnoDB has never had the feature. You switched to InnoDB (which is a good thing), but got bitten by one of the few 'deficiencies'.

The link in the previous answer points out that INDEX(id) lets you have postnum be AUTO_INCREMENT and UNIQUE (unless you explicitly set it). However, it won't start over at 1. That is more complex; see my blog.

answered Apr 06 '15 at 03:22

Rick James

135,179
13
127
222

You're right - it seems to work as expected if I explicitly state that it's a MyISAM table: – Ras79 Apr 06 '15 at 15:48

score 0 · Answer 3 · answered Apr 06 '15 at 15:52

0

So this seems to solve the problem:

create table mytest33(
    topicnum mediumint,
    postnum mediumint auto_increment, 
    primary key (topicnum,postnum)
) ENGINE=MyISAM;

answered Apr 06 '15 at 15:52

Ras79

21
1

MySQL auto_increment on composite key no longer supported?

3 Answers3