I would appreciate some help with a problem. I'm trying to create two
tables that have referential integrity.

If I try and create the following table it works fine:

CREATE TABLE book
(
sku INT
) TYPE=INNODB;

However, if I creating this table, I get an error:

CREATE TABLE book
(
sku INT,
FOREIGN KEY (sku) REFERENCES stock_item (sku) ON DELETE CASCADE
) TYPE=INNODB;

The error is: ERROR 1005: Can't create table './shop/book.frm' (errno:
150)


The parent table is as follows:

CREATE TABLE stock_item
(
sku CHAR(14) NOT NULL,
PRIMARY KEY (sku),
description TEXT,
publisher_id INT DEFAULT NULL,
pub_date DATE DEFAULT NULL,
type ENUM('OT','BK','CD') NOT NULL,
availability_id TINYINT DEFAULT NULL,
image_id INT DEFAULT NULL,
buy_price FLOAT UNSIGNED,
list_price FLOAT UNSIGNED,
sell_price FLOAT UNSIGNED,
discount TINYINT UNSIGNED,
stock_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
display ENUM('no','yes') DEFAULT 'no'
) TYPE=INNODB;

Any suggestions would be welcome.

Thanks

Tom



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

Please ignore my last email. I made a nauseatingly dumb error in the
book table (sku should be CHAR(14), not INT).

The only help I need is to get some sleep.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw