0

I don't know why the error code keeps popping up. What i'm trying to do is set the Genre column in my Album table to a foreign key that links to the Genre column in the Genre Table. But every time I try to implement Genre as a foreign key in the SQL command on Albums, I keep getting error 150 with no indication to what the error can be. As the field information is identical in both tables.

Code behind my Albums table 

CREATE TABLE `Albums` (  
`Album_id` int(6) NOT NULL auto_increment,
`Album_Name` varchar(32) NOT NULL,  
`Number_Of_Tracks` int(11) NOT NULL,    
`Genre` varchar(32) NOT NULL,  
`Track_id` int(6) NOT NULL,  
`Artist_id` int(6) NOT NULL,  
PRIMARY KEY  (`Album_id`),  
KEY `Track_id` (`Track_id`),  
KEY `Artist_id` (`Artist_id`),  CONSTRAINT `Albums_ibfk_1` 
FOREIGN KEY (`Track_id`) REFERENCES `Tracks` (`Track_id`),  CONSTRAINT `Albums_ibfk_2`     
FOREIGN KEY (`Artist_id`) REFERENCES `Artist` (`Artist_id`)) 
ENGINE=InnoDB 
DEFAULT CHARSET=latin1

Code behind my Genre table

CREATE TABLE `Genre` (  
`Genre_id` int(11) NOT NULL auto_increment, 
`Genre` varchar(32) NOT NULL,  
PRIMARY KEY  (`Genre_id`)) 
ENGINE=InnoDB DEFAULT CHARSET=latin1

Code i'm using on SQL to try and make Genre in Albums a Foreign key

ALTER TABLE Albums
ADD FOREIGN KEY (`Genre`)
REFERENCES Genre (`Genre`)

Can anyone help me understand what's going wrong?

Henry Green
  • 243
  • 3
  • 4
  • 15
  • 3
    InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. (http://dev.mysql.com/doc/refman/5.1/en/create-table-foreign-keys.html). **Please DO READ THE DOCUMENTATION**. Furthermore, I recommend to use an int(11) column in the `albums` table and use the existing numeric key as the reference (instead of the character representation of the genre) – Pred Mar 17 '16 at 15:43
  • So because genre in the genre table isn't the 1st column that's why it's coming up with the error? – Henry Green Mar 17 '16 at 16:17
  • Look here: http://stackoverflow.com/questions/17691282/error-code-1215-cannot-add-foreign-key-constraint-foreign-keys – White Feather Mar 17 '16 at 16:25
  • But everything should be working, i've ensured the field and their data match from both tables. My only conclusion it that it's because it's not the primary key, so would I have to instead make a compound key out of both Genre_id and Genre itself? – Henry Green Mar 17 '16 at 16:32

2 Answers2

1

If you put:

`Genre` int(11) NOT NULL,

and

KEY `Genre` (`Genre`),

in Albums. Then the following works:

ALTER TABLE Albums
ADD FOREIGN KEY (`Genre`)
REFERENCES Genre (`Genre_id`);

The reasons of not working are:

  1. Type mismatch
  2. Genre is not a key in Albums
  3. You are not referencing the primary key of Genres

Look here for expanded clarifications:

Error Code: 1215. Cannot add foreign key constraint (foreign keys)

Community
  • 1
  • 1
White Feather
  • 2,733
  • 1
  • 15
  • 21
  • I've tried the following to no success. ALTER TABLE tbl_Albums MODIFY COLUMN`Genre` int(11) NOT NULL, ADD KEY `Genre` (`Genre`), ADD FOREIGN KEY (`Genre`), REFERENCES tbl_Genre (`Genre_id`) – Henry Green Mar 17 '16 at 17:12
  • First make ALTER TABLE tbl_Albums MODIFY COLUMN Genre int(11) NOT NULL, ADD KEY Genre (Genre); then run ALTER TABLE Albums ADD FOREIGN KEY (`Genre`) REFERENCES Genre (`Genre_id`); – White Feather Mar 17 '16 at 17:36
0

You can only use the primary key field in Genre (Genre.Genre_id) as the foreign key. You can't use Genre.Genre.

"A FOREIGN KEY in one table points to a PRIMARY KEY in another table."

http://www.w3schools.com/sql/sql_foreignkey.asp

Xavier J
  • 4,326
  • 1
  • 14
  • 25