PostgreSQL> column integer[] value check from another table(id)

Question

I want to build an application which use postgresql. It should be a simple question but I still cannot find the answer quickly enough ; maybe would you help me to do this?

I have a table of ingredients. id PK, name VARCHAR

I want to create a table of recipies where I got an id and integer[] But I don't find how to do this: every element of integer[] should be referenced in ingredients.

I can do it with a simple integer REFERENCE ingredients(id) but not with an array.

Thanks for the help! :)

score 0 · Answer 1 · answered Dec 24 '22 at 19:18

0

What you are looking for is a cross-reference table or an XREF table.

How it would work:

TABLE 1: ingredients

id

name

TABLE 2: recipes

id

name

description

TABLE 3: recipes_ingredients_xref

recipes_ingredients_xref_id

recipe_id

ingredient_id

In table 3 (recipes_ingredients_xref), you link multiple ingredients with a single recipe.

answered Dec 24 '22 at 19:18

Clifford Cheefoon

215
1
8

Thanks for the quick answer. I did it with 2 tables items(id PK, name) and recipes(id_craft ref to items, id_ingredient ref to items, qty). – MeowCatTheWhiteknight Dec 26 '22 at 01:47

PostgreSQL> column integer[] value check from another table(id)

1 Answers1