On Fri, 6 Sep 2013 17:29:25 Harmen de Jong - CoachR Group B.V. wrote > [Explaining why there can be 10,000 tables referencing one table] ... users > can create highly customized research forms with varying number of columns, > varying field types and a lot of specific restrictions, which we store in > tables that are generated on the fly with the proper indexes ...
[J. Merrill's comment below] SQL Server is considered to much less "lite" than SQLite, but it does not support more than about 253 foreign key references to a particular column. (Some versions have a hard limit that blocks creation of the (N+1)th foreign key reference; other versions let you create so many references that if you try to delete from the referenced table you get "The query processor ran out of stack space during query optimization. Please simplify the query.") In the case I ran into in real life, deletes from the referenced table were simply incredibly slow because all the referencing tables had to be checked to ensure that none pointed to any rows being deleted. (My case was that I had a centralized "notes" table and each other table that the users saw as having a "notes" column really just had an FK to the notes table.) I'm more surprised that SQLite actually supports 1,000 or 10,000 FK references without failing (unlike SQL Server) than that it gets quite a bit slower to delete from the referenced table. (Whether the slowness is during Prepare or the actual deletion seems somewhat unimportant.) I suggest that perhaps you could avoid having an "official" foreign key reference to the main table in each of those other tables; you almost certainly have application logic to avoid creating rows in other tables that don't have an invalid (non-null) reference to the main table. (You should of course create an index on the no-longer-official-FK column in those other tables.) I find it curious that my two most recent posts are suggestions to avoid using different parts of SQLite's "data integrity" support -- referential integrity in this case, a multi-column UNIQUE constraint in the other case. Perhaps it's because I think such features are akin to "strong typing" in programming languages -- both prevent your from having particular kinds of bugs in your code but do not prevent you from having any of the gazillion of other kinds of bugs that it's at least as easy to have. Although I write in C# some of the time, I have a preference for so-called "dynamic" languages that don't pick one particular kind of bug to help me avoid. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
