SQLite - Create a Relationship

SQLite supports relationships just like any other relational database management system.

SQLite is a relational database management system (RDBMS). It uses the same relational model that other popular DBMSs (such as MySQL, Oracle, SQL Server, MS Access) use.

What this means, is that you can create multiple tables, then have them linking to each other via a relationship.

A relationship is where you have multiple tables that contain related data, and the data is linked by a common value that is stored in both tables.

The following diagram illustrates this concept:

Diagram of a database relationship.

So, let's add another table called Albums, then have that linked to our Artists table via a relationship.

Doing this will enable us to lookup which artist a given album belongs to.

Create the New Table

So let's go ahead and create the Albums table:

Similar to when we created the Artists table, however, on this one, we have added FOREIGN KEY(ArtistId) REFERENCES Artists(ArtistId) to the end of the statement.

This creates a foreign key constraint on the Albums.ArtistId column. What this means is that, any data that is inserted into this column, must match a value in the Artists.ArtistId column.

If we didn't do this, it would be possible to have an album that doesn't belong to an artist. In other words, we could have orphaned records in our database. Not good if you're trying to maintain referential integrity.

Now, if we run a .tables command, we should see both tables in the database:


sqlite> .tables

Albums   Artists

Test the Relationship

Once we've created the table with the foreign key, we can test it by attempting to enter erroneous data. We can try to enter an album with an ArtistId that doesn't match an ArtistId in the referenced table (i.e. the Artists table):

This should result in the following:


sqlite> INSERT INTO Albums (AlbumName, Year, ArtistId)

   ...> VALUES ('Powerslave', '1984', 70);

Error: FOREIGN KEY constraint failed

Also, running a SELECT statement on the table will return no data.

This is because the foreign key constraint blocked the wrong value from being inserted.

Didn't Work?

If you don't receive an error when trying to enter erroneous data like this, you may need to check your settings.

Run the following command: PRAGMA foreign_keys;

If this results in 0 it means that your foreign key constraints are disabled. In fact, this is the default behaviour of SQLite (it's for backwards compatibility).

To enable foreign key constraints, type the following PRAGMA foreign_keys = ON;

Now, running PRAGMA foreign_keys; should return 1, and subsequent attempts at inserting an invalid foreign key will fail.

However, if the PRAGMA foreign_keys; command returns no data, your SQLite implementation doesn't support foreign keys (either because it is older than version 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).

Insert More Data

Now that the relationship has been established, we can add as much data as we need, with the confidence that only records with valid foreign keys will be inserted.

Next, we'll select data from both tables using a JOIN statement.