Clara Gonçalves Clara Gonçalves - 3 months ago 7
MySQL Question

Create a mysql table that references foreign key and add several labels/tags of that key inside one column

I want to create a table with this fields:

CREATE TABLE recipes(
id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
image TEXT,
title TEXT,
related_product1 TEXT,
related_product2 TEXT,
related_product3 TEXT,
FOREIGN KEY(related_product1) REFERENCES products(product_name),
FOREIGN KEY(related_product2) REFERENCES products(product_name),
FOREIGN KEY(related_product3) REFERENCES products(product_name)
);


Is there a way to create that foreign key fields in one column and add them as "labels/tags"? I don't want to limit the amount of related products.
thanks!

Answer

You have to make a second table with Relations, where you save both the id's for each Table.

(Not sure if the syntax is right like this, I normaly generate from MySQLWorkbench)

CREATE TABLE recipes(
id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
image TEXT,
title TEXT)

CREATE TABLE relations(
recipe INTEGER PRIMARY KEY NOT NULL,
pruduct INTEGER PRIMARY KEY NOT NULL,
FOREIGN KEY(related_product) REFERENCES products(product_name),
FOREIGN KEY(related_recipe) REFERENCES recipes(id)
)
Comments