ERROR 1005 (HY000): Can't create table 'test.sports' (errno: 150)

Question

It is throwing error:

ERROR 1005 (HY000): Can't create table 'test.sports' (errno: 150).

I need help in resolving it.

 CREATE TABLE sports(
-> interest text,
-> prize_money int,
-> sp_id int NOT NULL,
-> CONSTRAINT fk_sports 
-> FOREIGN KEY(sp_id)
-> REFERENCES records(id)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE
-> ) ENGINE=INNODB;

+------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | student_id | int(11) | NO | PRI | 0 | | | subject | text | YES | | NULL | | | marks | int(11) | YES | | NULL | | | teacher | varchar(20) | YES | | NULL | | | sex | tinytext | YES | | NULL | | | age | int(11) | YES | | NULL | | +------------+-------------+------+-----+---------+ — deka4tech, Nov 25 '16 at 03:02
@TimBiegeleisen:Its not understandable...can a image be uploaded in the comment section?? — deka4tech, Nov 25 '16 at 03:04
The `records` table has no `id`column. Try using `REFERENCES records(student_id)` instead. — Tim Biegeleisen, Nov 25 '16 at 03:05
@TimBiegeleisen: One more thing. after the creation of the sports table, the id column is showing MUL as the key. what that means — deka4tech, Nov 25 '16 at 03:11
+-------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+-------+ | interest | text | YES | | NULL | | | prize_money | int(11) | YES | | NULL | | | sp_id | int(11) | NO | MUL | NULL | | — deka4tech, Nov 25 '16 at 03:14
`"MUL means that the key allows multiple rows to have the same value"` .. check here: http://stackoverflow.com/questions/5317889/sql-keys-mul-vs-pri-vs-uni — Tim Biegeleisen, Nov 25 '16 at 03:14

score 0 · Answer 1 · answered Nov 25 '16 at 03:08

Your records table does not have a column called id. Rather, its primary key column is student_id, so you probably intended to reference this from the foreign key in sports. Try this definition:

CREATE TABLE sports(
    interest text,
    prize_money int,
    sp_id int NOT NULL,
    CONSTRAINT fk_sports 
    FOREIGN KEY(sp_id)
    REFERENCES records(student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=INNODB;

ERROR 1005 (HY000): Can't create table 'test.sports' (errno: 150)

1 Answers1