Foreign keys

Jan 16, 2022

Example:

PRAGMA foreign_keys = ON;

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

CREATE INDEX trackindex ON track(trackartist);

Foreign key constraints are disabled by default, they need to be enabled ^[1] .
artist it the parent or referenced table, track is the child or referencing table. ^[2]
artistid is the parent key, trackartist is the child key.
The index on the child key column is recommended for efficienty when adding or modifying rows in the parent table ^[3] .

A shorthand form uses REFERENCES in the column definition:

PRAGMA foreign_keys = ON;

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);

CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT, 
  trackartist INTEGER REFERENCES artist
);

CREATE INDEX trackindex ON track(trackartist);

Composite foreign keys

The child and parent keys are both composite keys ^[4] :

PRAGMA foreign_keys = ON;

CREATE TABLE album(
  albumartist TEXT,
  albumname TEXT,
  albumcover BINARY,
  PRIMARY KEY(albumartist, albumname)
);
		
CREATE TABLE song(
  songid     INTEGER,
  songartist TEXT,
  songalbum TEXT,
  songname   TEXT,
  FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
);

CREATE INDEX songindex ON song(songartist, songalbum);

Foreign keys

Composite foreign keys

References