SQLite - Create a Relationship
SQLite supports relationships just like any other relational database management system.
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:
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.
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:
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;
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
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