mysql innoDB error 1005

I'm trying to find what causes error 1005 on creation of my tables:

CREATE TABLE hospitals(
    hosp_id        INT       NOT NULL AUTO_INCREMENT,
    hosp_name      VARCHAR(100) NOT NULL,
    hosp_address   VARCHAR(100) NOT NULL, 
    hosp_ph_number VARCHAR(8)   NOT NULL,
    PRIMARY KEY(hosp_id)
) TYPE=InnoDB CHARACTER SET=UTF8;

CREATE TABLE transport(
    tr_regnumber   VARCHAR(8)  NOT NULL,
    tr_brand       VARCHAR(15) NOT NULL,
    tr_description VARCHAR(25), 
    hosp_id        INT, 
    PRIMARY KEY (tr_regnumber), 
    FOREIGN KEY (hosp_id) REFERENCES hospitals(hosp_id)
) TYPE=InnoDB CHARACTER SET=UTF8; 

CREATE TABLE buildings(
    build_id          INT NOT NULL AUTO_INCREMENT,
    hosp_id           INT,
    build_address     VARCHAR(100) NOT NULL,
    build_description VARCHAR(25),
    PRIMARY KEY (build_id), 
    FOREIGN KEY (hosp_id) REFERENCES hospitals(hosp_id)
) TYPE=InnoDB CHARACTER SET=UTF8; 

CREATE TABLE patients(
    pat_id         INT NOT NULL AUTO_INCREMENT,
    pat_fullname   VARCHAR(150) NOT NULL,
    diagnosis      VARCHAR(150) NOT NULL,
    emp_id         INT, 
    pat_ph_number  VARCHAR(8),
    pat_address    VARCHAR(100),
    hosp_id        INT,
    pl_num         INT,
    PRIMARY KEY (pat_id),
    FOREIGN KEY (pl_n开发者_JAVA技巧um) REFERENCES places(pl_number),
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
    FOREIGN KEY (hosp_id) REFERENCES hospitals(hosp_id)
) TYPE=InnoDB CHARACTER SET=UTF8;

CREATE TABLE places(
    pl_number    INT NOT NULL AUTO_INCREMENT,
    pat_id       INT NOT NULL,
    hosp_id      INT NOT NULL,
    PRIMARY KEY (pl_number),
    FOREIGN KEY (pat_id) REFERENCES patients(pat_id),
    FOREIGN KEY (hosp_id) REFERENCES hospitals(hosp_id)
) TYPE=InnoDB CHARACTER SET=UTF8;

CREATE TABLE employees(
    emp_id            INT NOT NULL AUTO_INCREMENT,
    emp_fullname      VARCHAR(150) NOT NULL,
    emp_position      VARCHAR(100) NOT NULL,
    emp_ph_number     VARCHAR(8),
    emp_home_address  VARCHAR(100), 
    hosp_id           INT NOT NULL,
    PRIMARY KEY (emp_id),
    FOREIGN KEY (hosp_id) REFERENCES hospitals(hosp_id)
) TYPE=InnoDB CHARACTER SET=UTF8;

Here are errors:

ERROR 1005 (HY000): Can't create table 'hospital_db.patients' (errno: 150)
ERROR 1005 (HY000): Can't create table 'hospital_db.places' (errno: 150)

Here's output of SHOW INNODB STATUS:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
110829 11:52:01 Error in foreign key constraint of table hospital_db/places:
FOREIGN KEY (pat_id) REFERENCES patients(pat_id),
    FOREIGN KEY (hosp_id) REFERENCES hospitals(hosp_id)
) TYPE=InnoDB CHARACTER SET=UTF8:
Cannot resolve table name close to:
(pat_id),
    FOREIGN KEY (hosp_id) REFERENCES hospitals(hosp_id)
) TYPE=InnoDB CHARACTER SET=UTF8

I use MySQL v5.1.49.

---

This seems to be to do with the order you're creating the tables and the foreign key dependencies you have.

Try disabling foreign key checks before creating the tables and enabling them after like so:

SET foreign_key_checks = 0;
-- Your create queries here
SET foreign_key_checks = 1;

Cheers

---

I know this one is solved, but for the Googlers out here, I did this in mysql workbench:

Primary keys have 'not null' checked by default. Creating a foreign key in a child table and also checking it as 'not null' will cause this error. It took me ages to find this myself because its an exception of what people suggest (making sure everything in the column is of the same type as the parent key). So just leave 'not null' unchecked on the foreign key

thought this might help saving people some time :)

---

You create FK on table 'places' before creating this table. Table places is created after table patients which try to use table which doesn't exist yet. It seems that you have crossing foreign keys. In this case it's better to create tables without FKs and than use ALter TABLE for adding FKs.