SQL Join question

[Peter Haworth]

On Sun, Jun 22, 2014 at 6:55 AM, Mark Smith <Mark_Smith at cpe.umanitoba.ca>
wrote:

> I never did
> resolve how to make the primary keys unique across devices
>

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)

The Device column would have a different default value on each iPad, or you
could omit the DEFAULT specification and your application could supply the
appropriate value on each iPad.

Your application would be responsible for incrementing the Recno column for
each row added to the table.  Alternatively, you could simply set it to the
milliseconds when each row is added.

The result would be that each row would have a unique primary key
consisting of the Device and Recno concatenated together and since the
Device column would be different on each device, you're guaranteed that
there would be no duplicates across devices.

The riskier one involves using an internal SQLite table named
sqlite_sequence.  This table has 2 columns, table and seq.  There is one
row for each table in the db with the seq column containing the last used
primary key value.

When you first install your db on each iPad, you could set the seq field
for the table to a value that would guarantee there would never be an
overlap.  For example, if you know that there will never be more than
499,999 rows in the tabnle on any individual iPad, iPad 1 would have seq
zero, iPad 2 would have seq 499,999, ipad 3 would have seq 999,999.  You
can do that with a simple INSERT statement on the sqlite_sequence table but
make sure the database is empty when you do it.


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