mysql - MySQL错误:1215外键

经过数小时的谷歌搜索，我找不到问题所在
当它到达表“病房”时，给我1215错误...
你能帮我吗？

 CREATE TABLE IF NOT EXISTS patient
  (
     pat_registration INTEGER(5) NOT NULL,
     pat_name         VARCHAR(20),
     pat_address      VARCHAR(15),
     pat_birthdate    DATE,
     pat_sex          VARCHAR(1),
     pat_ssn          INTEGER(9),
     CHECK (pat_sex = 'M' OR 'F'),
     PRIMARY KEY (pat_registration)
  );

CREATE TABLE IF NOT EXISTS hospital
  (
     hosp_code    INTEGER(3) NOT NULL,
     hosp_name    VARCHAR(15),
     hosp_address VARCHAR(15),
     hosp_phone   VARCHAR(8),
     hosp_sumbed  INTEGER(5),
     PRIMARY KEY (hosp_code)
  );

CREATE TABLE IF NOT EXISTS lab
  (
     lab_code    INTEGER(2) NOT NULL,
     lab_name    VARCHAR(15),
     lab_address VARCHAR(15),
     lab_phone   VARCHAR(8),
     PRIMARY KEY (lab_code)
  );

CREATE TABLE IF NOT EXISTS hospital_lab
  (
     hosp_code INTEGER(3) NOT NULL,
     lab_code  INTEGER(2) NOT NULL,
     PRIMARY KEY (hosp_code, lab_code),
     FOREIGN KEY (hosp_code) REFERENCES hospital (hosp_code),
     FOREIGN KEY (lab_code) REFERENCES lab (lab_code)
  );

CREATE TABLE IF NOT EXISTS ward
  (
     hosp_code   INTEGER(3) NOT NULL,
     ward_code   INTEGER(3) NOT NULL,
     ward_name   VARCHAR(20),
     ward_sumbed INTEGER(3),
     PRIMARY KEY (hosp_code, ward_code),
     FOREIGN KEY (hosp_code) REFERENCES hospital (hosp_code),
     FOREIGN KEY (ward_code) REFERENCES ward (ward_code)
  );

CREATE TABLE IF NOT EXISTS emp
  (
     hosp_code  INTEGER(3) NOT NULL,
     ward_code  INTEGER(3) NOT NULL,
     emp_code   INTEGER(5) NOT NULL,
     emp_name   VARCHAR(20),
     emp_shift  VARCHAR(1),
     emp_duty   VARCHAR(10),
     emp_salary INTEGER(6),
     CHECK (emp_shift = 'M' OR 'A' OR 'E'),
     PRIMARY KEY (emp_code),
     FOREIGN KEY (hosp_code) REFERENCES hospital (hosp_code),
     FOREIGN KEY (ward_code) REFERENCES ward (ward_code)
  );

CREATE TABLE IF NOT EXISTS test
  (
     lab_code  INTEGER(2) NOT NULL,
     test_code VARCHAR(3) NOT NULL,
     test_name VARCHAR(15),
     PRIMARY KEY (lab_code, test_code),
     FOREIGN KEY (lab_code) REFERENCES lab (lab_code),
     FOREIGN KEY (test_code) REFERENCES test (test_code)
  );

CREATE TABLE IF NOT EXISTS examination
  (
     pat_registration INTEGER(5) NOT NULL,
     lab_code         INTEGER(2) NOT NULL,
     test_code        VARCHAR(3) NOT NULL,
     result           VARCHAR(4),
     test_date        DATE NOT NULL,
     PRIMARY KEY (pat_registration, test_code, lab_code, test_date),
     FOREIGN KEY (pat_registration) REFERENCES patient (pat_registration),
     FOREIGN KEY (test_code) REFERENCES test (test_code),
     FOREIGN KEY (lab_code) REFERENCES lab (lab_code)
  );

CREATE TABLE IF NOT EXISTS doctor
  (
     hosp_code   INTEGER(3) NOT NULL,
     doctor_code INTEGER(3) NOT NULL,
     doctor_name VARCHAR(15),
     speciality  VARCHAR(15),
     PRIMARY KEY (doctor_code),
     FOREIGN KEY (hosp_code) REFERENCES hospital (hosp_code)
  );

CREATE TABLE IF NOT EXISTS att_doctor
  (
     doctor_code      INTEGER(3) NOT NULL,
     pat_registration INTEGER(5) NOT NULL,
     PRIMARY KEY (doctor_code, pat_registration),
     FOREIGN KEY (doctor_code) REFERENCES doctor (doctor_code),
     FOREIGN KEY (pat_registration) REFERENCES patient (pat_registration)
  );

CREATE TABLE IF NOT EXISTS occupancy
  (
     hosp_code        INTEGER(3) NOT NULL,
     ward_code        INTEGER(3) NOT NULL,
     pat_registration INTEGER(5) NOT NULL,
     bed_no           INTEGER(4),
     entry_date       DATE NOT NULL,
     PRIMARY KEY (hosp_code, ward_code, pat_registration, entry_date),
     FOREIGN KEY (hosp_code) REFERENCES hospital (hosp_code),
     FOREIGN KEY (ward_code) REFERENCES ward (ward_code),
     FOREIGN KEY (pat_registration) REFERENCES patient (pat_registration)
  );

最佳答案

请检查所有表格的结构。

您正在尝试将FOREIGN KEY应用于两个不同表的主键...

请参见hospital表中的host_code是PRIMARY KEY，在hospital_lab表中的hosp_code也是PRIMARY KEY，将不允许这样做。

    CREATE TABLE IF NOT EXISTS hospital
  (
     hosp_code    INTEGER(3) NOT NULL,
     hosp_name    VARCHAR(15),
     hosp_address VARCHAR(15),
     hosp_phone   VARCHAR(8),
     hosp_sumbed  INTEGER(5),
     PRIMARY KEY (hosp_code)
  );
  CREATE TABLE IF NOT EXISTS hospital_lab
  (
     hosp_code INTEGER(3) NOT NULL,
     lab_code  INTEGER(2) NOT NULL,
     PRIMARY KEY (hosp_code, lab_code),
     FOREIGN KEY (hosp_code) REFERENCES hospital (hosp_code),
     FOREIGN KEY (lab_code) REFERENCES lab (lab_code)
  );

谢谢...

关于mysql - MySQL错误:1215外键，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23471644/