On 26 Jun 2010, at 4:34pm, kee wrote: > both of them may have duplicated records
... and later ... > name TEXT NOT NULL UNIQUE, Those two things contradict each-other. If you specify UNIQUE you can't have duplicated values. > CREATE TABLE tableA { Try to get out of that habit. if those are countries, call your table 'countries', not 'tableA'. > if I use "CREATE TABLE > table_dst AS SELECT * FROM table_src" to duplicate tables later, can > those definition be copied either? They will be. You can't avoid it. > 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, > } Do it in software. If you find it easier to work with long strings, and if there're no commas in your names then you can use SELECT group_concat(name,',') FROM countries and get one long string instead of lots of records To answer your questions: 'INTEGER PRIMARY KEY AUTOINCREMENT' is assigned when a row is created. It won't change even if you change the values of all the other columns of that row. As for storing the combinations, that'll work fine but it's not obvious why you want to store them. There may be something that suits your purposes better. But yes, it'll work fine and it's the sort of thing people use SQL for all the time. Tables don't magically update one-another. If you create your table of combinations and then your fruit table changes, the combination table won't magically update. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users