MySQL错误:1215外键

Question

经过几个小时的谷歌，我找不到问题是什么，当它到达表‘病房’它给我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)
  );  

Answer 1

请检查一下您的全桌的结构。

您正试图在两个不同表的主键上应用外键.

参见这里的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)
  );

谢谢..。