I remember in Trevor's original DB abstraction library (before SQL Yoga) he used a manual indexing method, instead using a special table of indexes for each table that is used. That allowed the changing the actual value of the keys if needed. Automatic indexing is a feature that is not mandatory, but convenient.
On 16 January 2012 13:25, Bob Sneidar <[email protected]> wrote: > 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 > [email protected] > Please visit this url to subscribe, unsubscribe and manage your > subscription preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode > -- Stephen Barncard San Francisco Ca. USA more about sqb <http://www.google.com/profiles/sbarncar> _______________________________________________ use-livecode mailing list [email protected] Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
