2

The Many-to-many (data model) Wikipedia article uses the example of Authors and Books:

For example, think of A as Authors, and B as Books. An Author can write several Books, and a Book can be written by several Authors.

In a relational database management system, such relationships are usually implemented by means of an associative table (also known as cross-reference table), say, AB with two one-to-many relationships A -> AB and B -> AB. In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).

With constraints, how would you enforce that a Book must have at least one Author?

Notes:

  • For this example, there is no such thing as a "primary" Author for the Book.
  • Answer in SQL (any RDBMS) or in general terms.
3

How to do this in SQL, using only DDL (FOREIGN KEY and UNIQUE constraints):

CREATE TABLE author
( author_id INT NOT NULL,
  author_name VARCHAR(100),
  CONSTRAINT author_pk
    PRIMARY KEY (author_id)
) ;

CREATE TABLE book
( book_id INT NOT NULL,
  book_title VARCHAR(100),
  CONSTRAINT book_pk
  PRIMARY KEY (book_id)
) ;

CREATE TABLE book_author  
( book_id INT NOT NULL,
  author_id INT NOT NULL,
  CONSTRAINT book_author_pk
    PRIMARY KEY (book_id, author_id),
  CONSTRAINT book__book_author_fk
    FOREIGN KEY (book_id) REFERENCES book (book_id),
  CONSTRAINT author__book_author_fk
    FOREIGN KEY (author_id) REFERENCES author (author_id)
) ;

So far this has implemented only a common many-to-many relationship between books and authors. Which means that a book can have 0, 1 or many authors and an author can have 0, 1 or many books.

If we want to have the strict "a book must have at least 1 author", we can add another column in book and the additional constraint:

ALTER TABLE book
  ADD COLUMN author_id INT NOT NULL,
  ADD CONSTRAINT book_must_have_at_least_1_author_fk
    FOREIGN KEY (book_id, author_id) 
    REFERENCES book_author (book_id, author_id) ;

Notes:

  • For the above constraints to work we need a DBMS that has implemented deferrable constraints (eg. Postgres, Oracle) or can insert into / delete from multiple tables in one statement (Postgres). Without any of these, we can relax some of the constraints, for example lifting the NOT NULL from book (author_id) and doing the insert (say of a new book) in 3 steps:

    • insert into book (with author_id null)
    • insert into book_author
    • update book (setting author_id accordingly).

    We can then wrap the above steps in a transaction/procedure and then restrict access to the tables only via these transactions/procedures (one procedure for each operation, insert new book and its authors, delete book and its authors, update author, remove author from book, etc).

    If not entirely obvious these procedures may get a bit complicated, for example when the "primary" author of a book has to be removed, as the procedure has to search the remaining authors and update book.author_id accordingly or fail/deny the removal if there in no other author left.

  • Another option - since we mentioned procedures - is to use them from the beginning for all insert/update/delete operations. If designed and tested correctly, they can enforce all the constraints, allowing to not declare any foreign keys.

  • Would using triggers on INSERT book and UPDATE or DELETE book_author (rather than creating book.author_id) be considered bad practice? – Steven Mar 22 '17 at 19:39
  • I'm not sure how an INSERT trigger on book would work. If I insert a book, how would the trigger know which author to add in the many-to-many table? – ypercubeᵀᴹ Mar 22 '17 at 19:43
  • I want to ensure when a book is inserted that a book_author must also be inserted at the same time. I also want to ensure when a book_author is updated or deleted that another book_author for that book remains. – Steven Mar 22 '17 at 19:54
  • Yes, I understand. A trigger would work for the update/delete cases. But I don't see how it could work for the insert case. – ypercubeᵀᴹ Mar 22 '17 at 19:57
  • You have provided several options each with trade-offs. I will have to decide which works best for my application. – Steven Mar 22 '17 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.