CODEWITHSUNDEEP
mysqlconstraints
w.k
w.k

Reputation: 8386

How to create foreign key for 2 tables?

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

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?

Upvotes: 1

Views: 78

Answers (1)

VMAtm
VMAtm

Reputation: 28366

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:

Something like this:

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

Upvotes: 1

Related Questions