SQL Join question

Peter Haworth pete at lcsql.com
Sun Jun 22 20:11:33 EDT 2014


Unfortunately, autoincrementing can only be done on primary keys and there
can only be one primary key per table.

However, it just occurred to me that if you go back to an autoincrementing
primary key you could create the concatenated key in your SELECT with:

SELECT Device || pKey AS UniqueKey,.....

"||" is the SQLite concatenation operator so the SELECT would create a
column named UniqueKey.

Don't know enough about iOS to know if there is some sort of unique ID you
could get from it to use as the device column but that would work great if
so.


Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>


On Sun, Jun 22, 2014 at 3:15 PM, Mark Smith <Mark_Smith at cpe.umanitoba.ca>
wrote:

> Hi Peter, thanks for that. I definitely prefer your option 1. Would it
> still
> be possible to have an auto incrementing field that combines with the
> device
> id to make the primary key (ie. could recno in your example be auto
> incrementing)? Also, I wonder if there is a device ID that could be grabbed
> from the device itself? Combining the two would make this foolproof since
> no
> manual intervention would be required, and each iPad device would be
> guaranteed to have/contribute its own unique id.
>
>
> Peter Haworth wrote
> > Assuming you are using SQLite, there are a couple of ways to achieve
> this,
> > one riskier than the other.
> >
> > The less risky approach is to give up on using an autoincrementing
> primary
> > key field and use your own counter along with a device identifier column
> > as
> > a prefix.  The two columns together would become your primary key column.
> >  The CREATE TABLE statement for this would something like:
> >
> > CREATE TABLE mytable (Device TEXT DEFAULT 'D1', Recno INTEGER,...other
> > columns) PRIMARY KEY (Device,Recno)
>
>
>
>
>
> --
> View this message in context:
> http://runtime-revolution.278305.n4.nabble.com/SQL-Join-question-tp4680574p4680596.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> 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
>



More information about the use-livecode mailing list