StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POScoped/composite surrogate keys in MySQL
text
Body
copied!<p>Here's an excerpt of my current database (changed the table-names for an easier understanding):</p> <pre><code>Pet(ownerFK, id, name, age) Owner(id, name) </code></pre> <p>Where <code>id</code> is always a surrogate key, created with <code>auto_increment</code>.</p> <p>I want to have the surrogate key <code>Pet.id</code> to be "scoped" by <code>Pet.ownerFK</code> or in otherwords, have a composite key <code>[ownerFk, id]</code> as my minimum key. I want the table to behave like this:</p> <pre><code>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) </code></pre> <p>I am currently using this <a href="http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html" rel="nofollow noreferrer"><em>feature</em> of MyISAM</a> where "you can specify <code>AUTO_INCREMENT</code> on a secondary column in a multiple-column index. In this case, the generated value for the <code>AUTO_INCREMENT</code> column is calculated as <code>MAX(auto_increment_column) + 1 WHERE prefix=given-prefix</code>. This is useful when you want to put data into ordered groups."</p> <p>However, due to various (and maybe obvious) reasons, I want to switch from MyISAM to InnoDB, as I need transactions at some places.</p> <p><strong>Is there any way how to achieve this effect with <em>InnoDB</em>?</strong></p> <p>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 <code>Owner.current_pet_counter</code> as an helper field.</p> <p><em>So another acceptable Solution would be...</em></p> <p>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:</p> <pre><code> 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; </code></pre> <p>I haven't worked with transactions/transactionvars in MySQL yet, so I don't know whether there would be serious issues with this one. <em>Note:</em> I do not want to reuse <code>id</code>s that have been given to a pet once. That's why I don't use <code>MAX()</code>. <strong>Does this solution have any caveats?</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload