当调用存储过程时,我在表"telefono":Error Code: 1062. Duplicate entry '84-1' for key 'PRIMARY'
上得到一个错误,并且它们在表中的顺序相反。
例如,如果我做call updateProvider("1", "Terra1", "Ana", "Mora", "Escobar", "31312323", "11111111");
,并且数字31312323和11111111已经在表telefono_proveedor
中以相反的顺序具有条目,则抛出错误。
错误对应于此更新
UPDATE telefono_proveedor SET id_telefono = var_id_phone1
WHERE id_telefono = old_id_phone1 AND id_proveedor = var_id_prov;
UPDATE telefono_proveedor SET id_telefono = var_id_phone2
WHERE id_telefono = old_id_phone2 AND id_proveedor = var_id_prov;
表的SQL:
CREATE TABLE `telefono` (
`id_telefono` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tipo` enum('casa','movil','oficina') NOT NULL,
`numero` varchar(20) NOT NULL,
PRIMARY KEY (`id_telefono`),
UNIQUE KEY `id_telefono_UNIQUE` (`id_telefono`),
UNIQUE KEY `numero_UNIQUE` (`numero`),
UNIQUE KEY `numero` (`numero`)
) ENGINE=InnoDB AUTO_INCREMENT=119 DEFAULT CHARSET=utf8;
这是表格的图表
存储过程:
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateProvider`(
IN var_id_prov INT(10), IN var_provider VARCHAR(20),
IN var_rep_name VARCHAR(20), IN var_rep_last_name VARCHAR(20),
IN var_rep_last_name2 VARCHAR(20), IN var_phone1 VARCHAR(20),
IN var_phone2 VARCHAR(20)
)
BEGIN
DECLARE old_id_phone1 INT(10);
DECLARE old_id_phone2 INT(10);
DECLARE var_id_phone1 INT(10);
DECLARE var_id_phone2 INT(10);
SELECT id_telefono INTO old_id_phone1 FROM telefono_proveedor
WHERE id_proveedor = var_id_prov ORDER BY id_telefono ASC LIMIT 1;
SELECT id_telefono INTO old_id_phone2 FROM telefono_proveedor
WHERE id_proveedor = var_id_prov ORDER BY id_telefono ASC LIMIT 1,1;
INSERT IGNORE INTO telefono (tipo, numero) VALUES ('oficina', var_phone1);
INSERT IGNORE INTO telefono (tipo, numero) VALUES ('oficina', var_phone2);
SELECT id_telefono INTO var_id_phone1 FROM telefono WHERE numero = var_phone1;
SELECT id_telefono INTO var_id_phone2 FROM telefono WHERE numero = var_phone2;
UPDATE telefono_proveedor SET id_telefono = var_id_phone1
WHERE id_telefono = old_id_phone1 AND id_proveedor = var_id_prov;
UPDATE telefono_proveedor SET id_telefono = var_id_phone2
WHERE id_telefono = old_id_phone2 AND id_proveedor = var_id_prov;
UPDATE proveedor SET nombre_proveedor = var_provider, nombre_representante = var_rep_name,
apellido1_representante = var_rep_last_name, apellido2_representante = var_rep_last_name2
WHERE id_proveedor = var_id_prov;
END
为什么要这样修改主键?你的主键一定会遇到波动性那么大的问题。
您似乎正在尝试将主值更新为已存在的值。
-- This will create a brand new row with a new/unique id_telefono.
INSERT IGNORE INTO telefono (tipo, numero) VALUES ('oficina', var_phone1);
-- You are selecting the id_telefono that use just inserted.
SELECT id_telefono INTO var_id_phone1 FROM telefono WHERE numero = var_phone1;
-- Now you are updating another row to the id_telefono that you just inserted,
-- resulting in the duplicate primary key error.
/*Actualizo la tabla telefono_proveedor */
UPDATE telefono_proveedor SET id_telefono = var_id_phone1
WHERE id_telefono = old_id_phone1 AND id_proveedor = var_id_prov;
我建议您保留主键,并且在创建后不要修改它们。修改表中的其他列以捕获您的业务需求。
通过将更新更改为删除然后插入来解决。发生此错误的原因是,我试图用属于主键的另一个记录中已经存在的值更新一个记录。现在,通过删除和插入,顺序无关紧要,因为所有记录都将被删除并再次插入。
原件:
UPDATE telefono_proveedor SET id_telefono = var_id_phone1
WHERE id_telefono = old_id_phone1 AND id_proveedor = var_id_prov;
UPDATE telefono_proveedor SET id_telefono = var_id_phone2
WHERE id_telefono = old_id_phone2 AND id_proveedor = var_id_prov;
已修复:
DELETE FROM telefono_proveedor WHERE id_proveedor = var_id_prov;
INSERT INTO telefono_proveedor (id_telefono, id_proveedor) VALUES (var_id_phone1, var_id_prov);
INSERT INTO telefono_proveedor (id_telefono, id_proveedor) VALUES (var_id_phone2, var_id_prov);