How to make a composite key out of two foreign keys-CodePudding

I have three tables:

Table 1
   pizza
   country
   base 

Table 2
   ingredient
   type

Table 3
   pizza
   ingredient
   amount

In Table 1 primary key is pizza and in Table 2 ingredient. In Table 3, pizza and ingredient are foreign keys, that reference Table 1 and Table 2 respectively, and i want to make them into a composite key.

Here's what i tried:

CREATE TABLE Table3(
pizza varchar(12),
ingredient varchar(12),
amount int,
CONSTRAINT FK_pizzaRecipe FOREIGN KEY (pizza)
REFERENCES Table1(pizza),
CONSTRAINT FK_ingredientBase FOREIGN KEY (ingredient)
REFERENCES Table2(ingredient), 

CONSTRAINT pk_myConstraint PRIMARY KEY (pizza, ingredient)
);

But i'm not sure whether it's a correct implementation.

CodePudding user response：

No, it doesn't make sense to assemble a single composite FK. Those are two separate foreign key constraints, unrelated to each other.

Your table is perfectly correct as:

CREATE TABLE Table3(
  pizza varchar(12),
  ingredient varchar(12),
  amount int,
  CONSTRAINT FK_pizzaRecipe FOREIGN KEY (pizza)
  REFERENCES Table1(pizza),
  CONSTRAINT FK_ingredientBase FOREIGN KEY (ingredient)
  REFERENCES Table2(ingredient)
);