let's say I have 3 tables "Goods", "Orders", "Goods in stock"

In the "Orders" we can only add products from the table "Goods", and in "Goods in stock" only products from "Orders" i.e. GoodsID - the primary key is the Goods table, the Orders table is the GoodsID column — Foreign key references Goods(GoodsID) but you cannot add the GoodsID int FOREIGN KEY REFERENCES ORDERS(GoodsID) Goods on stock table, since foreign key to external key references are not allowed: enter image description here key, only on the primary key. How to add the required limit?

    1 answer 1

    The answer to the question in the headline: it is impossible, because the foreign key must refer to a unique column (if formally, to a potential key). The foreign key itself is not required to be unique (and in most cases it cannot be so). For example, in terms of the question - several orders can be made for the same product.

    The answer to the question contained in the text: judging by the phrase «В "Заказы" мы можем добавить только товары из таблицы "Товары", А в "Товары на складе" только товары из "Заказы"» , in the table Goods on stock you need a link not at all on Orders(GoodsID) , but on Orders(OrdersID) .

    • Then it turns out 1 and the same product can be entered into the "goods in stock" table several times - ketchyn
    • This means that the foreign key in the Товары на складе table should refer to the Товары table (which is, in general, logical). Well, in addition to make a check ( CHECK CONSTRAINT ) for the presence of goods in orders. - Yaant