Definición de clave compuesta con incremento automático en MySQL

Guión:

Tengo una tabla que hace referencia a dos claves externas, y para cada combinación única de estas claves externas, tiene su propia columna auto_increment. Necesito implementar una clave compuesta que ayudará a identificar la fila como única usando la combinación de estas tres (una clave externa y una columna autoincremento, y otra columna con valores no únicos)

Mesa:

CREATE TABLE `issue_log` ( `sr_no` INT NOT NULL AUTO_INCREMENT , `app_id` INT NOT NULL , `test_id` INT NOT NULL , `issue_name` VARCHAR(255) NOT NULL , primary key (app_id, test_id,sr_no) ); 

Por supuesto, tiene que haber algo mal con mi consulta, debido a que el error es:

ERROR 1075: definición incorrecta de la tabla; solo puede haber una columna auto y debe definirse como una clave

Lo que estoy tratando de lograr:

Tengo una tabla de aplicaciones (con app_id como clave principal), cada aplicación tiene un conjunto de problemas que resolver, y cada aplicación tiene varias pruebas (por lo que test_id col) El sr_no col debe incrementarse para app_id y test_id únicos.

es decir, los datos en la tabla deben verse así:

enter image description here

El motor de la base de datos es InnoDB. Quiero lograr esto con la mayor simplicidad posible (es decir, evitar desencadenantes / procedimientos si es posible, lo que se sugirió para casos similares en otras Preguntas).

No puede hacer que MySQL haga esto automáticamente para las tablas de InnoDB; necesitará usar un disparador o procedimiento, o utilizar otro motor de DB como MyISAM. El incremento automático solo se puede realizar para una sola clave principal.

Algo como lo siguiente debería funcionar

 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 ; 

Puede hacer esto con los motores myISAM y BDB. InnoDB no es compatible con esto. Cita del manual de referencia de MySQL 5.0.

Para las tablas MyISAM y BDB, puede especificar AUTO_INCREMENT en una columna secundaria en un índice de varias columnas. En este caso, el valor generado para la columna AUTO_INCREMENT se calcula como MAX (auto_increment_column) + 1 WHERE prefix = given-prefix.

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Puede usar una clave compuesta única para sr_no , app_id y test_id . No puede usar incremental en sr_no ya que no es único.

 CREATE TABLE IF NOT EXISTS `issue_log` ( `sr_no` int(11) NOT NULL, `app_id` int(11) NOT NULL, `test_id` int(11) NOT NULL, `issue_name` varchar(255) NOT NULL, UNIQUE KEY `app_id` (`app_id`,`test_id`,`sr_no`) ) ENGINE=InnoDB ; 

He comentado una violación de restricción única en sql fiddle para demostrar (eliminar # en la línea 22 del esquema y reconstruir el esquema)

No entiendo completamente su requisito de incremento en la columna test_id , pero si desea una secuencia ~ autoincrement que se reinicie en cada combinación única de ( app_id , test_id ), puede hacer un INSERT … SELECT FROM the same table, como asi que:

 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 */ 

Esto supone una definición de tabla sin una columna AUTO_INCREMENT declarada. Básicamente estás emulando el comportamiento de autoincrement con la cláusula IFNULL (MAX ()) + 1, pero la emulación manual funciona en columnas arbitrarias, a diferencia de la autoincrementación incorporada.

Tenga en cuenta que INSERT … SELECT es una consulta única que garantiza la atomicidad de la operación. InnoDB bloqueará el índice apropiado, y muchos procesos concurrentes pueden ejecutar este tipo de consultas sin dejar de producir secuencias no conflictivas.