How to make a single composite key for different tables?

Question

MySQL

There is a database of recipes. The recipe table indicates the ingredient and its quantity. An ingredient can be either a product (milk, sugar, egg, flour, etc.) or a semi-finished product (wafer cakes, cake mix, or even a product, but a specific manufacturer, for example, dry yeast such and such).
There is a table of semi-finished products, there is a table of products. An ingredient can be an entry from either one or another table. While I'm getting out of the situation like this: There is a field ingridientId and ingridientType (int) - if ingridientType == 1, then I think that ingridientId is a ProductId from the Products table, and if ingridientType == 2, then I think that ingridientId is a semimanufactureId from the table Semimanufactures.
All responsibility for tracking what and how in this case lies with the developer. I want to change it.
The question is, is it possible to make the database engine itself, with the composite key
ingridientType == 1 + ingridientId, have a connection with the Products table, and
ingridientType == 2 + ingridientId have a connection with the Semimanufactures table
And one more thing: If this is still possible, then how should the data type (structure) look like in .NET? dynamic? Single ancestor for ProductType and SemimanufactureType descendants?
Another option was to make an intermediate table in the Ingridients database,
which would contain its own id and foreign keys for Products (ProductId) and Semimanufactures (SemimanufactureId) with the condition that only one of the ProductId or SemimanufactureId fields can be non-null in one record. and in the recipe table, specify exactly ingridientId

Answer 1

I would store products and semi-finished products in the same table with an additional Type field. Keep only common attributes in the table, and put the differences in a separate EAV table.
Your version with an intermediate table is also good, but a little less flexible, and foreign keys on null values ​​are not a good solution.