Re: ERROR 1005: Can't create table (errno: 150). [SOLVED] An other solution

[Adrian Meyer]

I got the same error when adding a foreign constraint. The issues was that one table was missing the ENGINE=InnoDB statement:

CREATE TABLE lkupspecialty (
specialtyid INTEGER(11) NOT NULL,
label VARCHAR(255) NOT NULL,
CONSTRAINT PK_lkupspecialty PRIMARY KEY (specialtyid)
);

CREATE TABLE lkupsurgery (
surgeryid INTEGER(11) AUTO_INCREMENT NOT NULL,
specialtyid INTEGER(11) NOT NULL,
label VARCHAR(255) NOT NULL,
CONSTRAINT PK_lkupsurgery PRIMARY KEY (surgeryid)
)
ENGINE=InnoDB;

ALTER TABLE lkupsurgery
ADD CONSTRAINT FK_lkupsurgery_lkupspecialty
FOREIGN KEY (specialtyid) REFERENCES lkupspecialty (specialtyid);