CREATE a foreign key

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

The relationship between the tables is not enforced. Even when the album table is empty we can create a record in the track table:

INSERT INTO track VALUES
('1122334455',1,1,'song')

The relationship between the tables is not enforced. Even when the album table is empty we can create a record in the track table:

INSERT INTO track VALUES
('1122334455',1,1,'song')

We can force MySQL to respect foreign keys if we use the InnoDB table type. See MySQL Reference Manual.

CREATE and DROP Operations

CREATE a new table

DROP a table

Composite primary key

CREATE a foreign key

CREATE a VIEW

Autonumber fields

ALTER TABLE ... ADD COLUMN

ALTER TABLE ... DROP COLUMN

ALTER TABLE ... ADD constraint

CREATE TABLE problems: Invalid column name.

CREATE TABLE problems: Insufficient privileges.

CREATE TABLE problems: Table already exists.

DROP TABLE problems: Foreign key references.

CREATE TABLE problems: Foreign key references.

Rename column