MySQL allow one relation or another relation, but not both for the same id

Question

I have the following schematic

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)

Where Book_Genre and Book_Classification act as join tables from Book. Technically with this current approach a Book can have a Genre and a Classification if both join tables are populated with the book_id (Book.id). The problem with this is that a book with a Classification is considered a NonFiction book, and a book with a Genre is considered to be a Fiction book.

I'm insistent on keeping the join tables as it allows me to give multiple Fiction books multiple genres, i.e. Action & Horror, and it allows me to give NonFiction books multiple classifications, such as their LOC numbers and their DeweyDecimal values.

Is it possible to limit a relation/reference to one table if a relation/reference to another table already exists?

To clarify:- I don't want a Book to be able to be given a classification if it already has a genre, and I don't want a book to be able to be given a genre if it already has a classification.

(Sorry for the naff schematic, not on my work PC)

This restriction can't be enforced in the MySQL database with a declarative constraint; to have MySQL enforce that kind of restriction, you'd have to add BEFORE INSERT/BEFORE UPDATE triggers on the two relationship tables. — spencer7593, Feb 23 '16 at 15:31
I wasn't even aware those triggers existed until somebody posted it here - and it's an ideal concept, so thank you and to the other guys for bringing them up as they're actually really useful for what I'm trying to do! @spencer7593 — Jack hardcastle, Feb 23 '16 at 15:33

score 2 · Accepted Answer · answered Feb 23 '16 at 15:22

How about a BEFORE INSERT TRIGGER on each Book_Genre and Book_Classification table. The trigger would check if a relation for the book_id already exists in the other table and, if so, will SIGNAL an error.

The trigger code could look something like this:

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

(Note: above code is NOT tested)

More info on Trigger syntax.

score 0 · Answer 2 · answered Feb 23 '16 at 15:19

You are, I guess, hoping to use a database constraint to enforce your cataloging rule that nonfiction books cannot have fiction genres and fiction books cannot have nonfiction classifications.

MySQL's built-in constraint capability is not sophisticated enough to handle your requirement in a simple way.

You might be able to build a query, and therefore a VIEW object, that omits genres when a classification is present and vice versa. But this would not prevent the creation of data that breaks your cataloging rule.

You might be able to build a trigger to enforce this.

But your best bet is to put a rule like this in your application logic.

I figured it would likely be a case of doing this in application logic, thought it probably best to ask. Anyway, I'll take a look at the use of `VIEW` objects as I've never looked at these before. And yes - your assumption towards the beginning of your answer is correct. — Jack hardcastle, Feb 23 '16 at 15:22

score 0 · Answer 3 · edited May 23 '17 at 12:23

0

As a solution to your problem (as it is involved 3 tables), you may write a trigger function before insertion into Book_Genre and Book_Classification tables which would check if any record with the same book id exists in another table and decide whether the record should be rejected.

It's a little bit tricky and you may refer to this question as an example.

edited May 23 '17 at 12:23

Community

1
1

answered Feb 23 '16 at 15:20

max

2,757
22
19

MySQL allow one relation or another relation, but not both for the same id

3 Answers3