Databases with SQLite

Keys

Primary Key

In order to define the relation between various tables in your database, keys are the ... key to proper design.

An important part of designing a table is to define the primary key, consisting of 1 or more columns in the table.

Primary keys must uniquely identify each row of a table
and every possible future row.

Many times the primary key is already unique, such as a part number, MAC address, or employee ID number. Thus, names or phone numbers are not a good choice for a primary key.

If no column is available, we can create our own, as we do in the next page.

You define a primary key when you create the table.

This primary key will be used significantly later in your queries.

In my books database, I have a table for Subjects, with an AuthorID, TitleID, and Subject.

Since none of those rows would be unique (several subjects may refer to several titles), I created one called id.

create table subjects (
id integer primary key autoincrement,
authorID integer,
titleID integer,
subject text
);

The autoincrement will automatically increment the integer by 1 when a new subject is inserted, thus ensuring uniqueness.

Foreign Key

Now that we have a primary key for each table, we need to define how the tables are related to each other. Enter the foreign key.

We define a foreign key by referring to the primary key/s of other tables - a pointer.

This is also done as part of the table creation statement.

If no column is indicated in a foreign key definition the primary key of that table is used.
Foreign keys may be defined as Null or Not Null. Null is not an empty row or blank. It is unassigned or unknown.
Create Table