CODEWITHSUNDEEP
mysqlsql
Concerned_Citizen
Concerned_Citizen

Reputation: 6835

Error 1215 and 1146 When Sourcing MySQL Code

I am working through the "Learning MySQL" book published by O'Reilly and I am trying to source the following SQL code posted on the book's website: 

DROP DATABASE IF EXISTS music;
CREATE DATABASE music;
USE music;

CREATE TABLE artist (
    artist_id SMALLINT(5) NOT NULL DEFAULT 0,
    artist_name CHAR(128) DEFAULT NULL,
    PRIMARY KEY  (artist_id)
);

CREATE TABLE album (
    artist_id SMALLINT(5) NOT NULL DEFAULT 0,
    album_id SMALLINT(4) NOT NULL DEFAULT 0,
    album_name CHAR(128) DEFAULT NULL,
    PRIMARY KEY  (artist_id,album_id),
    FOREIGN KEY (artist_id) REFERENCES artist(artist_id)
);

CREATE TABLE track (
    track_id SMALLINT(3) NOT NULL DEFAULT 0,
    track_name CHAR(128) DEFAULT NULL,
    artist_id SMALLINT(5) NOT NULL DEFAULT 0,
    album_id SMALLINT(4) NOT NULL DEFAULT 0,
    time DECIMAL(5,2) DEFAULT NULL,
    PRIMARY KEY  (artist_id,album_id,track_id),
    FOREIGN KEY (artist_id) REFERENCES artist(artist_id),
    FOREIGN KEY (album_id) REFERENCES album(album_id)
);

CREATE TABLE played (
    artist_id SMALLINT(5) NOT NULL DEFAULT 0,
    album_id SMALLINT(4) NOT NULL DEFAULT 0,
    track_id SMALLINT(3) NOT NULL DEFAULT 0,
    played TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY  (artist_id,album_id,track_id,played),
    FOREIGN KEY (artist_id) REFERENCES artist(artist_id),
    FOREIGN KEY (album_id) REFERENCES album(album_id),
    FOREIGN KEY (track_id) REFERENCES track(track_id)
);

-- And the whole bunch of data input into those tables. 
INSERT INTO played VALUES (1, 3, 0, "20060814102103");
INSERT INTO artist VALUES (1, "New Order");
INSERT INTO album VALUES (2, 1, "Let Love In");
INSERT INTO track VALUES (0,'Do You Love Me?',2,1,'5.95');

However, when I tried SOURCE MySQL is giving me ERROR 1215 (HY000): Cannot add foreign key constraint and ERROR 1146 (42s02): Table 'music.track' doesn't exist. I've been mulling over this for a while. What seems to be wrong?

Upvotes: 3

Views: 2050

Answers (2)

Tim Lehner
Tim Lehner

Reputation: 15251

Your foreign key must reference an entire candidate key.

CREATE TABLE track (
    track_id SMALLINT(3) NOT NULL DEFAULT 0,
    track_name CHAR(128) DEFAULT NULL,
    artist_id SMALLINT(5) NOT NULL DEFAULT 0,
    album_id SMALLINT(4) NOT NULL DEFAULT 0,
    time DECIMAL(5,2) DEFAULT NULL,
    PRIMARY KEY  (artist_id,album_id,track_id),
    FOREIGN KEY (artist_id) REFERENCES artist(artist_id),
    /* This won't work, not referencing an entire key: */
    /*FOREIGN KEY (album_id) REFERENCES album(album_id)*/
    /* This should work: */
    FOREIGN KEY (artist_id,album_id) REFERENCES album(artist_id,album_id)
);

You'll have to make a similar change in the played table.

Here is a SqlFiddle of those changes, including inserting data in the right order, with existing values of keys.

There is also the idea that if album_id is it's own candidate key (it's unique), then just make that the primary key of album. You wouldn't need to have the artist_id column in track, then, as you know the artist of a track by the album. You can carry this out to other child tables (played) as well.

Of course if you're using track_id as the ordinal on an album (it's not unique, every album probably has a track_id = 1), then you should stick to your compound keys, or make surrogate keys with other unique constraints.

Having defaults on primary keys is puzzling as well, considering they'd only work for the first insert.

Upvotes: 1

Martin
Martin

Reputation: 16433

The reason your code is failing is because you have a compound primary key on the album and track table. In order to allow the foreign key to be created successfully, you must add another index to each table for each column of the compound key.

Try modifying your code for each table to add an index as follows. On the album table:

INDEX (album_id),

On the track table:

INDEX (track_id),

So your complete code looks like this (excluding the database creation and inserts):

CREATE TABLE artist (
    artist_id SMALLINT(5) NOT NULL DEFAULT 0,
    artist_name CHAR(128) DEFAULT NULL,
    PRIMARY KEY  (artist_id)
);

CREATE TABLE album (
    artist_id SMALLINT(5) NOT NULL DEFAULT 0,
    album_id SMALLINT(4) NOT NULL DEFAULT 0,
    album_name CHAR(128) DEFAULT NULL,
    PRIMARY KEY  (artist_id,album_id),
    INDEX (album_id),
    FOREIGN KEY (artist_id) REFERENCES artist(artist_id)
);

CREATE TABLE track (
    track_id SMALLINT(3) NOT NULL DEFAULT 0,
    track_name CHAR(128) DEFAULT NULL,
    artist_id SMALLINT(5) NOT NULL DEFAULT 0,
    album_id SMALLINT(4) NOT NULL DEFAULT 0,
    time DECIMAL(5,2) DEFAULT NULL,
    PRIMARY KEY  (artist_id,album_id,track_id),
    INDEX (track_id),
    FOREIGN KEY (artist_id) REFERENCES artist(artist_id),
    FOREIGN KEY (album_id) REFERENCES album(album_id)
);

CREATE TABLE played (
    artist_id SMALLINT(5) NOT NULL DEFAULT 0,
    album_id SMALLINT(4) NOT NULL DEFAULT 0,
    track_id SMALLINT(3) NOT NULL DEFAULT 0,
    played TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY  (artist_id,album_id,track_id,played),
    FOREIGN KEY (artist_id) REFERENCES artist(artist_id),
    FOREIGN KEY (album_id) REFERENCES album(album_id),
    FOREIGN KEY (track_id) REFERENCES track(track_id)
);

Upvotes: 1

Related Questions