PostgreSQL array of foreign keys-CodePudding

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?

CodePudding user response：

It is possible, but that is a very bad idea and will make you unhappy.

Create a junction table recipe_ingredient that stores the relationship between the two.

CodePudding user response：

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