I am creating a recipe app where you can add ingredients to the recipe. I want the ingredient table to have columns for name, quantity, and measurment. And then connect it to the recipe table. Is it possible to have a array with foreign keys in my recipe table?
If not, how else do you solve issues like this?
Not a good idea to create a foreign key on a column with array type. You should rather create one table for the ingredients, one table for the recipes, and one intermediate table for the many-to-many relationship between ingredients and recipes, with the quantity of the ingredient in the recipe :
CREATE TABLE IF NOT EXISTS ingredient (name varchar primary key) ;
CREATE TABLE IF NOT EXISTS recipe(name varchar primary key) ;
CREATE TABLE IF NOT EXISTS recipe_composition
( recipe_name varchar
, ingredient_name varchar
, ingredient_quantity double precision
, ingredient_unit_of_measure varchar
, PRIMARY KEY (recipe_name, ingredient_name)
, FOREIGN KEY recipe_name REFERENCES recipe(name) ON UPDATE CASCADE ON DELETE RESTRICT
, FOREIGN KEY ingredient_name REFERENCES ingredient(name) ON UPDATE CASCADE ON DELETE RESTRICT
) ;
It is possible, but that is a very bad idea and will make you unhappy:
any query that joins the tables will become more complicated and is likely to perform worse
you cannot guarantee referential integrity with a foreign key
Create a junction table recipe_ingredient that stores the relationship between the two.
that is a very bad idea and will make you unhappy... Really ? Why ?