Trouble defining foreign keys (MySQL)

Question

Working on an introduction to databases class assignment and running into trouble on a few foreign key. I've done a few foreign keys but every now and then I get stumped and can't figure out why it doesn't work.

This example is a university database and I have section

describe section;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| course_id    | varchar(7)  | NO   | PRI |         |       |
| sec_id       | varchar(10) | NO   | PRI |         |       |
| semester     | varchar(10) | NO   | PRI |         |       |
| year         | int(11)     | NO   | PRI | 0       |       |
| building     | varchar(20) | YES  | MUL | NULL    |       |
| room_no      | varchar(5)  | YES  |     | NULL    |       |
| time_slot_id | varchar(5)  | YES  | MUL | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

I'm trying to make a takes database and am able to make the foreign key for ID and course_id, but any other one I try doesn't work

create table takes(
  ID int, 
  course_id varchar(7), 
  sec_id varchar(10), 
  semester varchar(10), 
  year int, 
  grade int, 
  primary key (course_id, sec_id, semester, year), 
  foreign key (ID) references student(ID)
    on delete cascade on update cascade, 
  foreign key (course_id) references section(course_id) 
    on delete cascade on update cascade
) engine = innodb;

Up until this point it works. If I try and add this, it give me errno: 150

foreign key (sec_id) 
  references section(sec_id) 
  on delete cascade on update cascade

Any help is appreciated. I think I might be missing something fundamental about foreign keys since it worked for course_id but not for sec_id, which seem to be completely the same?

Answer 1

Possibilities:

1. Data types, including unsigned, are not matching.
2. Parent field is not indexed.

If the datatypes are matching, perhaps you have not defined an index on the parent column on which you wanted to define a foreign key relation. Please check if the column sec_id in table section has any indexes defined.

Refer to:

• MySQL: InnoDB Error Codes

Answer 2

Make sure that CHARACTER SET and COLLATION of both the fields is same i.e, takes(sec_id) and section(sec_id)

Please refer to this check list : http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/