0

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>
   
4
  • If you want any combination of (table_a_id, user_id) you add to table a_b to have a relevant row (id, user_id) in table a then you need to add a FOREIGN KEY like that. CONSTRAINT a_b__ref__a__fk FOREIGN KEY (table_a_id, user_id) REFERENCES a (id, user_id) - and the required UNIQUE constraint on table a Sep 26, 2022 at 15:36
  • This makes sense, but it feels a bit awkward to create a UNIQUE constraint on (id, user_id) given that id is already unique. Or isn't that a bad practice?
    – Don Draper
    Sep 26, 2022 at 16:00
  • It's denormalization, yes. The other option is to remove user_if from table a_b. You can always get this info with a join. Sep 26, 2022 at 16:04
  • @ypercubeᵀᴹ, see my edit, please. Do you think this approach looks better? It's a bit hacky too but at least this way the query itself guarantees that we only consider an existing match from table_a to be inserted
    – Don Draper
    Sep 26, 2022 at 16:18

1 Answer 1

2

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)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.