Defining Composite Key with Auto Increment in MySQL
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_logFOR 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 ;
You can do this with myISAM and BDB engines. InnoDB does not support this. Quote from MySQL 5.0 Reference Manual.
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix.
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
I don't fully understand your increment requirement on the test_id
column, but if you want an ~autoincrement sequence that restarts on every unique combination of (app_id
, test_id
), you can do an INSERT ... SELECT FROM the same table, like so:
mysql> INSERT INTO `issue_log` (`sr_no`, `app_id`, `test_id`, `issue_name`) SELECT IFNULL(MAX(`sr_no`), 0) + 1 /* next sequence number */, 3 /* desired app_id */, 1 /* desired test_id */, 'Name of new row' FROM `issue_log` /* specify the table name as well */ WHERE `app_id` = 3 AND `test_id` = 1 /* same values as in inserted columns */
This assumes a table definition with no declared AUTO_INCREMENT column. You're essentially emulating autoincrement behavior with the IFNULL(MAX()) + 1 clause, but the manual emulation works on arbitrary columns, unlike the built-in autoincrement.
Note that the INSERT ... SELECT being a single query ensures atomicity of the operation. InnoDB will gap-lock the appropriate index, and many concurrent processes can execute this kind of query while still producing non-conflicting sequences.