SQLite - implementing Upserting on a Composite Key Leading to Integrity Constraints
I'm working with SQLite 3.36.0 and trying to implement an upsert operation on a table that has a composite primary key... The table is defined as follows: ```sql CREATE TABLE orders ( order_id INTEGER, product_id INTEGER, quantity INTEGER, PRIMARY KEY (order_id, product_id) ); ``` I'm attempting to insert or update records based on the combination of `order_id` and `product_id`. My upsert statement looks like this: ```sql INSERT INTO orders (order_id, product_id, quantity) VALUES (1, 2, 5) ON CONFLICT(order_id, product_id) DO UPDATE SET quantity = quantity + 1; ``` However, when I execute this, I receive the following behavior: ``` behavior: UNIQUE constraint failed: orders.order_id, orders.product_id ``` To troubleshoot, I've verified that the combination of `order_id` and `product_id` does indeed exist in the table when I'm trying to update it. I even queried the table right before the insert to confirm it: ```sql SELECT * FROM orders WHERE order_id = 1 AND product_id = 2; ``` Interestingly, when I manually run the update statement separately, it works fine: ```sql UPDATE orders SET quantity = quantity + 1 WHERE order_id = 1 AND product_id = 2; ``` I also tried wrapping the upsert in a transaction: ```sql BEGIN TRANSACTION; INSERT INTO orders (order_id, product_id, quantity) VALUES (1, 2, 5) ON CONFLICT(order_id, product_id) DO UPDATE SET quantity = quantity + 1; COMMIT; ``` This did not resolve the scenario. I suspect it could be related to the timing of the constraints being checked or some other transaction-related behavior in SQLite. Can anyone guide to understand why this upsert is failing and how I might resolve it? Any insights into handling composite keys in this context would be greatly appreciated. Is there a better approach? Is there a better approach?