mysql innoDB错误1005

Question

我正在尝试找出导致创建表时出现错误 1005 的原因:

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_num) 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;

这里是错误:

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

这是 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

我使用的是 MySQL v5.1.49。

Answer 1

这似乎与您创建表的顺序和您拥有的外键依赖关系有关。

尝试在创建表之前禁用外键检查，然后像这样启用它们:

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

干杯