MySQL Error 150 Can't assign Genre as a foreign key from the Genre table into Album table

Question

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?

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

Answer 2

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