Search code examples
mysqlauto-increment

How auto-increment within a subset of the table MYSQL

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.

db image

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?

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
  )
;