> 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
