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
