0

I like to set internal AUTO_INCREMENT counter for the table, which uses MyISAM and composite primary key - but just for specific PK combination. If I use

ALTER TABLE tablename AUTO_INCREMENT = 1;

It will set internal counter for all composite PK combinations, which I don't want.

I need something like

ALTER TABLE tablename AUTO_INCREMENT = 1 WHERE prefix = 5 AND suffix = X;

It does not work this way. Is there any possibility to change only counter for specific PK combination in MyISAM table?

Table:

CREATE TABLE `ENG__faktury_counter` 
(
    `year` int(10) NOT NULL,
    `prefix` varchar(10) NOT NULL,
    `DIC` varchar(50) NOT NULL,
    `id_counter` int(15) NOT NULL AUTO_INCREMENT ,
    `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
     PRIMARY KEY (`year`,`prefix`,`DIC`,`id_counter`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ivan
  • 315
  • 1
  • 3
  • 16
  • I found possibility to manage this by deleting/adding rows for given PK combination, but seems to me a bit.. strange. – Ivan Jan 08 '19 at 13:19

1 Answers1

0

There is virtually zero use for ALTER TABLE ... AUTO_INCREMENT=....

If you are using MyISAM and have 

foo ...
id ... AUTO_INCREMENT
PRIMARY KEY(foo, id)  -- where the _2nd_ column is auto-inc

Then there is nothing to do to get

foo  id
---  --
cat   1
cat   2
dog   1
bird  1
cat   3
bird  2
bird  3
dog   2

regardless of the order in which you insert the rows.

If this does not address your question, please enhance it with an example and SHOW CREATE TABLE.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks for your comment, I have added table for better question description. – Ivan Jan 09 '19 at 14:03
  • For MyISAM to do the work, the auto-inc must be the second column in the PK. – Rick James Jan 09 '19 at 16:13
  • No sure if I understand. AC is working fine, there isn't any problem with that (it's 4-th column right now), I just need to know how to set counter for specific PK combination and not for whole table. – Ivan Jan 09 '19 at 16:30
  • No, this question IS NOT a duplicate to mentioned one and it is not answered in referred answer. Please remove you invalid duplicate mark, thank you. – Ivan Jan 10 '19 at 12:38
  • How about an `INSERT TRIGGER` that sets the id to `MAX(id)+1 WHERE` the other 3 columns match? – Rick James Jan 10 '19 at 22:27
  • AI is working really fine, I don't need to create trigger. I just wanted to reset number for specific combination, because I did wrong inserts. Anyway, seems that manual approach (deleting and inserting rows) is only one working solution for this situation and there isn't real command to make that. Deleting wrong rows will do the job exactly, as on the next INSERT MAX(id)+1 will generate correct ID. – Ivan Jan 11 '19 at 16:39