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?
CodePudding user response：
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.
CodePudding user response：
The tables recipe and ingredient are independent tables and data can be inserted by simple queries:
insert into recipe (name) values ('Pasta'), ('Pizza '), ('Fish');
insert into ingredient (name) values ('Spaghetti'), ('Salmon '), ('Tomato sauce');
The junction table can be filled by select data for both tables like:
insert into recipe_ingredient (recipe_id, ingredient_id)
select recipe_id, ingredient_id
from recipe, ingredient
where recipe.name = 'Pasta' and ingredient.name in ('Spaghetti', 'Tomato sauce');