Foreign Key Issues!

[mattclements]

Hello, I use the following code to create relational tables:

/* Remove all old Tables that may cause an error */ 
DROP TABLE behaviour_code;
DROP TABLE behaviour_log;
DROP TABLE teacher;
DROP TABLE student;

/* Create All Tables */
/* Create Behaviour Code Table */ 
CREATE TABLE behaviour_code (
  `code` VARCHAR(6) NOT NULL,
  `name` TEXT(50) NOT NULL,
  `description` LONGTEXT,
  PRIMARY KEY (`code`)
)
ENGINE = InnoDB;

/* Create Student Table */
CREATE TABLE student (
  `id` INTEGER(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` TEXT(75) NOT NULL,
  `last_name` TEXT(75) NOT NULL,
  `form` VARCHAR(5) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

/* Create Teacher Table */
CREATE TABLE teacher (
  `id` INTEGER(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` TEXT(150) NOT NULL,
  `form` VARCHAR(5),
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

/* Create Behaviour Log Table */
CREATE TABLE behaviour_log (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` DATETIME NOT NULL,
  `code` VARCHAR(45) NOT NULL,
  `student` VARCHAR(45) NOT NULL,
  `teacher` VARCHAR(45) NOT NULL,
  `description` LONGTEXT,
  `action_taken` LONGTEXT,
  PRIMARY KEY (`id`),
)
ENGINE = InnoDB;

/* Create Behaviour Log Relationships */
ALTER TABLE behaviour_log 
  ADD Foreign Key (student) references teacher(id),
  ADD Foreign Key (teacher) references student(id),
  ADD Foreign Key (code) references behaviour_code(code);

 

However when I run this in SQL I get the following error:

/* Create Behaviour Log Relationships */ ALTER TABLE behaviour_log ADD FOREIGN KEY ( student ) REFERENCES teacher( id ) ,

ADD FOREIGN KEY ( teacher ) REFERENCES student( id ) ,

ADD FOREIGN KEY ( code ) REFERENCES behaviour_code( code ) ;

 

 

 

MySQL said: 

 

#1005 - Can't create table './unit13/#sql-656_29ed.frm' (errno: 150)

 

Please help!

 

P.S. Im using: phpMyAdmin 2.10.1, SQL: 5.0.32-Debian_7etch3-log, PHP 5 with Apache Latest