Sqlite

- 148 words - 1 minute

Foreign keys

Parent or referenced table, artistid is the parent key: [1]

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

Child or referencing table, trackartist is the child key:

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

Index on the child key column [2] :

CREATE INDEX trackindex ON track(trackartist);

Foreign key constraints are disabled by default, to enable [3] :

PRAGMA foreign_keys = ON

Composite foreign keys [4] :

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)
);

References

  1. SQLite Foreign Key Support → Introduction to Foreign Key Constraints
  2. SQLite Foreign Key Support → Required and Suggested Database Indexes
  3. SQLite Foreign Key Support → Enabling foreign key support
  4. SQLite Foreign Key Support → Composite foreign key constraints