yes, the names will be unique. It will be a userid. On Sat, Feb 25, 2012 at 6:46 AM, Kees Nuyt <k.n...@zonnet.nl> wrote:
> 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 > -- --- Get your facts first, then you can distort them as you please.-- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
