Re: ERROR 1005: Can't create table (errno: 150)

[Paul Carey]

i was having big headache trying to sort this, dont know if this will work for every one but the following worked for me, just adding CONSTRAINT into my statement seamed to sort it out amongst other things such as ensuring that all keys had same types and where all unsigned also.

Problem occured when creating second table (quote_item)

Before with problems:

CREATE TABLE quote (
quote_id INT NOT NULL AUTO_INCREMENT,
customer_id INT NULL,
///Fields
PRIMARY KEY(quote_id),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON DELETE CASCADE ON UPDATE CASCADE
)
TYPE=InnoDB;

CREATE TABLE quote_item (
quote_item_id INT NOT NULL AUTO_INCREMENT,
quote_id INT NULL,
//Fields
PRIMARY KEY(quote_item_id),
FOREIGN KEY (quote_id) REFERENCES quote(quote_id) ON DELETE CASCADE ON UPDATE CASCADE
)
TYPE=InnoDB;

After WORKED:

CREATE TABLE quote (
quote_id INT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
//Fields
PRIMARY KEY(quote_id),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON DELETE CASCADE ON UPDATE CASCADE
)
TYPE=InnoDB;

CREATE TABLE quote_item (
quote_item_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
quote_id INTEGER UNSIGNED NOT NULL,
//Fields
PRIMARY KEY (quote_item_id),
CONSTRAINT quote_id FOREIGN KEY quote_id (quote_id)
REFERENCES quote (quote_id)
ON DELETE CASCADE
ON UPDATE CASCADE
)
TYPE=InnoDB;

Hope this helps someone