CODEWITHSUNDEEP
phpmysqlpdoauto-increment
user1032531
user1032531

Reputation: 26321

Retrieving last inserted ID for InnoDB composite key table

I have the following table:

CREATE  TABLE `my_table` (
  composite_pk1 INT NOT NULL ,
  composite_pk2 INT NOT NULL ,
  data VARCHAR(255) NOT NULL ,
   primary key (composite_pk1, composite_pk2)
) ENGINE=InnoDB;

For a given composite_pk1, I wish composite_pk2 to act as an autoincrement primary key. I don't wish to lock the table, and as such plan on using a trigger such as the following:

DELIMITER $$

CREATE TRIGGER my_trigger BEFORE INSERT ON my_table
FOR EACH ROW BEGIN
    SET NEW.composite_pk2 = (
       SELECT IFNULL(MAX(composite_pk2), 0) + 1
       FROM issue_log
       WHERE composite_pk1  = NEW.composite_pk1
 );

END $$

I can now insert a record:

$stmt=$myDB->prepare('INSERT INTO my_table(composite_pk1, data) VALUES (?,?)');
$stmt->execute([123,'hello']);

How do I get the last inserted composite_pk2? PDO::lastInsertId only works with native autoincrement tables (i.e. not the trigger approach). I "could" later do a SELECT query to get the max value, however, there is no guarantee that another record has snuck in.

Upvotes: 2

Views: 498

Answers (1)

Fabricator
Fabricator

Reputation: 12782

You can make composite_pk2 an unique key with auto_increment:

CREATE  TABLE `my_table` (
  composite_pk1 INT NOT NULL ,
  composite_pk2 INT NOT NULL unique auto_increment,
  data VARCHAR(255) NOT NULL ,
   primary key (composite_pk1, composite_pk2)
) ENGINE=InnoDB;

Now last_insert_id() will return the recently created id for composite_pk2.

Upvotes: 1

Related Questions