Error 1215 and 1146 When Sourcing MySQL Code

Question

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?

score 1 · Answer 1 · answered Apr 04 '13 at 19:37

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

Does this assume that `album.album_id` and `track.track_id` are both unique? — Tim Lehner, Apr 04 '13 at 20:02
Interesting. As primarily a SQL Server developer, it's both intriguing and scary to me to [reference something that's not unique](http://stackoverflow.com/questions/2362163/mysql-foreign-key-by-non-unique-key-how-is-that-possible). — Tim Lehner, Apr 04 '13 at 20:26
I thought you were asking if it was created as unique index, not whether the columns themselves contain unique values. I presume they do although it's not my design. — Martin, Apr 04 '13 at 20:30
I personally would not have used compound primary keys, as I would have been content with using the unique id column for each table in question. The OP didn't ask for advice about database design though, he just asked why he was getting those two error messages. — Martin, Apr 04 '13 at 20:34
Your code also works! But how is this approach different from Tim's? Are there any performance differences between yours and his? I am a SQL beginner and I really appreciate your input. — Concerned_Citizen, Apr 04 '13 at 21:33
I believe Tim's example is more valid if you are using compound primary keys (you don't have to). However, I do not know what the performance difference is. My guesstimate would be that my example will be quicker as it does not use the compound key but without testing, I don't know for sure. — Martin, Apr 04 '13 at 21:47

Tim Lehner · Accepted Answer · 2013-04-04T20:21:18.613

1

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.

edited Apr 04 '13 at 20:21

answered Apr 04 '13 at 19:37

Tim Lehner

14,813
4
59
76

I've added a [SqlFiddle](http://www.sqlfiddle.com/#!2/bdc53) to the answer showing those changes. Note that data should be inserted in the proper order as well (artist then album, etc.). – Tim Lehner Apr 04 '13 at 20:17
I just copied and pasted your code and it's giving me `Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails( `music`.`played`, ed_ibfk_1 FOREIGN KEY (`artist-id`) REFERENCES `artist` (`artist_id`)) – Concerned_Citizen Apr 04 '13 at 20:47
I'm guessing you are using your own insert statements, which are A: out of order (child before parent?) and B: attempt to insert values into child tables which don't exist in parent tables (even if they were in the correct order). Please notice the insert statements I am using in the SQL Fiddle link. – Tim Lehner Apr 04 '13 at 20:51
You're right! I don't even know why the authors posted such buggy code (granted the book is not cheap). So it worked but I am a beginner and I just can't wrap my head with foreign keys, references, etc. Why do we need to reference entire candidate key? – Concerned_Citizen Apr 04 '13 at 21:24
[This wiki article](http://en.wikipedia.org/wiki/Foreign_key) may have helpful info as to why. – Tim Lehner Apr 04 '13 at 21:36

Error 1215 and 1146 When Sourcing MySQL Code

2 Answers2