SQLite Create and Drop Index
An index is a special data structure , which stores the values for an entire column (or columns) in a highly organized manner that is optimized for searching.
Indexes (or indices) are very special to a database lookups, because, this allows specific rows in a table to be found without having to scan every row in the table. Hence, indexes can provide a large performance boost to some types of queries. Indexes are always associated with only one table, but more columns of the table can be included in the indexes.
Usually, the indexes can allow duplicate values, but with the UNIQUE keyword restricts the duplicity for any attempt to insert or update a table with a nonunique value. As NULL isn’t considered a value, so a UNIQUE index will not prevent one or more NULLs. To prevent NULLs, you must indicate NOT NULL in the original table definition.
Dropping an index will remove the index from the database, but the associated table will remain intact.
CREATE [UNIQUE] INDEX index_name ON table_name ( column_name [, ...] );
index_name - Name of the index.
table_name - Name of the table with which the index is associated.
column_name - Name of the column(s)
Creating an Index on a Column
Here we are creating a table company.
CREATE TABLE company( com_id text(4), com_name text(15));
Here, we added an index named com_id_index on the column "com_id" of company table. Here is the statement.
CREATE INDEX com_id_index ON company (com_id);
Create Index on Multiple Columns
Now, we want to add an index named com_id_name on the columns "com_id" and "com_name":
CREATE INDEX com_id_name ON company (com_id,com_name);
This type of index is called composit index, because two or more columns collectively make this type index.
Create UNIQUE Index
If you want to add an index named unique_com_id on com_id column of company table, the following statement can be used.
CREATE UNIQUE INDEX unique_com_id ON company (com_id,com_name);
After creating a UNIQUE index if you are trying to insert a record into the table that already exists in the UNIQUE Index, this leads to an error message. In a UNIQUE index, there cannot be any duplicate entries.
Here is some examples:
sqlite> INSERT INTO company VALUES ('com_001', 'company-1'); sqlite> INSERT INTO company VALUES ('com_002', 'company-2'); sqlite> INSERT INTO company VALUES ('com_001', 'company-1'); Error: UNIQUE constraint failed: company.com_id, company.com_name
Here in the above you can see that uniqueness is defined by both columns collectively, not individually and uniqueness is violeted here.
Here is the list of indexes created on company table:
sqlite> .indices company com_id_index com_id_name unique_com_id
If you want to see database wise all indexes, use the following statement.
sqlite> SELECT * FROM sqlite_master WHERE type = 'index'; type name tbl_name rootpage sql ---------------------- ------------------ ---------- ---------- --------------------------------------------- index com_id_index company 3 CREATE INDEX com_id_index ON company (com_id) index com_id_name company 4 CREATE INDEX com_id_name ON company (com_id,c index unique_com_id company 5 CREATE UNIQUE INDEX unique_com_id ON company(
The COLLATE keyword is used to make a column case-insensitive associated with index. This means that values in the indexed column will sort without respect to the case.
If you want to create a caseinsensitive index on com_name, the following index statement can be used.
CREATE INDEX com_name_collate on company (com_name COLLATE NOCASE);
To get the details information for the table company, use the following command:
sqlite> .schema company CREATE TABLE company( com_id text(4), com_name text(15)); CREATE INDEX com_id_index ON company (com_id); CREATE INDEX com_id_name ON company (com_id,com_name); CREATE UNIQUE INDEX unique_com_id ON company (com_id,com_name); CREATE INDEX com_name_collate on company (com_name COLLATE NOCASE);
The DROP INDEX command will drop the index from the databse but will leave the associated table intact.
DROP INDEX index_name;
If you want to drop the index com_name_collate from the database, the following can be used.
DROP INDEX com_name_collate;