MySQL 允许一种关系或另一种关系，但对于同一个 id 则不允许两者

Question

我有如下示意图

Book
    id INT(12), // PK
    title VARCHAR(50),
    isbn VARCHAR(50),
    price VARCHAR(8),
    image VARCHAR(200)

Book_Genre
    book_id INT(12), // COMPOUND KEY also references Book.id
    genre_id INT(12) // COMPOUND KEY also references Genre.id  

Book_Classification
    book_id INT(12), // PK + FK, references Book.id
    classification_id INT(12) // FK, references classification.id

Genre
    id INT(12), // PK
    title VARCHAR(50) 

Classification
    id INT(12), // PK
    classification_type VARCHAR(20),
    classification_description VARCHAR(200),
    classification_value VARCHAR(12)

Book_Genre 和 Book_Classification 充当 join 来自 Book 的表。从技术上讲，如果两个 join 表都用 book_id (Book.id) 填充，那么使用当前方法，一本书可以有一个流派和一个分类。这样做的问题是，带有分类的书被认为是非小说类书籍，而带有流派的书被认为是小说类书籍。

我坚持保留 join 表，因为它允许我为多本小说类书籍提供多种类型，即 Action 与恐怖，并且它允许我为非小说类书籍提供多种分类，例如它们LOC 数及其 DeweyDecimal 值。

如果对另一个表的关系/引用已经存在，是否可以限制对一个表的关系/引用？

澄清一下:- 我不希望一本书在已经有流派的情况下能够被赋予分类，我也不希望一本书在已经有流派的情况下能够被赋予流派分类。

(抱歉，我的工作 PC 上没有原理图)

Answer 1

BEFORE INSERT怎么样TRIGGER在每个 Book_Genre 和 Book_Classification 表上。触发器将检查 book_id 的关系是否已存在于另一个表中，如果存在，将 SIGNAL一个错误。

触发代码可能如下所示:

CREATE TRIGGER ins_Book_Classification BEFORE INSERT ON Book_Classification
FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM Book_Genre WHERE book_id = NEW.book_id) > 0 THEN
        SIGNAL SQLSTATE '90001'
        SET MESSAGE_TEXT = 'Reference already exists in Book_Genre';
    END IF;
END

(注意:以上代码未经测试)

有关 Trigger syntax 的更多信息.