How to do AUTO_INCREMENT on a composite key that only affects a key?

An option to solve the problem can be by means of a sequence .

Currently, unlike other products, MySQL does not have native support for this functionality, however, a basic way to implement it is by means of triggers, functions, among other functionalities.

Example:

mysql> DROP TABLE IF EXISTS 'sequences';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS 'sequences' (
    ->   'name' VARCHAR(6) PRIMARY KEY,
    ->   'val' INT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> DROP FUNCTION IF EXISTS 'nextgroup';
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE FUNCTION 'nextgroup' ('p_sequence_name' VARCHAR(6))
    -> RETURNS INT UNSIGNED
    -> READS SQL DATA
    -> BEGIN
    ->   INSERT INTO 'sequences' ('name', 'val')
    ->   VALUES ('p_sequence_name', LAST_INSERT_ID(1))
    ->     ON DUPLICATE KEY UPDATE 'val' = LAST_INSERT_ID('val' + 1);
    ->   RETURN LAST_INSERT_ID();
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> DROP TABLE IF EXISTS 'materia_grupo';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS 'materia_grupo' (
    ->   'cod_materia' VARCHAR(6), 
    ->   'no_grupo' INT UNSIGNED NOT NULL DEFAULT 0,
    ->   PRIMARY KEY ('cod_materia', 'no_grupo')
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TRIGGER IF EXISTS 'trg_materia_grupo_bi';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER //

mysql> CREATE TRIGGER 'trg_materia_grupo_bi' BEFORE INSERT ON 'materia_grupo'
    -> FOR EACH ROW
    -> BEGIN
    ->   SET NEW.'no_grupo' := IF(NEW.'no_grupo' = 0,
    ->                            'nextgroup'(NEW.'cod_materia'),
    ->                            NEW.'no_grupo');
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> TRUNCATE TABLE 'materia_grupo';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO 'materia_grupo' ('cod_materia')
    -> VALUES ('TC2016');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO 'materia_grupo' ('cod_materia')
    -> VALUES ('TC2016');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO 'materia_grupo' ('cod_materia')
    -> VALUES ('MA1056');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO 'materia_grupo' ('cod_materia')
    -> VALUES ('TC2016');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO 'materia_grupo' ('cod_materia')
    -> VALUES ('TC2016');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO 'materia_grupo' ('cod_materia')
    -> VALUES ('MA1056');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
    ->   'cod_materia',
    ->   'no_grupo'
    -> FROM
    ->   'materia_grupo';
+-------------+----------+
| cod_materia | no_grupo |
+-------------+----------+
| MA1056      |        1 |
| MA1056      |        2 |
| TC2016      |        1 |
| TC2016      |        2 |
| TC2016      |        3 |
| TC2016      |        4 |
+-------------+----------+
6 rows in set (0.00 sec)

mysql> SELECT
    ->   'name',
    ->   'val'
    -> FROM
    ->   'sequences';
+--------+-----+
| name   | val |
+--------+-----+
| MA1056 |   2 |
| TC2016 |   4 |
+--------+-----+
2 rows in set (0.00 sec)

If you are using the MyISAM engine, you can define the autoincrement of a given column that is part of a composite key, in which case the prefix of the composite key determines the autoincrement: example auto increment . You would define your table as:

CREATE TABLE materia_grupo (
    materia CHAR(7) NOT NULL,
    grupo MEDIUMINT NOT NULL AUTO_INCREMENT
    PRIMARY KEY (materia,grupo)
) ENGINE=MyISAM;

In InnoDB you can not make this definition and you have to resort to triggers like the one pointed to by @wchiquito.