Mysql: autoincrement field in composite primary key for each unique combination of other fields

Question

I have a Mysql table with a composite primary key.
I need one of the fields of the primary key to autoincrement separately for each unique pair of the other two fields.
For instance:

Book_Name | Author_Name | Chapter
Book1 | Author1 | 1
Book1 | Author1 | 2
Book1 | Author1 | 3
Book2 | Author2 | 1
Book2 | Author2 | 2
Book2 | Author2 | 3
Book3 | Author3 | 1
Book3 | Author3 | 2
Book3 | Author3 | 3

Does anyone know how this can be done in Mysql Workbench?

Answer 1

It's only possible for (book,author,chapter) to be the primary key and for chapter to auto-increment independently for each unique (book,author) combination only in one circumstance:

The storage engine has to be MyISAM.

A MyISAM table defined like this will do exactly what you expect, starting column `c` at "1" for each unique (a,b):

CREATE TABLE `aa_test` (
  `a` varchar(48) NOT NULL,
  `b` varchar(48) NOT NULL,
  `c` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`,`b`,`c`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

However, don't do it.

For at least these reasons:

First, you shouldn't be using MyISAM. It is a very old technological dinosaur, it is not transactional, and it is not nearly as robust as InnoDB when it comes to corruption and crash recovery. You should be using InnoDB and this legacy behavior in MyISAM is no good reason to do that differently.

Second, that's not really an appropriate use of auto-increments. Auto-increments should really only be used for values that are needed as surrogate values but that have no actual meaning in the real world.

Third, your table does not appear to be well-designed, perhaps not even in 2nd normal form.

One of two things must be true based on this structure:

Either each chapter of a book can have a different author, which breaks the whole point of trying to auto-increment on (book,author) ... or (more likely) the presence of the author attribute on every row for (book,chapter) is redundant.

Arguably, the only candidate key in this table is (book,chapter) and if author does not depend on both book and chapter then the table isn't in 2NF.

Additionally, you shouldn't be storing the name of the book in each row, either. Any given piece of data should only appear in a single column in a single row in a single table in a relational database, with every thing else referencing it by a key (e.g., a "book_id" here references a single "id" in the "book" table which contains a column for the "title" of the book).

My answer, then, is a suggestion that you are not trying to solve the correct problem, and should consider redesigning your structure so that author is an attribute of book in a table where each book only has one row, and then you'd have this table with (book,chapter), and your application could populate those chapter numbers manually.

Alternately, if you insist on auto-generating these values, you could use a BEFORE INSERT trigger to calculate the appropriate value on insert, containing logic like this:

IF IFNULL(NEW.Chapter,0) = 0 THEN  
  SET NEW.Chapter = (SELECT COUNT(1) FROM this_table_name x
                      WHERE x.Book_Name = NEW.Book_Name
                        AND x.Author_Name = NEW.Author_Name) + 1;
END IF;

A trigger with this logic would work with any storage engine.

The IFNULL() is required because MySQL historically coerces a null to 0 before firing the trigger when the column is not nullable, even though this behavior is not correct.