All, I appreciate the input. Some of the advice comes obviously from very good and talented people who find a challenge at doing things WITHOUT reverting to code writing.
I did a small program. Current show -stopper : what is the API equivalent of PRAGMA foreign_keys = ON; If I can not turn foreign_key support on, the insert with the erronous id will just slip in. Thanks, Shalom On Wed, Mar 23, 2011 at 5:24 PM, BareFeetWare <list....@barefeetware.com>wrote: > On 23/03/2011, at 9:03 PM, Shalom Elkin wrote: > > > I am new to SQLITE. Here's the challenge: > > > > sqlite> .dump > > BEGIN TRANSACTION; > > CREATE TABLE people( > > id integer, > > nm text); > > INSERT INTO "people" VALUES(1,'Jack'); > > INSERT INTO "people" VALUES(2,'Jill'); > > CREATE TABLE activity( > > aid integer, > > act text, > > foreign key (aid) references people(id) > > ); > > COMMIT; > > PRAGMA foreign_keys=On; > > sqlite> .import ac.in activity > > Error: foreign key mismatch > > > > This is ac.in > > > > 1|eat > > 2|sleep > > 3|run > > 1|drink > > 2|dream > > > > id 3 doesn't exist, the insertion fails. Now, this was easy. what if > > ac.inhad millions of rows? I am looking for some way to get a message > > like "error > > in line 3: foreign key mismatch". > > preferably, the import would go on without actually inserting, but report > > ALL errors in one pass. > > > > Any ideas? > > I usually import into a separate table, then use SQL to process the data > into the final destination. This way, I can use any dumb import tool (such > as the .import command line tool) and take care of the smarts (including > constraints, error logging etc) in SQL. > > How about this: > > pragma foreign_keys = on; > create temp table "activity import" > ( aid integer > , act text > ) > ; > create table "import error" (aid integer); > > .import ac.in "activity import" > > begin immediate; > insert into "import error" select aid from "activity import" where aid not > in (select id from "people"); > insert into "activity" select * from "activity import" where aid in (select > id from "people"); > commit; > > or, you can add the required people on the fly: > > begin immediate; > insert or ignore into "people" (id) select aid from "activity import" where > aid not in (select id from "people"); > insert into "activity" select * from "activity import"; > commit; > > For this to work, you probably want to define the primary key in people: > > CREATE TABLE people > ( id integer primary key not null > , nm text > ) > ; > > Tom > BareFeetWare > > -- > iPhone/iPad/iPod and Mac software development, specialising in databases > develo...@barefeetware.com > -- > Comparison of SQLite GUI tools: > http://www.barefeetware.com/sqlite/compare/?ml > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shalom Elkin +972-544-704994 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
