Sorry if this is in the wrong forum but it appears daniweb doesn't have a category for sqlite or miscellaneous database systems. I have just started on a personal project which is an admin interface for sqlite (Like sqliteMyAdmin), similar to Phpmyadmin but for sqlite.

Anyways the question is how do I add indexes to a table when creating a table and what indexes are available for use. The only one I know of is PRIMARY KEY . Does anybody know?


I have already read that page but its examples aren't very useful. However I have done a lot of research and is the following query a valid query with valid indexing?

CREATE TABLE IF NOT EXISTS table_name (column_name1 INTEGER PRIMARY KEY, column_name2 TEXT UNIQUE, column_name3 INTEGER DEFAULT 0

Just found out the answer to the previous post... that sql didn't work due to the fact that sqlite2 doesn't accept the "IF NOT EXISTS" statement. But that leads to another question. Does sqlite accept regex? Because I am looking for a query like the following but in valid syntax.

SELECT * from sqlite_master WHERE sql LIKE /^*create table*$/i

So in that regex within the query it searches for the term "create table" case insensitive. Does anybody know how to do this as I'm new to sqlite?

bumpedy bump bump.
Is anybody going to reply?

You should know also where to use indexex and where not.