INNODB Cascade Syntax / Logic Problem

Question

Hi,

I have a problem with an SQL database I'm creating. I'm trying to switch it over to use INNODB but I can't seem to get the syntax (or possibly logic) correct for cascading.

Here's a part of the code erroring. It does not like line 40.

Error output in the usual cryptic (to me at least) form:

ERROR 1005 (HY000) at line 36: Can't create table './school/staff.frm' (errno: 150)

Edit:

Here's the whole SQL file I'm trying to pipe in if that helps.

Answer 1

Foreign keys have to be indexes. (role.name)

Try this:

CREATE TABLE IF NOT EXISTS `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(30) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB;

Answer 2

Did you mean for the "role" column's foreign key constraint to be using the name instead of the ID? It looks like the following line (line 43):

FOREIGN KEY (role) REFERENCES role(name)

should be:

FOREIGN KEY (role) REFERENCES role(id)

This executes without errors.

Alternatively, the "staff.role" column's data type (line 40, role INT NOT NULL) could be changed to CHAR(30) NOT NULL.