On 28 Feb 2011, at 9:41am, rf rf wrote: > Looking at the documentation for the latest version I think I understand that > I must define indexes for all foreign keys, manually, with UNIQUE as > described in the documentation. If I don't then the database will give me an > immediate or deferred error. These Foreign Key indexes are NOT generated > automatically by the database when I define a FOREIGN KEY... REFERENCES... ON > DELETE.. constraint.
The relevant text is at <http://www.sqlite.org/foreignkeys.html#fk_indexes> I believe the requirement is that some index has to exist which ensures that the key has a UNIQUE constraint. You don't have to make one index per FOREIGN KEY, or an index which exactly matches the terms used in the FOREIGN KEY clause, as long as something on the table ensures appropriate UNIQUEness. Of course, making a UNIQUE index which exactly matches the FOREIGN KEY clause will, under most circumstances, not only ensure UNIQUEness, but also provide a fast and efficient way for SQLite to check the constraint. So it might be worth doing things that way as a standard. > I want to double check before I screw up my (sizable) database. I seem to > remember that most DBMS (eg Oracle) would create these indexes automatically. Right. SQLite doesn't. Partly because it's possible to create one index which takes care of many requirements and that will save database space, and SQLite has to worry more about space. But you can check to see whether you already have all that's required: insert a record in the child database. If it works, SQLite has all that's needed. If it doesn't you don't, so create one. If you created one and decide you don't need it, you can always 'DROP INDEX ...'. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
