mySQL integer types

[Bob Sneidar]

On Jan 16, 2012, at 12:56 PM, Pete wrote:

> In SQLite, if you define an INTEGER PRIMARY KEY column with AUTOINCREMENT
> keyword, primary key values are always allocated as the next highest value
> for the table, but if you omit the AUTOINCREMENT, then values freed up by
> deleted rows may get assigned.  I don't know if this is how mySQL works.

It's my understanding that a primary key MUST be AI in mySQL. A friend of mine gave me a query that will find the next available value in a column of numbers: 

put "select l." & theColumnName & " + 1 as start" & cr & \
            "from " & theTable & " as l" & cr & \
            "left outer join " & theTable & " as r on l." & theColumnName & \
            " + 1 = r." & theColumnName & cr & \
            "where r." & theColumnName & " is null;" into theSQL

I do not pretend to know what that means. But it works! I know you can update a primary key with something other than the next incremental value, so long as it is unique. I suppose if you lock the table first, get the next unique value, update the primary key, unlock the table, then select with that primary key, that would accomplish the same thing, but I believe that the next incremental value gets updated anyway, so if you use that method, you can't go back to allowing mySQL to increment without a gap in your sequence. In other words it's a one way street, and I am not sure that mySQL won't still throw an error anyways once it reaches it's max on the value, so the point may be moot. 

Bob