如何将最后插入的两个其他表的唯一组合外键作为单个键

Question

Lets take three tables... USER, PRODUCT, ORDERS.

USER(user_id,name); 
PRODUCT(product_id,price);  
ORDERS(user_id,product_id,order_code,paid);

here we have user_id and product_id in ORDERS table as foreign key for USER and PRODUCT tables which together makes a unique index.

Oright..

Assume that there are data rows in user and product tables.
So, I'm inserting into ORDERS table

INSERT INTO ORDERS(user_id,product_id,paid) VALUES ('2','1','true');

SELECT @lid:= /* some function which can return last inserted combination of foreign keys as single identifier */;

UPDATE ORDERS SET order_code=concat(
    substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)
             )
     WHERE /*in here combined foreign key*/ = @lid;

I want to get the last inserted id (as in LAST_INSERT_ID) of two foreign keys as a single key which can be used to update the table.

Now, I know that I can accomplish this by introducing a primary key to ORDERS table which will solve the problem as such. But, I don't want to do that.

I have also searched this type of questions but it didn't solve my problem.

Any help is appreciable. Thanks in Advance.

Answer 1

Well, this might work... create a column with auto_increment named inc then

INSERT INTO ORDERS(user_id,product_id,paid) VALUES ('2','1','true');

SELECT @lid:= select max(inc) from ORDERS;

UPDATE ORDERS SET order_code=concat(
    substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)
             )
     WHERE inc = @lid;

your are done.