Defining Composite Key with Auto Increment in MySQL

by

You can’t have MySQL do this for you automatically for InnoDB tables – you would need to use a trigger or procedure, or user another DB engine such as MyISAM. Auto incrementing can only be done for a single primary key.

Something like the following should work

DELIMITER $$

CREATE TRIGGER xxx BEFORE INSERT ON issue_log
FOR EACH ROW BEGIN
    SET NEW.sr_no = (
       SELECT IFNULL(MAX(sr_no), 0) + 1
       FROM issue_log
       WHERE app_id  = NEW.app_id
         AND test_id = NEW.test_id
    );
END $$

DELIMITER ;

More Related Contents:

  1. MySQL AUTO_INCREMENT does not ROLLBACK
  2. Prevent auto increment on MySQL duplicate insert
  3. Why does MySQL autoincrement increase on failed inserts?
  4. MyISAM versus InnoDB [closed]
  5. How to set initial value and auto increment in MySQL?
  6. How do I quickly rename a MySQL database (change schema name)?
  7. Is there a REAL performance difference between INT and VARCHAR primary keys?
  8. Get current AUTO_INCREMENT value for any table
  9. 1114 (HY000): The table is full
  10. Restore table structure from frm and ibd files
  11. What’s the difference between MyISAM and InnoDB? [duplicate]
  12. How to handle fragmentation of auto_increment ID column in MySQL
  13. Why is MySQL InnoDB insert so slow?
  14. MySql: MyISAM vs. Inno DB! [closed]
  15. UUID performance in MySQL?
  16. Add Auto-Increment ID to existing table?
  17. How to fill in the “holes” in auto-increment fields?
  18. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  19. Is there any performance gain in indexing a boolean field?
  20. How does MySQL Auto Increment work?
  21. MySQL InnoDB: autoincrement non-primary key
  22. How do I rename a MySQL database (change schema name)?
  23. MySQL Integer vs DateTime index
  24. How do I lock on an InnoDB row that doesn’t exist yet?
  25. mysql Failed to read auto-increment value from storage engine
  26. “Unknown column in ‘field list'”, but column does exist
  27. Should I always prefer MySQL InnoDB over MyISAM?
  28. Composite Primary Key performance drawback in MySQL
  29. make an ID in a mysql table auto_increment (after the fact)
  30. How can I rebuild indexes and update stats in MySQL innoDB?

Leave a Comment