Menu
I know that SQLite does not enforce foreign keys natively, but that's not my primary concern. The question is: If I declare
CREATE TABLE invoice ( invoiceID INTEGER PRIMARY KEY, clientID INTEGER REFERENCES client(clientID), ... ) will sqlite at least use the information that clientID is a foreign key to optimize queries and automatically index invoice.clientID, or is this constraint a real no-op?
882
When you define a foreign key constraint in your database table, an index will not be created automatically on the foreign key columns, as in the PRIMARY KEY constraint situation in which a clustered index will be created automatically when defining it.
MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created. Information about foreign keys on InnoDB tables can also be found in the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables, in the INFORMATION_SCHEMA database.
By the same token, you should also omit explicit declaration of indexes on columns declared unique or unique constraints in the table definition. SQLite3 will create these indexes automatically.
Every foreign key? No. Where the selectivity is low (i.e. many values are duplicated), an index may be more costly than a table scan. Also, in a high activity environment (much more insert/update/delete activity than querying) the cost of maintaining the indexes may affect the overall performance of the system.
Even if it is not actually a no-op (a data structure describing the constraint is added to the table), foreign key related statement doesn't create any index on involved columns. Indexes are implicitly created only in the case of PRIMARY KEY and UNIQUE statements. For more details, check it out build.c module on the sqlite source tree: http://www.sqlite.org/cvstrac/rlog?f=sqlite/src/build.c https://www.sqlite.org/src/file?name=src/build.c&ci=tip
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
