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