👀 Views: 1 💬 Answers: 1 📅 Created: 2025-06-11

mysql insert duplicate-key sql

I'm migrating some code and I'm working with an unusual behavior when trying to use `INSERT ... ON DUPLICATE KEY UPDATE` with a composite unique key in MySQL 8.0. I have a table `orders` structured as follows: ```sql CREATE TABLE orders ( order_id INT NOT NULL, customer_id INT NOT NULL, order_date DATETIME, quantity INT, UNIQUE KEY unique_order (customer_id, order_id) ); ``` I'm trying to insert new records like this: ```sql INSERT INTO orders (order_id, customer_id, order_date, quantity) VALUES (1, 101, NOW(), 5) ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity); ``` However, I'm getting an behavior message: `behavior 1062 (23000): Duplicate entry '101-1' for key 'unique_order'`. This happens even when I believe there should be no conflict, as the combination of `customer_id` and `order_id` is unique for my inserts. I've also tried breaking down the process into individual inserts to check existing records and ensure that my conditions are met, but I keep hitting the same behavior. I've verified that there are no existing entries with `customer_id = 101` and `order_id = 1`. I have examined the underlying data and used debug logs to confirm that the data being passed is indeed unique. I even ran a select query to assert this: ```sql SELECT * FROM orders WHERE customer_id = 101 AND order_id = 1; ``` Nothing is returned, which leads me to believe that there are no conflicting records. I'm aware that MySQL uses locking internally for this operation, but I suspect there might be some implicit constraints or triggers affecting this behavior. Anyone encountered something similar? What am I missing here?