Check if a table has an index

Check if the table holds an index on the specified columns and optionally check whether it is unique.

has_idx(conn, on_tbl, on_cols, keep_unique = FALSE, temp = FALSE)

Arguments

conn

An object of class SQLiteConnection to a sqlite database.

on_tbl

A table in the database.

on_cols

A character vector with the columns to check.

keep_unique

A logical. Additionally check if the index has the UNIQUE constraint. Default is FALSE.

temp

Should sqlite_temp_master be queried, instead of sqlite_master? Default is FALSE. This can be useful when looking for temporary tables and indexes on them.

Value

A logical.

Details

The function only checks exact matching of on_cols, that is, the order of columns in on_cols needs to correspond to the order of columns in the CREATE INDEX statement: If on_cols = c("col1", "col2"), but the database has "CREATE UNIQUE INDEX idx1 ON mytable(col2, col1)", the function will return FALSE.

Examples

conn <- connect_to_db(db_example("AcademicGraph.sqlite"))
#> The database connection is: 
#> src:  sqlite 3.38.5 [/tmp/RtmptPxKJr/temp_libpath72cc939d74f39/magutils/extdata/AcademicGraph.sqlite]
#> tbls: AuthorAffiliation, FieldsOfStudy, FirstNamesGender, author_coauthor,
#>   author_output, current_links, current_links_advisors, pq_advisors,
#>   pq_authors, pq_fields_mag, pq_unis
has_idx(conn, "author_output", "AuthorId", keep_unique = TRUE)
#> [1] FALSE