如何为2个表创建外键？ [英] How to create foreign key for 2 tables?

问题描述

假设我销售书籍和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

这篇关于如何为2个表创建外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！