Wednesday, April 6, 2011

Android - Creating an Index or Primary Key in SQLite

If this is your first time working in a database, you should read this entire post before using these queries. For those of you who know all about Primary Keys and Indexes, here are the queries:


    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
"Create Table ExampleTable (Id INTEGER PRIMARY KEY, CreatedDate DATETIME);"
);
        db.execSQL(
"Create Index ExampleTable_CreatedDate_idx ON ExampleTable(CreatedDate);"
);
    }

This will create a Primary Key, Id, for a table named ExampleTable.
The second query would place an index on the CreatedDate DateTime column, allowing you to efficiently query records based on when they were created, or order by when they were created.

In almost all cases, you want a Primary Key. And if you aren't querying your data based on the primary key very often, you want at least one index too!

If you haven't used Primary Keys or Indexes before, you should learn about them before using them. They can greatly reduce the time it takes to query data, but if you use them incorrectly they can slow it down too.

W3Schools.com is a great resources to learn the basics about SQL.

3 comments: