Missing Foreign key

[layman]

Hello there,

 

I have created these tables, and because MySQL did not like the foreign key, so I took it out.

It seems that I have made a serious mistake...

 

Could anyone help me with this code, how to fix my tables? And how to modify them or add a foreign key?

 

CREATE TABLE students (
sid int( NOT NULL auto_increment,
sname varchar(50) NOT NULL default '',
address varchar(50) NOT NULL default '',
post_code varchar(10) NOT NULL default'',
photo blob,
PRIMARY KEY (sid)
) ENGINE=INNODB AUTO_INCREMENT=1;

CREATE TABLE courses (
cid int(11) NOT NULL auto_increment,
cname varchar(50) NOT NULL default '',
department varchar(25) NOT NULL default '',
PRIMARY KEY (cid)
) ENGINE=INNODB AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `student_course` (
`SID` varchar( NOT NULL,
`CID` varchar(11) NOT NULL,
`GRADE` varchar(1) NOT NULL,
`COMMENTS` varchar(50) NOT NULL,
PRIMARY KEY (`SID`,`CID`),
KEY `CID` (`CID`),
KEY `SID` (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Thank you in advance for reading this!

[mikosiko]

Just try to follow this:

- In your table student-courses the fields SID and CID must be the same type/length that the corresponding ones in tables students and courses.

 

- In your table student-courses set your Indexes KEY `SID` and KEY `CID` as a BTREE type.

 

- after that try to create your foreign keys.

 

This script work perfectly for me (in case you want to try it to recreate the table.. WARNING: It will delete all your data in that table!!) :

 

DROP TABLE IF `student_course`;
CREATE TABLE  `student_course` (
  `SID` int( NOT NULL,
  `CID` int(11) NOT NULL,
  `GRADE` varchar(1) NOT NULL,
  `COMMENTS` varchar(50) NOT NULL,
  KEY `Index_1` (`SID`),
  KEY `Index_2` (`CID`),
  CONSTRAINT `FK_student_course_2` FOREIGN KEY (`CID`) REFERENCES `courses` (`cid`) ON DELETE RESTRICT,
  CONSTRAINT `FK_student_course_1` FOREIGN KEY (`SID`) REFERENCES `students` (`sid`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

[layman]

Dear Mikosiko,

 

Very well spotted, that I was trying to use different  type/length.

I have made the suggestions and re-created the table, it works perfectly.

So I think this would be the reason, why other stuff did not function orderly...

 

Thank you very much for your reply! I really appreciate it!

Cheers,

layman