On 13 Dec 2010, at 4:07pm, Duquette, William H (316H) wrote: > 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.
Yes yes and yes. Basically, you need to work out your data hierarchy and import in that order. Case 3 is quite rare, but when it does happen you need to figure out what order the data was entered in originally. One way to do this is to datestamp the appropriate fields. So when trying to figure out what order to reenter the records in, just do it in the same order they were entered originally. This saves you having to intelligently understand the hierarchy. > 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. True. But if you had two records like that the data could never have been entered in the first place without some intentional trickery. Finding records like that almost always means someone messed up somewhere. And any schema that uses 4 routinely usually means that the programmer didn't think the design through properly. Example: a parts database with a column for 'replacesPartID' and another for 'replacedByPartID'. It's one way of holding the data but storing just one of the two columns would do it better. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
