mysql - auto_increment - insert into primary key

Definiendo Clave Compuesta con Auto Incremento en MySQL

Question

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 de autoincremento. Necesito implementar una clave compuesta que ayude a identificar la fila como única usando la combinación de estas tres (una clave externa y una columna de 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 que se produce es:

ERROR 1075: Definición de tabla incorrecta; solo puede haber una columna automática y debe definirse como una clave

Lo que estoy tratando de lograr:

Tengo una tabla de aplicaciones (con app_id como su clave principal), cada aplicación tiene un conjunto de problemas por resolver, y cada aplicación tiene múltiples pruebas (por lo tanto, la columna test_id) La columna sr_no debe incrementarse para app_id único y test_id.

Es decir, los datos en la tabla deben verse como:

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

Answer 1

MySQL no puede hacer esto automáticamente para las tablas de InnoDB, ya que necesitaría usar un activador o un procedimiento, o utilizar otro motor de base de datos como MyISAM. El incremento automático solo se puede hacer para una clave principal única.

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 ;

Answer 2

No entiendo completamente su requisito de incremento en la columna test_id , pero si desea una secuencia de autoincremento que se reinicia en cada combinación única de ( app_id , test_id ), puede hacer un INSERT ... SELECT FROM de la misma tabla, 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á emulando el comportamiento de autoincremento con la cláusula IFNULL (MAX ()) + 1, pero la emulación manual funciona en columnas arbitrarias, a diferencia del autoincremento incorporado.

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

Answer 3

Puede usar una clave compuesta unique para sr_no , app_id y test_id . No puede usar incremental en sr_no ya que esto 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 la violación de una restricción única en el violín de sql para demostrar (eliminar # en la línea 22 del esquema y reconstruir el esquema)

Answer 4

Puedes hacer esto con los motores myISAM y BDB. InnoDB no soporta 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 DONDE prefijo = dado-prefijo.

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