Make AUTO_INCREMENT reset itself on change of Parent ID

Question

In a project assignment I have I want the one part of the PRIMARY KEY to reset after the other part changes value. For example:

CREATE TABLE shopping_center(
    centerID INTEGER AUTO_INCREMENT,
    centerName CHAR(50),
    CONSTRAINT center_PK PRIMARY KEY(centerID));

CREATE TABLE staff(
    staffID INTEGER AUTO_INCREMENT,
    centerID INTEGER,
    name VARCHAR(50),
    CONSTRAINT staff_PK PRIMARY KEY(staffID, centerID));

ALTER TABLE staff
    ADD CONSTRAINT staff_FK
    FOREIGN KEY(centerID) 
    REFERENCES shopping_center(centerID);

The AUTO_INCREMENT feature of MySQL is not working as I intended in this situation. When I insert these values (keep in mind the AUTO_INCREMENT feature on the IDs, as I don't define the centerID or staffID in the INSERT statements):

INSERT INTO shopping_center (centerName) VALUES("A Shopping Center");
INSERT INTO shopping_center (centerName) VALUES("Another Shopping Center");

INSERT INTO staff (centerID, name) VALUES(1, "Staffmember 1 in centerID 1");
INSERT INTO staff (centerID, name) VALUES(1, "Staffmember 2 in centerID 1");
INSERT INTO staff (centerID, name) VALUES(2, "Staffmember 1 in centerID 2");
INSERT INTO staff (centerID, name) VALUES(2, "Staffmember 2 in centerID 2");

I get this result:

SELECT centerID, staffID, name FROM staff; 
+----------+---------+-----------------------------+
| centerID | staffID | name                        |
+----------+---------+-----------------------------+
|         1|        1| Staffmember 1 in centerID 1 |
|         1|        2| Staffmember 2 in centerID 1 |
|         2|        3| Staffmember 1 in centerID 2 |
|         2|        4| Staffmember 2 in centerID 2 |
+----------+---------+-----------------------------+

But what I want is for the staffID to reset to 1, when centerID changes value. This is the result I want (pay attention to the change of staffID values):

SELECT centerID, staffID, name FROM staff; 
+----------+---------+-----------------------------+
| centerID | staffID | name                        |
+----------+---------+-----------------------------+
|         1|        1| Staffmember 1 in centerID 1 |
|         1|        2| Staffmember 2 in centerID 1 |
|         2|        1| Staffmember 1 in centerID 2 |
|         2|        2| Staffmember 2 in centerID 2 |
+----------+---------+-----------------------------+

As the PRIMARY KEY as required always is unique in this case, I don't see why this shouldn't work. Is there a solution to what I want to achieve here?

Answer 1

I'll try to answer your question to the point where I explain what's going on.

First off, the feature you want is available in MyISAM storage engine (kind of, there's a twist). However, transactions and referential integrity aren't available.

Now for auto_increment - it gives unique values to rows. However, it does this by taking concurrency in mind - meaning if two or more people connect and perform some work, the auto_increment will correctly calculate in that scenario for both people, if they are accessing the same table.

This means that auto_increment, for every insert, just gets incremented. There is no relation to any record ever, which is why auto_increment is fast and you get a unique identifier with 100% certainty.

Another useful thing that happens is that InnoDB performs clustering based on this number. In other words, it uses this number to perform its internal data structure balancing in order to provide performance at a later stage (reading / updating of the records).

Now why is this important - if you change this number, in any way, InnoDB will re-balance its B-Tree and it will take some time to do that. It means - never touch primary key value.

However, all of this isn't really relevant. What's relevant is the following - why do you need staffID to change at all? Nothing bad can happen if you leave it as is.