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