How to create a "facade" table?

A legacy database contains a join table which links tables table1 and table2, and contains just two foreign keys:

TABLE_ORIG:
table1_id
table2_id

In order to utilize this table using JPA I would need to create a surrogate primary key to the link table. However, the existing table must not be modified at all.

I would like to开发者_C百科 create another table which would contain also a primary key in addition to the foreign keys:

TABLE_NEW:
id
table1_id
table2_id

All changes to TABLE_ORIG should be reflected in TABLE_NEW, and vice versa.

Is this doable in mysql?

What you want is called a "view".

"In order to utilize this table using JPA I would need to create a surrogate primary key to the link table."

You have stated the very reason why you should not want to 'utilize this table using JPA'.

Apart from that :

"Is this doable in mysql?"

I suppose that it should be possible to use triggers to keep the two tables in sync (any insert in TABLE_ORIG causes an insert in TABLE_NEW, and perhaps vice versa too, and same for the deletes).