Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Does a SQLite Foreign key automatically have an index?

Tags:

indexing

sqlite

foreign-keys

query-optimization

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?

like image 882
balpha Avatar asked Jun 05 '09 10:06

balpha

People also ask

Does foreign key automatically create an index?

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.

Does a foreign key have an index?

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.

Does SQLite automatically create index?

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.

Should every foreign key have an index?

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.

1 Answers

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

like image 54
Masci Avatar answered Sep 17 '22 15:09

Masci
Sign in to Comment

Related questions

Saving to disk an in-memory database
Choice of Database schema for storing folder system
No sqlite3.exe in SQLite3 Download Folder For Windows 64 Bit
on update current_timestamp with SQLite
Android SQLite - what does SQLiteDatabase.replace() actually do?
Simple example using System.Data.SQLite with Entity Framework 6
Using text as a primary key in SQLite table bad?
How to increase max pool size in ActiveRecord?
Conditional SQLite check constraint?
String storage size in sqlite
SQLite: bind list of values to "WHERE col IN ( :PRM )"
How to update an SQLite database with a search and replace query?
Android: SQLite transactions when using ContentResolver
Foreign Key constraint doesn't work
How to discover table properties from SQLAlchemy mapped object
What will a SQLiteCursor do if a column is null?
ORM performance: is greenDAO faster than ORMLite?
data type mismatch error when using ORDER BY
"Unknown command syncdb" running "python manage.py syncdb"

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!