[SOLVED] How to manage the insert on a junction table

How to manage the insert on a junction table

I have two tables: ingredient and recipe

recipe table
-----------------------
|recipe_id   |name  |
-----------------------
|1           |Pasta |
|2           |Pizza |
|3           |Fish  |


ingredient table
-------------------------------
|ingredient_id   |name        |
-------------------------------
|1               |Spaghetti   |
|2               |Salmon      |
|3               |Tomato sauce|

Then I have a junction table for these two tables.

---------------------------
|id|recipe_id|ingredient_id|
---------------------------
|1 |1        |1            |
|2 |2        |3            |
|3 |3        |2            |

It's not clear for me how I should insert data in the junction table. I mean, do I have to insert the recipe_id and ingredient_id manually with a simple INSERT? Or do I have to use the relations with the other two tables in some way?

Solution

Inserting a full relationship into all three tables will generally require three separate insert statements. You may deal with this problem by performing all inserts within a single logical transaction. For example:

BEGIN;  -- start transaction

INSERT INTO recipe (recipe_id, name) VALUES (1, 'Pasta');
INSERT INTO ingredient (ingredient_id, name) VALUES (1, 'Spagehetti');
INSERT INTO junction_table (recipe_id, ingredient_id) (1, 1);

COMMIT; -- end transaction

In practice, if the recipe_id and ingredient_id columns be serial/auto increment, then you would omit them from the insert statement. If you needed to find the auto generated ID values for these tables after the insert, you could use the pg_get_serial_sequence() function, see here.