如何为2个表创建外键？
假设我销售书籍和CD，所以我在数据库中都有这两个表，因为我不能同时使用它们。另外，我还有两个库存表，它们通过外键指向相应项目的表。像这样：
Let's say I sell books and CDs, so I have table in database for both of them, because I can't use same table for both. Also I have a stock table for both of them, which refers through foreign key to according item's table. Like this:
book book_stock
----- ----------
bookID book_stockID
title bookID
price
cd cd_stock
----- ----------
cdID cd_stockID
title cdID
price
但我想购买一只普通股表，根据 type 列引用两个项目的表。
But I'd like to have one general stock table, which referes to both item's tables depending on type column.
book stock
----- ----------
bookID stockID
title type
itemID
price
cd
-----
cdID
title
因此，如何做出这样的约束来检查多个表？
So, how to make such constraint which checks more than one table?
不幸的是，您不能创建这样的约束，因为前面的k ey始终基于一个父表。
You can't create such constraint, unfortunately, as the foreighn key is always based on one parent table.
您可以做的是创建触发器，该触发器将根据类型对输入数据进行测试，并回滚不适当的更改：
What you can do is to create the trigger which will test your input data according it's type, and rollback unupropriate changes:
- CREATE TRIGGER Syntax
- Trigger Syntax and Examples
类似这样的事情：
CREATE TRIGGER insert_stock_child
BEFORE INSERT
ON stock
FOR EACH ROW
BEGIN
IF new.type = 'BOOK_TYPE' AND (SELECT COUNT(*) FROM book
WHERE book.bookID = new.itemID)= 0
THEN
INSERT error_msg VALUES ('No such book!');
END IF;
END;
-- same logic for the cd table