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

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.

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.

based on the fields in your tables you cannot determine the last inserted rows definitely.

As you say either add in a primary key or alternatively as you dont want to do that you could add in a timestamp field which adds sysdate when data is inserted. However I think that if you can add a field, adding in a PK will make finding the last entry easier as you just look for the MAX PK to identify the most recent entry.

All you need is an auto increment field in ORDERS. Then to return the most recent entry you would do:

     SELECT MAX(your auto incrementing field) from orders.