On Tuesday, 13 November, 2018 05:47, Thomas Kurz <sqlite.2...@t-net.ruhr> asked:
>May I ask why parent keys *must* have indexes? I don't see any >correlation between enforcing a client/parent-relationship and the >necessity for an index. I'm just asking to better understand. To me >it is clear that the parent column is to be declared as PRIMARY KEY >or UNIQUE, but why does it need to have an index? It does not. However in most database storage systems the "UNIQUE" and "PRIMARY KEY" attributes are implemented by a unique index. If you happen to be using one of the few (if any) systems that do not implement a UNIQUE (or PRIMARY KEY, which is the same thing spelled differently (with very minor differences)) through an index, and you are willing to suffer a table scan every single time a constraint needs to verify whether or not a parent record exists, then you are free NOT to have an index and just have it be unique (and no, prayerfully unique is not sufficient -- the database system must throw an error if you attempt to violate the unique requirement)). The same applies to indexes on the child columns of a foreign key constraint. They do not need to be indexed either if you are willing to suffer a table scan every time the existence of a child needs to be verified. >And do I have to manually index PRIMARY KEY columns as well or are >those auto-indexed? That is what is called "implementation dependent". If you were using an implementation that did not use an index to enforce uniqueness (and I do not think there are any) then you would need to define an index yourself if you wanted one. Since I do not think there is presently any implementation which does not use an index to enforce uniqueness, declaring a separate index merely creates a separate (and perhaps duplicate) index. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
