How did I do this?

Getting Started
1

Yes, I’m really trying to remember how I did this.

I wrote an database update routine about a decade ago (back in the days of Realbasic) and what it does is in short is create a new database using what I call a FSM (file structure master) file and then add the data from the current database to it.

Here is what is troubling me. Somehow, the linked records (based on an auto-incrementing primary key) remains the same after doing an “insert into … select from … “

For example, I have a master table whose ID (primary key auto increment field) is the child key in another table.

The only thing that may be making this work this way is that I’m attaching the old database to the new file - I’m using the “attachdatabase” command to do this - and then doing the insert command.

Does the insert command somehow override the “auto-increment” field and insert the value from the attached database?

2

Yes, that’s exactly what is happening. From sqlite.org

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

So since you are supplying a value, the auto increment does not come into play.

1 Like
3

Thom

Thank you for clarifying this. I figured that’s what was going on but just wanted to verify.

4

If you need to ‘reset’ the auto increment variable, here is what I wrote years ago for the major databases after I imported records manually into PostgreSQL, but the auto increment was still set as 1. It returns the SQL string to execute:

Protected Function getAutoIncrementResetWAD(SQLBrand As String, myTable As String, myField As String = "id", newValue As Integer = -1) As String
  Select Case SQLBrand
  Case "MySQL"
    Return "ALTER TABLE " + myTable + " AUTO_INCREMENT = " + newValue.ToString    
  Case "MSSQL"
    Return "" 'unsure
    
  Case "PostgreSQL", "Postgres"
    'stackoverflow.com/questions/5342440/reset-auto-increment-counter-in-postgres#5342503
    If NewValue < 0 Then
      Return "SELECT setval('" + myTable.Lowercase + "_" + myField.Lowercase + "_seq" + "', (SELECT coalesce(MAX(" + myField + "), 1) FROM " + myTable + "));" 'coalesce returns the first non NULL value in the list
      
    Else
      Return "ALTER SEQUENCE " + myTable.Lowercase + "_" + myField.Lowercase + "_seq RESTART WITH " + newValue.ToString
    End If
    
  Case "SQLite"
    If NewValue < 0 Then
      Return "UPDATE SQLITE_SEQUENCE SET seq = (SELECT MAX(" + myField + ") FROM " + myTable + ") WHERE name = '" + myTable + "' "
    Else
      Return "UPDATE SQLITE_SEQUENCE SET seq = " + newValue.ToString + " WHERE name = '" + myTable + "' "
    End If
    
  Case Else
    Return ""
  End Select
    
End Function
5

Thanks for the replies. I guess I figured this out a decade or more ago but with time must have forgotten that the “insert into” was the solution. I was aware that you cannot change the field attribute auto-increment without creating a new field, which would be a much more complicated solution, and changing the sequence wouldn’t work in our case either… we looked at that.

I’ve changed my code to use the “insert into” and everything is converting as it should.