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
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
