How auto-increment within a subset of the table MYSQL

auto-increment, mysql

Solution

You could use a trigger:

CREATE TRIGGER biBooks 
  BEFORE INSERT ON books 
  FOR EACH ROW SET NEW.booknum = (
    SELECT COALESCE(MAX(booknum), 0) + 1 
      FROM books 
      WHERE authors_id = NEW.authors_id
  )
;

Problem

To illustrate my problem I will use the analogy of authors and books. I have 2 tables "author" and "books". Authors are unique and books are tied to a specific authors using a foreign key constraint. I was wondering if it was possible to have a column called "booknum" in the "books" table that auto-increment within the subset of a single author. So if the table has 100 rows and im inserting the 4th book of an author it puts a 4 into the "booknum" column. For example if the books table had 6 rows: ``` id | authors_id | booknum | name 1 | 1 | 1 | "hello" 2 | 1 | 2 | "goodbye" 3 | 2 | 1 | "booktitle" 4 | 3 | 1 | "more title" 5 | 1 | 3 | "nametwo" 6 | 2 | 2 | "nameone" ``` Is this possible within mysql or do I need to go and check for the last created book and manually increment when I add a book?

Original source

Related problems