1
\$\begingroup\$

The intent is to have an auto_increment composite primary key on a InnoDB table which mimics the behavior as on a MyISAM table. The following assumes (3) InnoDB tables all with an auto_increment composite primary key (id incrementing and a_id not incrementing). Please comment positively or negatively as you see fit. Thanks

Create a MyISAM table to create the auto_increment id only, and use a trigger to use this id for the targeted table. If there are more than one InnoDB tables that need a composite auto_incrementing primary key, add an extra primary key to the MyISAM table.

Disadvantages:

  1. No foreign key constraints allowed on the MyISAM table, however, hopefully a trigger removes the risk.
  2. Requires an additional table.

Advantages

  1. Primary key valves are not reused if deleted.
  2. Client just uses normal SQL queries and not a stored procedure.
  3. Maybe less maintenance than a stored procedure since adding a column to a table doesn't require the trigger to be modified.

enter image description here

-- MySQL Script generated by MySQL Workbench
-- 07/08/16 05:12:11
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`a`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`a` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t1` (
  `a_id` INT NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`, `a_id`),
  CONSTRAINT `fk_t1_a1`
    FOREIGN KEY (`a_id`)
    REFERENCES `mydb`.`a` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t2` (
  `a_id` INT NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`, `a_id`),
  CONSTRAINT `fk_t2_a1`
    FOREIGN KEY (`a_id`)
    REFERENCES `mydb`.`a` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t3`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t3` (
  `a_id` INT NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`, `a_id`),
  CONSTRAINT `fk_t3_a1`
    FOREIGN KEY (`a_id`)
    REFERENCES `mydb`.`a` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`inc`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`inc` (
  `a_id` INT NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  `type` CHAR(4) NOT NULL,
  PRIMARY KEY (`a_id`, `type`, `id`))
ENGINE = MyISAM;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
USE `mydb`;

DELIMITER $$
USE `mydb`$$
CREATE TRIGGER `t1_BINS` BEFORE INSERT ON `t1` FOR EACH ROW
BEGIN 
INSERT INTO inc(a_id,type) VALUES(NEW.a_id,'t1');
SET NEW.id=LAST_INSERT_ID();
END$$

USE `mydb`$$
CREATE TRIGGER `t2_BINS` BEFORE INSERT ON `t2` FOR EACH ROW
BEGIN 
INSERT INTO inc(a_id,type) VALUES(NEW.a_id,'t2');
SET NEW.id=LAST_INSERT_ID();
END$$

USE `mydb`$$
CREATE TRIGGER `t3_BINS` BEFORE INSERT ON `t3` FOR EACH ROW
BEGIN 
INSERT INTO inc(a_id,type) VALUES(NEW.a_id,'t3');
SET NEW.id=LAST_INSERT_ID();
END$$


DELIMITER ;

Testing

mysql> insert into a(id) VALUES(null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(a_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
+------+----+
1 row in set (0.00 sec)

mysql> insert into t1(a_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(a_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    1 |  3 |
+------+----+
3 rows in set (0.00 sec)

mysql> insert into t2(a_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t2;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
+------+----+
1 row in set (0.00 sec)

mysql> insert into a(id) VALUES(null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into a(id) VALUES(null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(a_id) VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(a_id) VALUES(3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(a_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    1 |  3 |
|    1 |  4 |
|    2 |  1 |
|    3 |  1 |
+------+----+
6 rows in set (0.00 sec)

mysql>
\$\endgroup\$
  • \$\begingroup\$ I just realized LAST_INSERT_ID() outside of the trigger is not returning the last inserted id. I will try to find a solution, but if you know, please advise. Thanks \$\endgroup\$ – user1032531 Jul 8 '16 at 12:54
  • \$\begingroup\$ Maybe no solution possible :( stackoverflow.com/questions/38267637/… \$\endgroup\$ – user1032531 Jul 8 '16 at 13:29
  • \$\begingroup\$ Still works, but unfortunately will require two inserts. \$\endgroup\$ – user1032531 Jul 8 '16 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.