My remarks were made in the context of AUTOINCREMENTING primary keys. With
auto-incremented keys, the database is free to implement the incrementation
in the manner it sees fit. It may skip numbers. It may re-generate keys on
import/restore and cascade the changes out to child tables. Given these
possibilities, it is not best practice to exchange autoincremented keys
between systems but to use instead an alternate unique key that is
guaranteed to remain constant.
Regards
Tim Romano
Swarthmore PA
On Mon, Jun 28, 2010 at 2:31 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
> > Such freedom is not suitable for data interchange between two systems.
> Not
> > that SQLite or any other database would change the PK during
> import-export,
> > but they are free to do so as long as the *intramural* integrity is
> > preserved.
>
> Can you point out some documentation supporting this claim?
> I've no time now to search internet on this matter but I believe DBMS
> changing data that *I stored* in it is fundamentally broken. I
> wouldn't advise anybody to use it. DBMS is allowed to change only
> internal details which do not appear in CREATE TABLE and INSERT
> statements (like ROWID for example). And AFAIK semantically primary
> key is no difference with unique constraint (except ability to be
> referenced by foreign key of course). And even more: I believe in a
> completely normalized database there couldn't be any unique
> constraints other than primary key. And to advise to developers either
> to not use primary key or to declare all columns referencing to it as
> foreign key is too much of a restrain.
>
>
> Pavel
>
> On Mon, Jun 28, 2010 at 2:15 PM, Tim Romano <tim.romano...@gmail.com>
> wrote:
> > Pavel,
> > Although you are right that SQLite persists the rowid for INTEGER PRIMARY
> > KEYS across VACUUMs and suchlike, I too am right.
> >
> > I was focusing on the OP's use of the words "guaranteed" and "globally"
> and
> > on this requirement:
> >
> > The OP wrote:
> > "BTW, in my story it is necessary to store the unique IDs as an integer
> > type not something like "uuid" or "hash" because the unique ID also
> > standard for a position in a string in exchanging protocol between 2
> > system."
> >
> > Since no SQL standard requires the primary key to do anything other than
> be
> > unique within the relation and with respect to its foreign references.
> As
> > long as the database maintains meets those requirements, it is free to
> > change the PK value as an "implementation detail" -- provided RI is not
> > broken in the process. The purist in me says the PK is for nothing but
> > uniqueness. It should have no other meaning whatsoever.
> >
> > Such freedom is not suitable for data interchange between two systems.
> Not
> > that SQLite or any other database would change the PK during
> import-export,
> > but they are free to do so as long as the *intramural* integrity is
> > preserved. Once you move into the extra-mural realm (data exchange, or
> > replication) I would advise against relying upon the PK value.
> >
> > The safest "guaranteed" way to achieve what the OP wants is to add
> another
> > column to the table and to make it an alternate unique key. This value
> > carries for him the specific meaning "position in a string in exchange
> > protocol between 2 systems".
> >
> > Regards
> > Tim Romano
> > Swarthmore PA
> >
> >
> > On Mon, Jun 28, 2010 at 10:23 AM, Pavel Ivanov <paiva...@gmail.com>
> wrote:
> >
> >> > the primary key column [id] is defined as INTEGER PRMARY KEY; so
> defined,
> >> > SQLite will treat this column as an alias for the ROWID. There is no
> >> > guarantee that ROWID will remain constant over time: its job is very
> >> simple:
> >> > to be unique. There is no "be constant" clause in its contract, so to
> >>
> >> Tim, you are not right here. You are right that as is ROWID is not
> >> guaranteed to be constant over time. But if you define some column as
> >> an alias to ROWID (i.e. if you have column INTEGER PRIMARY KEY) then
> >> SQLite guarantees that the value of this column will persist through
> >> any internal operations (like VACUUM or dumping and loading database
> >> again). Of course nobody will block UPDATEs on this column (as long as
> >> uniqueness remains valid), but that's a different story.
> >>
> >>
> >> Pavel
> >>
> >> On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano <tim.romano...@gmail.com>
> >> wrote:
> >> > In this example:
> >> >
> >> > CREATE TABLE tableA {
> >> > id INTEGER PRIMARY KEY AUTOINCREMENT,
> >> > name TEXT NOT NULL UNIQUE,
> >> > myspecialvalue TEXT NOT NULL UNIQUE
> >> > }
> >> >
> >> >
> >> > the primary key column [id] is defined as INTEGER PRMARY KEY; so
> defined,
> >> > SQLite will treat this column as an alias for the ROWID. There is no
> >> > guarantee that ROWID will remain constant over time: its job is very
> >> simple:
> >> > to be unique. There is no "be constant" clause in its contract, so to
> >> > speak. Therefore, you should add another column [myspecialvalue] and
> make
> >> it
> >> > unique if you want to associate a value with a tuple and also want to
> >> > guarantee that the associated value is both unique and remains
> >> unchanging.
> >> > Of course you have to prevent edits to the associated value to
> enforce
> >> its
> >> > immutability.
> >> >
> >> > Regards
> >> > Tim Romano
> >> > Swarthmore PA
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > On Sat, Jun 26, 2010 at 11:34 AM, kee <keekyc...@gmail.com> wrote:
> >> >
> >> >> Dear all
> >> >>
> >> >> I have 2 string lists, listA and listB as raw data which need to be
> >> >> store in the SQLITE database, both of them may have duplicated
> records
> >> >>
> >> >> listA listB
> >> >> =======================
> >> >> orange japan
> >> >> pear china
> >> >> orange china
> >> >> apple american
> >> >> cherry india
> >> >> lemon china
> >> >> lemon japan
> >> >> strawberry korea
> >> >> banana thailand
> >> >> australia
> >> >>
> >> >> I want all items in listA and listB have a runtime defined global ID
> and
> >> >> fix it, which means no matter how the lists changed later after the
> >> >> first time running, all the item always have an unique int type ID
> bind
> >> >> with, looks like:
> >> >> A B
> >> >> ================================
> >> >> 1 orange 1 japan
> >> >> 2 pear 2 china
> >> >> 3 apple 3 american
> >> >> 4 cherry 4 india
> >> >> 5 lemon 5 taiwan
> >> >> 6 strawberry 6 korea
> >> >> 7 banana 7 thailand
> >> >> 8 australia
> >> >>
> >> >>
> >> >> So I defined table with such structure:
> >> >> CREATE TABLE tableA {
> >> >> uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
> >> >> name TEXT NOT NULL UNIQUE,
> >> >> }
> >> >> CREATE TABLE tableB {
> >> >> uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
> >> >> name TEXT NOT NULL UNIQUE,
> >> >> }
> >> >>
> >> >> and my plan is to use "INSERT OR FAIL" to insert data into those
> tables.
> >> >>
> >> >> Here comes my QUESTION 1, is it possible no matter what the list
> >> >> changed, all items always get an unique ID, should any other
> limitation
> >> >> should be added into the defination, and if I use "CREATE TABLE
> >> >> table_dst AS SELECT * FROM table_src" to duplicate tables later, can
> >> >> those definition be copied either?
> >> >>
> >> >>
> >> >> Then, it may need to make a matrix for 2 tables: I want list all
> >> >> possible combination of 2 lists, for example:
> >> >>
> >> >> listC = listA * listB
> >> >> ====================
> >> >> japan orange
> >> >> china orange
> >> >> american orange
> >> >> india orange
> >> >> ...
> >> >> thailand banana
> >> >> australia banana
> >> >>
> >> >> I also want to use same table structure to store the combination
> result
> >> >> and assigned unique ID for those combined items same as before:
> >> >> CREATE TABLE tableC {
> >> >> uinque_id INTEGER PRIMARY KEY AUTOINCREMENT,
> >> >> name_combination TEXT NOT NULL UNIQUE,
> >> >> }
> >> >> Here comes my QUESTION 2, is it also reasonable using such a
> structure
> >> >> store the combination or should there be a better way to do it?
> >> >> I means will such a structure be a problem if the listA and listB be
> >> >> changed, should I store uniqueIDA and uniqueIDB replace the
> >> >> name_combination field will be a better solution?
> >> >>
> >> >> BTW, I using the python as the interface insert the lists into those
> >> >> tables, also the uinque_id in database is not need to be reused if
> some
> >> >> items in listA and listB been deleted, just remain as is because it
> will
> >> >> never get to sqlite limitation.
> >> >>
> >> >> BTW, in my story it is necessary to store the unique IDs as an
> integer
> >> >> type not something like "uuid" or "hash" because the unique ID also
> >> >> standard for a position in a string in exchanging protocol between 2
> >> >> system.
> >> >>
> >> >>
> >> >> And : a more general question:
> >> >> Anyone has better solution to solve my problem in sqlite - the items
> in
> >> >> a list need to be bind with an unchangeable integer type unique ID no
> >> >> matter what the list will be modified?
> >> >>
> >> >> Any comments and suggestions will be highly appreciated!
> >> >>
> >> >> Thanks!
> >> >>
> >> >> Rgs,
> >> >>
> >> >> KC
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> _______________________________________________
> >> >> sqlite-users mailing list
> >> >> sqlite-users@sqlite.org
> >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >>
> >> > _______________________________________________
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> _______________________________________________
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
