Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a slight problem with a table that I have converted which used to be MyISAM, I have now made it a InnoDB table.

Original structure:

id varchar(15),
seqno int(11) auto increment 
Joint PK index on the above 2 fields

New structure:

auto_id INT (11) auto increment,
id varchar(15),
seqno int(11)

The original structure had a joint PK on id and seqno with seqno being auto incremented for each unique id value. It would then return the seqno as the last_insert_id in my web application.

With the new innodb structure, because innodb doesnt support joint primary keys, I've had to add in an auto_id which now has the auto increment.

I then implemented a trigger to generate the seqno unique per id value. My question is how do I set the auto increment id so that it returns in place of the auto_id value? This trigger would be called when I insert into my_table, so that it can calculate the next seqno value to insert with.

DROP TRIGGER IF EXISTS `innodb_seqno_trigger`;
//

CREATE TRIGGER `innodb_seqno_trigger` BEFORE INSERT ON `my_table`
 FOR EACH ROW BEGIN

SET @newseqno = 0;

SELECT MAX(seqno) INTO @newseqno
FROM my_table
WHERE id = NEW.id;

SET NEW.seqno = (@newseqno + 1);
/* HERE I NEED HELP PLEASE TO GET THE AUTO INCREMENT VALUE TO ACTUALLY RETURN NEW.seqno */
END;
//

Thanks Peter

share|improve this question
    
What do you need the value of auto_id for? –  ypercube Jul 2 '13 at 6:02
    
innodb doesnt support joint primary keys - is it so? –  dezso Jul 2 '13 at 11:11

3 Answers 3

I don't think you can get the new value of auto_id in a BEFORE insert trigger. This will do what you want (if I have understood correctly):

CREATE TRIGGER innodb_seqno_trigger 
   BEFORE INSERT ON my_table 
   FOR EACH ROW 
BEGIN

  SELECT MAX(seqno) INTO @newseqno 
  FROM my_table 
  WHERE id = NEW.id;

  SET NEW.seqno = COALESCE(@newseqno + 1, 1); 

END; 

Test at SQL-Fiddle

But have you thought what will happen if you have 2 separate connections/threads/transactions that try to insert the same id?

share|improve this answer
    
Indeed one cannot get value of auto_increment in a BEFORE trigger. @ypercube's last comment is acutely important. –  Shlomi Noach Jul 2 '13 at 19:17

because innodb doesnt support joint primary keys

Wait, what?

Unless a "joint primary key" describes something other than a composite key or compound key as primary key, then, of course, InnoDB does support this.

mysql> CREATE TABLE `jpk` (
  `id` varchar(15) NOT NULL,
  `seqno` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`,`seqno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

All right, fine... it's not that InnoDB won't allow it, it's just that the auto_increment columns has to be declared as a key (index)... so, make seqno a key, and InnoDB has no problem:

mysql> CREATE TABLE `jpk` (
  `id` varchar(15) NOT NULL,
  `seqno` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`,`seqno`),
  KEY `seqno` (`seqno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Query OK, 0 rows affected (0.25 sec)

Or did I miss something?

share|improve this answer
    
I guess they mean that in InnoDB, if you have seqno as AI and (id, seqno) as the PK, the seqno created values are not unique per id (as in MyISAM) but unique in all the table. –  ypercube Jul 2 '13 at 8:56
    
On the other, you are of course correct. InnoDB allows composite primary keys just fine. –  ypercube Jul 2 '13 at 8:59
    
@ypercube, thanks... I didn't realize MyISAM had such bizarre (in my mind) handling of auto_increment values... I stay as far away from it as possible unless I need spatial indexes or pre-5.6 fulltext. Another +1 for your answer, then. :) –  Michael - sqlbot Jul 3 '13 at 6:08

I wish InnoDB could support auto_increment the same way MyISAM does. I wrote about this before

This being the case, you must accept full responsibility for sequencing the two columns. For starters, you must remove the auto_increment option from all columns that involve your sequencing.

CREATE TABLE `jpk` (
  `id` varchar(15) NOT NULL,
  `seqno` int(11) NOT NULL,
  ...
  PRIMARY KEY (`id`,`seqno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Your trigger has be just as ypercube specified. He gets +1 for that.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.