On Fri, Nov 30, 2012 at 02:57:30PM +0000, Simon Slavin scratched on the wall: > Need someone more familiar with the design philosophy and source code than I > am (which is not at all). > > <http://www.sqlite.org/foreignkeys.html#fk_indexes> > > Says you need an index for anything which acts as a parent. I can > understand why under normal circumstances (large table) this would > be desirable. But is there anything which would /require/ it ?
It isn't just an index, it is a UNIQUE index. Parent columns must be unique, so a UNIQUE constraint (and the implicit index that comes with it) or an explicit UNIQUE index is required to enforce the uniqueness of the parent columns. Yes, it also happens to help with performance, but the primary motivation is to make sure FK references are unique. > I'm asking this because I'm in a situation where someone may make a > daughter table with a foreign key relationship and the code will not > know whether the appropriate index already exists. Either I can try > to parse various pieces of information to figure it out, or I can > have a rule that a new index is always created, and accept that this > index may sometimes be a duplicate of one which already exists. In theory, if the database is designed correctly, an FK will never reference something that doesn't already have a UNIQUE constraint on it. One might argue that if you're trying to setup an FK that references a column or set of columns that does not have a UNIQUE constraint, either the FK is broken or the parent table is broken. ...which is not to say a general purpose tool still needs to deal with this, as there are plenty of broken database designs out there. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
