If you make all of your FK constraints DEFERRABLE INITIALLY DEFERRED, then all the FK checking occurs at the conclusion of each transaction, not at the conclusion of each statement. Then if you do all your importing within a single transaction, it won't matter what order you import things.
On Mon, Dec 13, 2010 at 11:07 AM, Duquette, William H (316H) < william.h.duque...@jpl.nasa.gov> wrote: > Howdy! > > I've been experimenting with SQLite3's foreign key support, > and I want to check my understanding. I've got an application > that regularly takes a number of tables and exports their > contents as plain text, and then later imports it again. If I > declare foreign key constraints and enable checking of > foreign key constraints, I think the following statements are > true: > > 1. If table A references table B, I'm going to have to import > B before A or I'll get constraint violations. > > 2. If table A references itself (e.g., because of a tree structure), > I'm going to have to import the rows of A in a particular order > or I'll get constraint violations. > > 3. If two tables reference each other (ugh!) I might have to > import the two tables simultaneous, properly interleaved, > or I'll get constraint violations. > > 4. If table A references itself, and I have two rows that > reference each other (double-ugh!), the table might not > be importable at all. > > (Note: I don't actually have cases 3 or 4 in my app. :-) > > These problems can be avoided by disabling foreign > key checking before importing the data; but that seems > like just when you'd want to have it. > > Am I missing something? How do folks actually deal > with this in practice? > > Will > > -- > Will Duquette -- william.h.duque...@jpl.nasa.gov > Athena Development Lead -- Jet Propulsion Laboratory > "It's amazing what you can do with the right tools." > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
