SQLite is a self-contained SQL database engine. This means that an application is able to make use of all of the benefits of a rational database without needing to provide additional server installations like PostgreSQL or Microsofts SQL Server. This means that you are easily able to share the database between multiple operating systems, and architectures (32/64 bit) simply by coping the file on to the target machine. As with all things; there are appropriate uses of SQLite, which are many, there are also times when a client/server rational database management system may work better.
Much of this page however; will cover SQL in general and be appropriate for any system.
As a introduction to SQLite we'll make use of SQLite Browser to create a contacts database which we'll add some test contacts too. This page will focus on using SQL directly to create the tables, and information stored in them. However all of this can be done, one way or another by using the SQLite Browser UI. All of the following will be done within the 'Execute SQL' tab, with the exception of creating the database file itself.
Within SQLite Browser you need to choose the "New Database" option, doing so will open a save dialogue. This will be the location on disk that the contacts database file gets stored; choose any location and name the file
contacts.db. Once you have chosen the file location you'll be prompted to create a new table, cancel this for the time being and then select the 'Execute SQL' tab.
Each database is made up of one, or more, tables while a table is a set of data elements. The data stored within a table is stored in rows, each row is made up a selection of values, columns. Each of the columns will have an associated data type, which get defined while creating the table and is known as the schema. SQLite has a number of available data types.
CREATE TABLE statement is used to create a new table. Creating a table involves naming the table and then defining the column names and data types. As an example, to create a table called
contacts which stores a first name and family name we'd run:
CREATE TABLE "contacts" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "firstname" TEXT NOT NULL, "family_name" TEXT );
This will create a new table with 3 columns:
id - The ID is used as a unique identifier for the table; each time we create a entry in the table it will automatically create a unique integer value and store it in the column. This column will allow us to link tables together
firstname - A text column that must have a value, here we're saying that each of our contacts must at least have a first name.
family_name - A optional text column for a family name.
The final two tables we're creating will look very similar and can be created with the following statements:
CREATE TABLE "numbers" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "number" TEXT NOT NULL, "contact_id" INTEGER NOT NULL, FOREIGN KEY("contact_id") REFERENCES contacts("id") ON DELETE CASCADE ); CREATE TABLE "email_addresses" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "address" TEXT NOT NULL, "contact_id" INTEGER NOT NULL, FOREIGN KEY("contact_id") REFERENCES contacts("id") ON DELETE CASCADE );
These statements will create two tables
email_addresses. The numbers table will be used to store all telephone numbers associated with a contact. You may notice that we're storing numbers in a
TEXT field instead of a
NUMBER, while this might seem a little strange at first there is a good reason for it. Both
NUMERIC fields are designed to store a particular type of number (1, or 1.0 for example). Phone numbers are a little more tricky, if we wanted to store a international dialling code for example we'd need to do so in a separate column. Telephone numbers such as "+44 (0)1234 123456" would not be stored in anything by a
TEXT field. So because of the diversity of available numbers it's much simpler for the end user to not have to worry.
Both of these tables also have a
contact_id column, this is going to hold special meaning within the table. We're setting it as a
FOREIGN KEY which is a reference to another tables
PRIMARY KEY and provides a link between records in different tables. Here we're saying that each entry in either table must link back to a valid
contact by referencing the
id primary key in the
contacts table. The value of the
contacts primary key, will be stored locally in the
contact_id column. When we later remove a record from the
contacts table the removal will cascade through each table that has a
FOREIGN KEY and remove it as well, therefore keeping our data consistent.
As of version 3.6.19 of SQLite foreign key constraints are disabled by default and require the
foreign_key_pragma option to enable them. To do this select the 'Edit Pragmas' tab and check the 'Foreign Keys' and hit save. If you don't enable this; then there will be no enforced link between
When you're creating your database you don't know how many phone numbers someone might have; or how many email addresses they have. So it's impossible for you to correctly determine the number of columns you need. You could specify just a Home, Mobile and Work number then a Personal and Work email address but that's not very user-friendly, after all I might have two work numbers!
By using different tables we're able to offer a unlimited number of phone numbers and email addresses for each contact and cleanly reference them from any contact. So if you're in a situation where you don't know how many items of a particular type separate tables might be exactly what you need.
Once all of the changes required have been made to the
contacts database you need to commit the changes, to do this choose 'Write Changes' or press
Ctrl+S. SQLite Browser works on a memory copy of the data, so any changes you made will not be saved to the file until you have done this. This process is known as committing the changes.
Contacts are added using the
INSERT query is used to add new records into a table; it has one of two basic forms:
INSERT INTO table(col1, col2) VALUES(1, 2); INSERT INTO table VALUES(1, 2);
The second variation will only work if you are insert values into all of the columns for a given table, and when doing it you need to make sure that the values are in the correct order. The recommendation would be to use the first form always however; so that you're being explicit in what you're attempting to do.
To insert a new contact, we would run:
INSERT INTO contacts(firstname, family_name) VALUES('Adam', 'Jefferiss');
If you attempt to insert solely into the
family_name column we'd get a error shown in the SQL output.
Before we insert records into the other tables we need to know the
id of the newly inserted row so that we can use it as the
FOREIGN KEY constraint. To do this immediately after running the
INSERT query you can run:
Once you have this number; you can use it to create records in the other tables like so:
INSERT INTO numbers(number, contact_id) VALUES("+44 (0)1234 567890", 1);
You'll notice that for this insert statement, we're not putting single or double quotes around the
contact_id. The basic rule is that an strings (words, characters, etc) need to be quoted using either single or double quotes. While integers do not.
If you attempt to create a record with an invalid contact ID you'll be present with a error message about a failed
FOREIGN KEY constraint like so:
FOREIGN KEY constraint failed: INSERT INTO numbers(number, contact_id) VALUES('22222222', -111);
If you have multiple values you want to insert into a given table you can do so by providing a
VALUES list for each entry, for example if we wanted to add 2 numbers for contact 1 we could run:
INSERT INTO numbers(number, contact_id) VALUES('+44 12345 678931', 1), ('+44 12345 678932', 1);
If you do insert multiple record bear in mind that
SELECT last_insert_rowid(); will only return the last row inserted, not the ids of the rows effected by the last statement. So if you need to retrieve the
PRIMARY KEY for a record inserting multiple values is not the way to go!
To find contacts within the database we need to use the
SELECT SQL command; this command has two basic forms:
SELECT id, firstname, family_name FROM contacts; SELECT * FROM contacts;
The first form is provided a list of columns you wish to retrieve information about; and the second uses an asterisk,
*, to mean 'all columns'. In the example above both commands will return all of the information from within the
contacts table. Considering the following table:
Using the WHERE clause we're able to limit the results of our select statements; there's a number of logical operators that can be used to limit the results. If we knew, for example, that the
firstname of our contact contained a 'a' we could
SELECT all contacts with an 'a' in the
firstname using the
SELECT id, firstname, family_name FROM contacts WHERE firstname LIKE '%a%';
From our table above; this would give us records 1 and 3. Within the
LIKE statement there are two special characters, the percent sign (%) and a underscore (_). The percent sign represents zero or more numbers or characters while the underscore represents a single number or character. So if we reran our
SELECT query with a
LIKE value of
_a% we'd only get the single row back for Han Solo. The reason for this is that we're saying there must be only 1 character, or number, in the
firstname before the 'a', but there can be any number of characters or numbers after the first 'a'.
If we wanted to find all of the Skywalker family; we'd use:
SELECT id, firstname, family_name FROM contacts WHERE family_name = 'Skywalker';
If we run this on our current set of data though we'd miss off the record for Ben Skywalker! This might seem a little confusing given what we've seen with the
LIKE condition. The
LIKE condition has the
case_sensitive_like pragma which by default is
false, this means that
LIKE does case insensitive comparisions, so 'A' and 'a' are both the same in the eyes of
= however is a exact match against the entry you've put. To get around this we can make use of two functions in SQLite, upper and lower. These functions return a copy of a string as either upper or lower case. The following SQL will take a copy of the
family_name column, and turn it to lower case before comparing it against 'skywalker'. Doing so will return us all of the rows we expect!
SELECT id, firstname, family_name FROM contacts WHERE LOWER(family_name) = 'skywalker';
Finally we can use the
IS NOT, which behave like
!= to see what columns have values. If a column does not have a value, it is considered to be NULL, as such we can write the following two queries
SELECT id, firstname, family_name FROM contacts WHERE family_name IS NULL; SELECT id, firstname, family_name FROM contacts WHERE family_name IS NOT NULL;
These queries will return the opposite records of each other; the first will return records for R2D2 and C3PO from the table above; while the second will return everything else.
So far we've only been querying the
contacts table; which doesn't actually do anything helpful. After all we need to find the email addresses and phone numbers of our contacts! Once you've created a few entries in both tables you can query them using JOINS or SUBQUERIES. Beyond our
contacts table above, we're going to be executing the following SQL against the
email_addresses tables with the following information.
|id||Email Address||Contact ID|
There are three types of JOINS available in SQLite
INNER JOIN, through there are technically three types of outer joins defined within the SQL standard SQLite only supports a
LEFT OUTER JOIN. The condition used by an outer join are identical those of an inner join, however once the primary join has been calculated an outer join will take any unjoined rows and pad them with NULLS before appending them to the resulting table.
A sub-query could also be known as either a nester or inner query, and is a query embedded within the
WHERE clause of a query. A sub-query is used to return data that will be used in the main query as a condition to restrict retrieved data. There are a few rules that each sub-query must follow:
To return the number of a specific person; we could use:
SELECT number FROM numbers WHERE contact_id IN ( SELECT id FROM contacts WHERE firstname = 'Adam' AND family_name = 'Jefferiss' );
To return the numbers of anyone in the Skywalker family we could use the following query:
SELECT number FROM numbers WHERE contact_id IN ( SELECT id FROM contacts WHERE LOWER(family_name) = 'skywalker' );
To return the numbers and email address for a given we could use:
SELECT n.number, e.address FROM numbers n, email_addresses e WHERE n.contact_id IN (SELECT id FROM contacts WHERE firstname = 'R2D2') AND e.contact_id IN (SELECT id FROM contacts WHERE firstname = 'R2D2')
The command to remove a row from the
contacts table we'll use the
DELETE SQL command, this command takes the table name and optionally a condition to match via the WHERE clause. If you do not limit the
DELETE it will remove everything from the table!
id of the contact you want to remove you can execute a statement like the following, which will remove all
email_addresses for the contact as well, as an example to remove everything for contact 9 we'd run:
DELETE FROM contacts WHERE id = 9;
If you do not know the
id of a contact; you can remove it using any of the other columns; but doing so may be risky as you will remove any rows with a matching column. For example if we had two rows, one for 'Adam Jefferiss' and another for 'Adam Smith' we'd end up deleting both records if we ran:
DELETE FROM contacts WHERE firstname = 'Adam';
So it is worth finding the ID of the record before attempting any deletes. If you just wanted to remove all of the numbers for a given contact, for example, you could run.
DELETE FROM numbers WHERE contact_id = 9;