mySQL integer types

[stephen barncard]

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 <bobs at twft.com> 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
> use-livecode at lists.runrev.com
> 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>