StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL ID Rolling
text
Body
copied!<p>I am working on a rather large database migration to a new database design. The existing structure had multiple tables for the same data being represented for different stores.</p> <p>For example:</p> <pre><code>`store1_tickets` -------------------- | id | customer | -------------------- | 1 | 29 | -------------------- `store2_tickets` -------------------- | id | customer | -------------------- | 1 | 54 | -------------------- </code></pre> <p>I am now consolidating into a table like this:</p> <pre><code>`tickets` ---------------------------------------- | id | legacy_id | store | customer | ---------------------------------------- | 1 | 1 | 1 | 29 | | 2 | 1 | 2 | 54 | ---------------------------------------- </code></pre> <p>This pattern repeats for several components (customers, vendors, appointments...).</p> <p>I am making a (PHP) script to do an ETL into INSERT statements. It has to keep a running total of the new ticket IDs while transforming the data. After the INSERT statement, I am making an UPDATE statement to change the corresponding IDs in other tables (such as changing the <code>customer</code> field in the <code>tickets</code> table once I've re-numbered the <code>customers</code> table.</p> <p>I'm afraid of running the UPDATEs (after all of the INSERTs) and having it do a kind of cascade, where it changes <code>customer</code> 1 into 54, then when it reaches <code>customer</code> 54, changing that into 243, and so on.</p> <p>How can I properly fix the IDs? The ticket table is the only one keeping the legacy ID since I will actually be using that as a multi-column auto_increment (each store has to have its own incrementing ticket ID for display purposes). The complexity comes in the fact that there are so many tables that reference each other, so that greatly complicates updating any of the IDs outright in the script.</p> <p>Is there any better approach to this, or some way of preventing the UPDATEs from cascading? I almost think something like starting the <code>id</code> off at a really high number (would have to be atleast 100k due to the records count), then after everything is said and done I could decrement all of the IDs by that value.</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