ERROR 1062 (23000): Duplicate entry 'DP1' for key 'PRIMARY'

Question

I have a table distributor_warehouse which has an auto-incremented coulmn dpID. I cannot insert row after inserting first row. I had seen similar queries like this but everywhere got answers that the column needs to be auto-incremented which is already done in mine.

mysql> desc distributor_warehouse;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| dpID       | varchar(12)   | NO   | PRI |         |       |
| cpID       | varchar(12)   | YES  |     | NULL    |       |
| QTY        | int(6)        | YES  |     | NULL    |       |
| COST_PRICE | decimal(10,2) | YES  |     | NULL    |       |
| SELL_PRICE | decimal(10,2) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> select * from distributor_warehouse;
+------+------+------+------------+------------+
| dpID | cpID | QTY  | COST_PRICE | SELL_PRICE |
+------+------+------+------------+------------+
| DP1  | CP5  |   10 |    3000.00 |    3100.00 |
+------+------+------+------------+------------+
1 row in set (0.00 sec)

mysql> INSERT INTO distributor_warehouse(cpID,QTY,COST_PRICE,SELL_PRICE) VALUES ('CP6',150,999,1500);
ERROR 1062 (23000): Duplicate entry 'DP1' for key 'PRIMARY'

dpID is made auto-incremented by using a trigger.

This is my table. I had inserted Row 1 successfully then while inserting Row 2 the problem is occurring.

mysql> desc autoid;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| ID    | int(10) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

| tg_distributor_warehouse_id | INSERT | distributor_warehouse | BEGIN
INSERT INTO autoid VALUES(NULL);
SET NEW.dpID = CONCAT('DP',LPAD(LAST_INSERT_ID(),1,''));
END |

Answer 1

Your code appears to be fine

drop table if exists t,t1;
create table t(dpid varchar(12), cpid varchar(12), qty int,cost_price decimal(10,2),sell_price decimal(10,2));
create table t1(id int auto_increment primary key);

drop trigger if exists t;
delimiter$$
create trigger t before insert on t
for each row
begin
    insert into t1 values (null);
    SET NEW.dpID = CONCAT('DP',LPAD(LAST_INSERT_ID(),1,''));
end$$
delimiter;

INSERT INTO t(cpID,QTY,COST_PRICE,SELL_PRICE) VALUES ('CP6',150,999,1500);


INSERT INTO t(cpID,QTY,COST_PRICE,SELL_PRICE) VALUES ('CP6',150,999,1500);
select * from t;

+------+------+------+------------+------------+
| dpid | cpid | qty  | cost_price | sell_price |
+------+------+------+------------+------------+
| DP1  | CP6  |  150 |     999.00 |    1500.00 |
| DP2  | CP6  |  150 |     999.00 |    1500.00 |
+------+------+------+------------+------------+
2 rows in set (0.00 sec)