Make sure that such a field combination exists in another table when inserting

Question

Table A:

id | name | user_id

Table B:

id | type

Table A_B

id | table_a_id | table_b_id | user_id

Table A_B is a standard linking table for a many-to-many relationship except that probably using a surrogate key is unnecessary here.

What I also want to achieve is that inserting a row into Table A_B is impossible if there is no such (id, user_id) combination in Table_A.

Of course, I have foreign keys for table_a_id and table_b_id but that only guarantees that there are matching entries in the corresponding tables. I also added a UNIQUE constraint on (table_a_id, table_b_id, user_id) but again this doesn't prevent from inserting a row that shouldn't be inserted.

What is the best way to achieve this behavior? So far the only feasible option I can think of is checking the condition programmatically by selecting from Table_A first, which doesn't however seem smart.

What are some other options?

EDIT: One idea based on the comments (user_id is removed from the junction table schema)

        INSERT INTO a_b
            (
              table_a_id,
              table_b_id
            )
            SELECT id, user_id, <input_table_b_id>
            FROM table_a
            WHERE id = <input_table_a_id> AND user_id = <input_user_id>
   

Answer 1

What I also want to achieve is that inserting a row into Table A_B is impossible if there is no such (id, user_id) combination in Table_A.

Am I misunderstanding something or did you not consider

FOREIGN KEY (table_a_id, userId) REFERENCES Table_A(id, userId)