MySQL Error:1215 Foreign Key

Question

After hours of googling i cant find what's the problem is When it reaches the table 'ward' it gives me the 1215 error... Can you help me please?

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

http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150 — Nagaraj S, May 05 '14 at 11:48
`FOREIGN KEY (ward_code) REFERENCES ward (ward_code)` what does this mean a key is referencing the same table ? — Abhik Chakraborty, May 05 '14 at 11:48
are you refering to the same table FOREIGN KEY (ward_code) REFERENCES ward (ward_code) also FOREIGN KEY (test_code) REFERENCES test (test_code) — user3470953, May 05 '14 at 11:48
Its for a school homework and professor gave some notes saying that ward_code will be foreing key in table ward Same on the other tables that i have done the same even after removing this to FK that referecning on the same table still get 1215 on emp table — user3604116, May 05 '14 at 11:52
You need a separate table `hospital_ward`. It should have foreign keys pointing to `hospital` and `ward`. It makes no sense for a foreign key to point to itself -- that means the value has to exist before you insert the value, which is logically impossible. — Barmar, May 05 '14 at 11:57
Going to check the hospital_ward solution! Thanks for pointing that.. Still learning! Be right back! — user3604116, May 05 '14 at 11:59

score 0 · Accepted Answer · answered May 05 '14 at 11:56

0

please check the structure of your all table.

you are trying to apply FOREIGN KEY on the primary key of two different tables...

see here in hospital table host_code is PRIMARY KEY and in hospital_lab table hosp_code is also PRIMARY KEY it will not allowed to do like this.

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

Thanks...

answered May 05 '14 at 11:56

Tirthrajsinh Parmar

158
1
7

first if you want to check your structure then create all tables without FOREIGN KEY then go to DESIGNER view and try to apply REFERENCES from there... – Tirthrajsinh Parmar May 05 '14 at 12:03
i didn't use it but i think in it "Visual Database Design" is there so you can visually check both table have primary key so it will not allow to refer to each other... – Tirthrajsinh Parmar May 05 '14 at 12:13
you welcome... :) and i use XAMPP (https://www.apachefriends.org/index.html) so no idea about "workbench" – Tirthrajsinh Parmar May 05 '14 at 12:24
Thanks guys Solved :) just had to reference to the right place – user3604116 May 05 '14 at 13:17

MySQL Error:1215 Foreign Key

1 Answers1