1

Say I have the following tables representing a three-level constrained relation:

CREATE TABLE `books` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `chapters` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `book_id` bigint unsigned NOT NULL,
  `number` tinyint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `chapters_book_id_number_uk` (`book_id`,`number`),
  CONSTRAINT `chapters_book_id_fk` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `pages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `chapter_id` bigint unsigned NOT NULL,
  `number` tinyint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `pages_chapter_id_fk` FOREIGN KEY (`chapter_id`) REFERENCES `chapters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

Similar to how chapters.number is unique to the given book (ie, no more than one "Chapter 1" in a book), I'd like pages.number to be unique to the given book.

Does MySQL offer any way to build a UNIQUE KEY with reference to a column in a table that there must be connection to? ie.:

UNIQUE KEY `pages_book_id_number_uk` (`chapter`.`book_id`,`number`)

I do realize I can include book_id in the pages table, itself, but I'd prefer to avoid that as it's logically redundant and exposes a data integrity problem if chapters.book_id and pages.book_id were to somehow fall out of sync.

Fiddle of it: http://sqlfiddle.com/#!9/0c7a9

Thanks in advance

0

1 Answer 1

2

No, the only way is to include book_id in pages. To avoid any integrity issues, you'll have to add a UNIQUE constraint (or change the PK) in chapters: UNIQUE (book_id, id) and then have the FK defined from pages as FOREIGN KEY (book_id, chapter_id) REFERENCES chapters (book_id, id).

Another possibility is to get rid of chapters (id) and pages (chapter_id) columns completely and reference the available UNIQUE (book_id, number).

With the second option, your design can be:

CREATE TABLE `books` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `chapters` (
  `book_id` bigint unsigned NOT NULL,
  `chapter_number` tinyint unsigned NOT NULL,
  PRIMARY KEY (`book_id`, `chapter_number`),
  CONSTRAINT `chapters_book_id_fk` 
      FOREIGN KEY (`book_id`) 
      REFERENCES `books` (`id`) 
      ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `pages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `book_id` bigint unsigned NOT NULL,
  `chapter_number` tinyint unsigned NOT NULL,
  `page_number` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`book_id`, `page_number`),
  CONSTRAINT `pages_chapter_id_fk` 
      FOREIGN KEY (`book_id`, `chapter_number`) 
      REFERENCES `chapters` (`book_id`, `chapter_number`) 
      ON DELETE CASCADE
) ENGINE=InnoDB;
3
  • Thanks. I'm tackling other problems now but might circle back to give that first alternative a crack. The second would deviate too far from our conventions for my liking.
    – Rikaelus
    Sep 16, 2021 at 18:39
  • 1
    @Rikaelus - If the convention is that every table must have a BIGINT AUTO_INCREMENT, I will protest on the grounds of inefficiency. It becomes especially bad for a many-to-many mapping table.
    – Rick James
    Sep 16, 2021 at 18:43
  • @RickJames Basically that; many frameworks expect and even require a single-column unique ID, though Laravel (which I'm using here) thankfully doesn't require them for many-to-many mapping tables. As a rule I prefer a clean delineation between database design and software implementation but I'm trying to let Laravel take the lead on this one.
    – Rikaelus
    Sep 16, 2021 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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