CREATE a foreign key
From SQLZoo
CREATE TABLE with a foreign key
schema:scott
A foreign key references is where one field "links" to another table. The database will maintain referential integrity - this means that there must be a corresponding record in the other table - in this example each track must be on an album that exists in the album table.
The field (or fields) linked to must be unique - usually the other field is the primary key of the other table.
In this example we store details of tracks on albums - we need to use three columns to get a unique key - each album may have more than one disk - each disk will have tracks numbered 1, 2, 3...
DROP TABLE track;
DROP TABLE album
CREATE TABLE album(
id CHAR(10) NOT NULL PRIMARY KEY,
title VARCHAR(100),
artist VARCHAR(100)
);
CREATE TABLE track(
album CHAR(10),
dsk INTEGER,
posn INTEGER,
song VARCHAR(255),
FOREIGN KEY (album) REFERENCES album(id)
)
CREATE TABLE album(
id CHAR(10) NOT NULL PRIMARY KEY,
title VARCHAR(100),
artist VARCHAR(100)
);
CREATE TABLE track(
album CHAR(10),
dsk INTEGER,
posn INTEGER,
song VARCHAR(255),
FOREIGN KEY (album) REFERENCES album(id)
);