Database Design – Enforce an ‘At Least One’ Relationship

constraintdatabase-designnormalization

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.

Best Answer

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.

Related Question