Our First Table

Using SQLiteStudio, add a database called TheBooks (no space) and add a table called Clients. Configure the table like this:

First table created in SQLiteStudio

CREATE TABLE Clients (
ID INTEGER PRIMARY KEY,
Name VARCHAR (32) NOT NULL,
Address1 VARCHAR (32) NOT NULL,
Address2 VARCHAR (32),
Address3 VARCHAR (32),
Address4 VARCHAR (32),
Contact1 VARCHAR (32),
Contact2 VARCHAR (32),
Contact3 VARCHAR (32),
Contact4 VARCHAR (32)
);

SQLite automatically creates an internal field called rowid that appears to be just like the auto-increment ID fields we’re all used to. You can even access this field in SQL, as in SELECT rowid FROM table WHERE rowid = 3;. At first glance, it seems you don’t have to create ID fields anymore, but this is not so. It turns out that foreign keys cannot point to rowids. They must point to fields you create in the table.

But wait, it gets a little stranger. If you create an INTEGER field and designate it as PRIMARY KEY (and just that), this integer field will be an alias for the internal rowid. But you should make it AUTOINCREMENT, right? No, you should not! This is because SQLite already increments rowid by one every time. If you use AUTOINCREMENT, your field will increment fine, but it won’t be an alias of rowid. It will be an ordinary field designated as primary key.

And what is the problem with that? It seems SQLite stores and manages rowid in such a way that it performs much faster than ordinary primary keys. It depends on the application and the storage media, but I’ve seen reports that the performance difference is in the 8-12%.

What’s the takeaway? Create your integer ID fields as usual, don’t call them AUTOINCREMENT, and let SQLite alias them to rowid.

Leave a Reply

Your email address will not be published. Required fields are marked *