InnoDB foreign key constraint

[Morten]

Hi. Creating the below to tables give errno 150, any idea why?

CREATE TABLE categories (
id int(11) unsigned NOT NULL auto_increment,
name varchar(64) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name)
) TYPE=InnoDB;

CREATE TABLE types (
id int(11) unsigned NOT NULL auto_increment,
category_id int(11) unsigned NOT NULL,
name varchar(64) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (category_id) REFERENCES categories(id)
) TYPE=InnoDB;

Br,

Morten

[Heikki Tuuri]

Morten,

please use

SHOW INNODB STATUS;

to print a detailed error explanation.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

"Morten" <us****@kikobu.com> kirjoitti
viestissä:cq**********@news.cybercity.dk...

Hi. Creating the below to tables give errno 150, any idea why?

CREATE TABLE categories (
id int(11) unsigned NOT NULL auto_increment,
name varchar(64) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name)
) TYPE=InnoDB;

CREATE TABLE types (
id int(11) unsigned NOT NULL auto_increment,
category_id int(11) unsigned NOT NULL,
name varchar(64) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (category_id) REFERENCES categories(id)
) TYPE=InnoDB;

Br,

Morten

[Morten]

Thanks for the pointer. The issue was a lacking index on the
types.category_id column.

Heikki Tuuri wrote:

Morten,

please use

SHOW INNODB STATUS;

to print a detailed error explanation.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

"Morten" <us****@kikobu.com> kirjoitti
viestissä:cq**********@news.cybercity.dk...

Hi. Creating the below to tables give errno 150, any idea why?

CREATE TABLE categories (
id int(11) unsigned NOT NULL auto_increment,
name varchar(64) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name)
) TYPE=InnoDB;

CREATE TABLE types (
id int(11) unsigned NOT NULL auto_increment,
category_id int(11) unsigned NOT NULL,
name varchar(64) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (category_id) REFERENCES categories(id)
) TYPE=InnoDB;

Br,

Morten