469,923 Members | 1,688 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,923 developers. It's quick & easy.

InnoDB foreign key constraint


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

Jul 20 '05 #1
2 2273
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

Jul 20 '05 #2

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


Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.