Junction Table in SQLite SQL Database with Cascade on Update and Delete

This is a complementary blog post to the video I've posted on the topic. In this blog post I will simply provide what to type into the command line of SQLite. The video is about the concept of using a junction table (also called a linking table) which is the "bridge" between two tables. This gives you a layout of three tables which is very useful in many practical applications and conforms to the normalization rules of database design. You will have one table with artists and one table with albums, and through the junction table you can link the artist to the album.

First you will start on a new SQLite database file by typing "sqlite database.db" or "sqlite3 database.db" which will open the command line and create the database file in the current directory. The cascading will not work in sqlite3 (and not in previous versions at all, since there was no support for the foreign key) unless you initially type in:

pragma foreign_keys = ON;

This must be done every time SQLite is started (unless you configure the startup settings).

Create the tables:

artist:

create table artist (
artist_id integer PRIMARY KEY AUTOINCREMENT,
artist_name text NOT NULL);

album:

create table album (
album_id integer PRIMARY KEY AUTOINCREMENT,
title text NOT NULL,
year integer NOT NULL,
tracks integer NOT NULL);

artistalbum: <- junction table

create table artistalbum (
artistalbum_id integer PRIMARY KEY AUTOINCREMENT,
artist_id integer NOT NULL,
album_id integer NOT NULL,
CONSTRAINT fk_artist_id
FOREIGN KEY (artist_id)
REFERENCES artist (artist_id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_album_id
FOREIGN KEY (album_id)
REFERENCES album (album_id) ON UPDATE CASCADE ON DELETE CASCADE
);

Add data:

insert into artist (artist_name) VALUES ('ABC');
insert into album (title,year,tracks) VALUES ('CDE',1999,3);
insert into album (title,year,tracks) VALUES ('DEF',1999,3);

Add data in the junction table so that "ABC" has 2 records (the added albums):

insert into artistalbum (artist_id, album_id) VALUES ( (SELECT artist_id FROM artist WHERE artist_name = 'ABC'),
(SELECT album_id FROM album WHERE title = 'CDE') );

insert into artistalbum (artist_id, album_id) VALUES ( (SELECT artist_id FROM artist WHERE artist_name = 'ABC'),
(SELECT album_id FROM album WHERE title = 'DEF') );   

Get all tables:

SELECT artist.artist_name, album.title, album.year, album.tracks
FROM artist JOIN artistalbum ON artist.artist_id = artistalbum.artist_id
JOIN album ON album.album_id = artistalbum.album_id;

Get from all tables where the band name is 'ABC':

SELECT artistalbum.artistalbum_id, album.title, artist.artist_name
FROM artist INNER JOIN (album INNER JOIN artistalbum ON album.album_id = artistalbum.album_id)
ON artist.artist_id = artistalbum.artist_id
WHERE artist.artist_name='ABC';

Delete records using cascade from the "associate table", i.e. the album or the artist table:

DELETE FROM album WHERE title = 'DEF';

You can now notice the that data has been removed from both the "associate table" and the junction table. Make a delete from the album table and all the records in the artistalbum table will also be deleted. However, the artist table will not be effected, so an additional delete must be made there as well.

Check out the video for more details on how this works.