Scoped/composite surrogate keys in MySQL

Question

Here's an excerpt of my current database (changed the table-names for an easier understanding):

Pet(ownerFK, id, name, age)
Owner(id, name)

Where id is always a surrogate key, created with auto_increment.

I want to have the surrogate key Pet.id to be "scoped" by Pet.ownerFK or in otherwords, have a composite key [ownerFk, id] as my minimum key. I want the table to behave like this:

INSERT Pet(1, ?, "Garfield", 8);
INSERT Pet(1, ?, "Pluto", 12);
INSERT Pet(2, ?, "Mortimer", 1);

SELECT * FROM Pet;
  RESULT:
   Pet(1, 1, "Garfield", 8)
   Pet(1, 2, "Pluto", 12)
   Pet(2, 1, "Mortimer", 1)

I am currently using this feature of MyISAM where "you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups."

However, due to various (and maybe obvious) reasons, I want to switch from MyISAM to InnoDB, as I need transactions at some places.

Is there any way how to achieve this effect with InnoDB?

I found some posts on this issue, many of them proposed to write-lock the table before insertion. I am not very familiar with this, but wouldn't be a table-write-lock a little-bit of an overhaul for this one? I rather thought of having write-safe transactions (which I never did before) if these are possible - having a Owner.current_pet_counter as an helper field.

So another acceptable Solution would be...

Actually I don't need the "scoped" ID to be part of the actual Key. My actual database design uses a separate "permalink" table which uses this 'feature'. I currently use it as a workaround for the missing transactions. I thought of the following alternative:

 Pet(id, ownerFK, scopedId, name, age), KEY(id), UNIQUE(ownerFK, scopedId)
 Owner(id, name, current_pet_counter)

 START TRANSACTION WITH CONSISTENT SNAPSHOT;
 SELECT @new=current_pet_counter FROM Owner WHERE id = :owner_id;
 INSERT Pet(?, :owner_id, @new, "Pluto", 21);
 UPDATE Owners SET current_pet_counter = @new + 1 WHERE id = :owner_id;
 COMMIT;

I haven't worked with transactions/transactionvars in MySQL yet, so I don't know whether there would be serious issues with this one. Note: I do not want to reuse ids that have been given to a pet once. That's why I don't use MAX(). Does this solution have any caveats?

Answer 1

I don't believe so. If you really had to have that schema, you could use a transaction to SELECT the MAX(id) WHERE ownerFK, then INSERT.

I'm very sceptical there's a good reason for that schema, though; the primary key is now also a fact about the key, which might make the database theorists unhappy.

Normally you'd want ‘id’ to really be a proper primary key on its own, with ownerFK used to group and, if you needed it, a separate ‘rank’ column to put pets in a particular order per owner, and a UNIQUE index over (ownerFK, rank).