On Fri, 24 Feb 2012 08:39:57 -0500, Rita <rmorgan...@gmail.com> wrote:
>thanks for the response. > > I assume the schema would be like this > > CREATE TABLE user( > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT NOT NULL); > > CREATE TABLE main( > > t long, > uid integer, > price real, > *FOREIGN KEY(uid) REFERENCES user(id)* > ); > > Then I would first read thru my entire data file (about 3 million rows) to > get the user and populate the user table and then I would populate the main > table. > > When populating the main table, what is the best way to assign the foreign > key, should I do a SELECT inside the INSERT? like > > INSERT into main (t,uid,price) values (1344343545,(SELECT id from user > where name='vberry'),100.00) > > Is that the proper way of doing it or is there a more simple way? The basic idea is ok, but what would happen when there is more than one user with name 'vberry'? In other words, it will only work reliably if name has a UNIQUE constraint. Which is not really a good model, because names are not unique in the real world. So, you need some way to identify distinct users, some natural key. http://www.informationweek.com/news/software/bi/201806814 If name is something like login name or account name or email address, it qualifies as a natural key. You'll need a UNQUE constraint on it anyway. HTH -- Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
