#1215 - Cannot add foreign key constraint :/

Question

CREATE TABLE bill (
  `number` int(255) NOT NULL,
  `patient_id` int(255) DEFAULT NULL,
  `doctor_charge` varchar(100) NOT NULL,
  `medicine_charge` varchar(100) NOT NULL,
  `room_charge` varchar(100) NOT NULL,
  `nursing_charge` varchar(100) NOT NULL,
  `total_amount` varchar(255) NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (number)
);

CREATE TABLE daily_status (
  `ID` int(255) NOT NULL,
  `patient_id` int(255) DEFAULT NULL,
  `date` date NOT NULL,
  `medication` varchar(100) CHARACTER SET utf8 NOT NULL,
  `body_temp` varchar(100) CHARACTER SET utf8 NOT NULL,
  `blood_pressure` varchar(100) CHARACTER SET utf8 NOT NULL,
  `heart_beat` varchar(100) CHARACTER SET utf8 NOT NULL,
  `diabetes_level` varchar(100) CHARACTER SET utf8 NOT NULL,
  `oxygen_level` varchar(100) CHARACTER SET utf8 NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (ID)
);

CREATE TABLE `doctors` (
  `id` int(255) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 NOT NULL,
  `position` varchar(100) CHARACTER SET utf8 NOT NULL,
  `username` varchar(50) CHARACTER SET utf8 NOT NULL,
  `email` varchar(100) CHARACTER SET utf8 NOT NULL,
  `mob` varchar(50) CHARACTER SET utf8 NOT NULL,
  `password` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `guardian` (
  `id` int(255) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 NOT NULL,
  `position` varchar(100) CHARACTER SET utf8 NOT NULL,
  `username` varchar(50) CHARACTER SET utf8 NOT NULL,
  `email` varchar(100) CHARACTER SET utf8 NOT NULL,
  `mob` varchar(50) CHARACTER SET utf8 NOT NULL,
  `password` varchar(50) CHARACTER SET utf8 NOT NULL,
  `request` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `nurses` (
  `id` int(255) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 NOT NULL,
  `position` varchar(100) CHARACTER SET utf8 NOT NULL,
  `username` varchar(50) CHARACTER SET utf8 NOT NULL,
  `email` varchar(100) CHARACTER SET utf8 NOT NULL,
  `mob` varchar(50) CHARACTER SET utf8 NOT NULL,
  `password` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE patient (
  `ID` int(255) NOT NULL,
  `guard_id` int(255) DEFAULT NULL,
  `FullName` varchar(50) CHARACTER SET utf8 NOT NULL,
  `DOB` date NOT NULL,
  `sex` varchar(50) CHARACTER SET utf8 NOT NULL,
  `bloodType` varchar(50) CHARACTER SET utf8 NOT NULL,
  `phoneNO` varchar(100) CHARACTER SET utf8 NOT NULL,
  `dateIN` date NOT NULL,
  `dateOut` date NOT NULL,
  `viewstatus` varchar(50) NOT NULL,
  `requeststatus` varchar(50) CHARACTER SET utf8 NOT NULL,
    FOREIGN KEY (guard_id) REFERENCES guardian (id) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (ID)
);

CREATE TABLE report (
  `ID` int(255) NOT NULL,
  `patient_id` int(255) DEFAULT NULL,
  `doctors_id` int(255) DEFAULT NULL,
  `upload_id` int(255) DEFAULT NULL,
  `number` int(255) NOT NULL,
  `description` varchar(300) CHARACTER SET utf8 NOT NULL,
  `source` varchar(100) CHARACTER SET utf8 NOT NULL,
  `date` date NOT NULL,
  `type` varchar(100) CHARACTER SET utf8 NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (doctors_id) REFERENCES doctors (id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (upload_id) REFERENCES upload (id) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (ID)
);

  CREATE TABLE `usersidaccess` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
   `name` varchar(150) NOT NULL,
  `position` varchar(15) NOT NULL,
  `username` varchar(100) NOT NULL,
  `email` varchar(50) NOT NULL,
  `mob` bigint(20) NOT NULL,
  `password` varchar(50) NOT NULL,
  `access` varchar(50) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `upload` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `date` datetime  NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


ALTER TABLE `doctors`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `guardian`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `nurses`
  ADD PRIMARY KEY (`id`);



ALTER TABLE `bill`
  MODIFY `number` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `daily_status`
  MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;


ALTER TABLE `doctors`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;


ALTER TABLE `guardian`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;


ALTER TABLE `nurses`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;


ALTER TABLE `patient`
  MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;


ALTER TABLE `report`
  MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;


CREATE TABLE doctor_patient (
    patient_id INTEGER NOT NULL,
    doctors_id INTEGER NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (doctors_id) REFERENCES doctors (id) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (patient_id, doctors_id)
);


CREATE TABLE nurses_status (
    nurses_id INTEGER NOT NULL,
    status_id INTEGER NOT NULL,
    FOREIGN KEY (nurses_id) REFERENCES nurses (id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (status_id) REFERENCES daily_status (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY ( nurses_id, status_id)
);

Read https://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 for a checklist to follow for troubleshooting foreign key errors — Bill Karwin, Aug 08 '18 at 17:52

Michał Drabik · Answer 1 · 2018-08-08T18:08:36.537

This might happen because you're trying to reference a table that is not yet created. Please rearrange the order of CREATE statements so that those tables that do not have any foreign keys come first, then the other tables in order which will not interfere with creating foreign keys.

Below's the solution. Your code was improperly ordered and it had different data types in some of the foreign key fields.

CREATE TABLE `doctors` (
  `id` int(255) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 NOT NULL,
  `position` varchar(100) CHARACTER SET utf8 NOT NULL,
  `username` varchar(50) CHARACTER SET utf8 NOT NULL,
  `email` varchar(100) CHARACTER SET utf8 NOT NULL,
  `mob` varchar(50) CHARACTER SET utf8 NOT NULL,
  `password` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `doctors`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `doctors`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

CREATE TABLE `guardian` (
  `id` int(255) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 NOT NULL,
  `position` varchar(100) CHARACTER SET utf8 NOT NULL,
  `username` varchar(50) CHARACTER SET utf8 NOT NULL,
  `email` varchar(100) CHARACTER SET utf8 NOT NULL,
  `mob` varchar(50) CHARACTER SET utf8 NOT NULL,
  `password` varchar(50) CHARACTER SET utf8 NOT NULL,
  `request` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `guardian`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `guardian`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

CREATE TABLE `nurses` (
  `id` int(255) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 NOT NULL,
  `position` varchar(100) CHARACTER SET utf8 NOT NULL,
  `username` varchar(50) CHARACTER SET utf8 NOT NULL,
  `email` varchar(100) CHARACTER SET utf8 NOT NULL,
  `mob` varchar(50) CHARACTER SET utf8 NOT NULL,
  `password` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `nurses`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `nurses`
  MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

  CREATE TABLE `usersidaccess` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
   `name` varchar(150) NOT NULL,
  `position` varchar(15) NOT NULL,
  `username` varchar(100) NOT NULL,
  `email` varchar(50) NOT NULL,
  `mob` bigint(20) NOT NULL,
  `password` varchar(50) NOT NULL,
  `access` varchar(50) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `upload` (
  `id` int(255) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `date` datetime  NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

CREATE TABLE patient (
  `ID` int(255) NOT NULL,
  `guard_id` int(255) DEFAULT NULL,
  `FullName` varchar(50) CHARACTER SET utf8 NOT NULL,
  `DOB` date NOT NULL,
  `sex` varchar(50) CHARACTER SET utf8 NOT NULL,
  `bloodType` varchar(50) CHARACTER SET utf8 NOT NULL,
  `phoneNO` varchar(100) CHARACTER SET utf8 NOT NULL,
  `dateIN` date NOT NULL,
  `dateOut` date NOT NULL,
  `viewstatus` varchar(50) NOT NULL,
  `requeststatus` varchar(50) CHARACTER SET utf8 NOT NULL,
    FOREIGN KEY (guard_id) REFERENCES guardian (id) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (ID)
);

ALTER TABLE `patient`
  MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

CREATE TABLE bill (
  `number` int(255) NOT NULL,
  `patient_id` int(255) DEFAULT NULL,
  `doctor_charge` varchar(100) NOT NULL,
  `medicine_charge` varchar(100) NOT NULL,
  `room_charge` varchar(100) NOT NULL,
  `nursing_charge` varchar(100) NOT NULL,
  `total_amount` varchar(255) NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (number)
);

ALTER TABLE `bill`
  MODIFY `number` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

CREATE TABLE daily_status (
  `ID` int(255) NOT NULL,
  `patient_id` int(255) DEFAULT NULL,
  `date` date NOT NULL,
  `medication` varchar(100) CHARACTER SET utf8 NOT NULL,
  `body_temp` varchar(100) CHARACTER SET utf8 NOT NULL,
  `blood_pressure` varchar(100) CHARACTER SET utf8 NOT NULL,
  `heart_beat` varchar(100) CHARACTER SET utf8 NOT NULL,
  `diabetes_level` varchar(100) CHARACTER SET utf8 NOT NULL,
  `oxygen_level` varchar(100) CHARACTER SET utf8 NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (ID)
);

ALTER TABLE `daily_status`
  MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

CREATE TABLE report (
  `ID` int(255) NOT NULL,
  `patient_id` int(255) DEFAULT NULL,
  `doctors_id` int(255) DEFAULT NULL,
  `upload_id` int(255) DEFAULT NULL,
  `number` int(255) NOT NULL,
  `description` varchar(300) CHARACTER SET utf8 NOT NULL,
  `source` varchar(100) CHARACTER SET utf8 NOT NULL,
  `date` date NOT NULL,
  `type` varchar(100) CHARACTER SET utf8 NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (doctors_id) REFERENCES doctors (id) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (ID)
);

ALTER TABLE `report`
  MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

  ALTER TABLE `report`
    ADD CONSTRAINT upload_fk FOREIGN KEY (upload_id) REFERENCES upload (id) ON DELETE RESTRICT ON UPDATE CASCADE;

CREATE TABLE doctor_patient (
    patient_id INTEGER NOT NULL,
    doctors_id INTEGER NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (doctors_id) REFERENCES doctors (id) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (patient_id, doctors_id)
);


CREATE TABLE nurses_status (
    nurses_id INTEGER NOT NULL,
    status_id INTEGER NOT NULL,
    FOREIGN KEY (nurses_id) REFERENCES nurses (id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (status_id) REFERENCES daily_status (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY ( nurses_id, status_id)
);

score 0 · Answer 2 · answered Aug 08 '18 at 18:15

0

.In table bill your reference table is patient which is not created when you execute crete table bill as a result it returns error.
same things happen for table daily_status creation
In table reports you used reference upload table, which you created later so it also thrown error

So order table creation statement that already suggested @brick

answered Aug 08 '18 at 18:15

Zaynul Abadin Tuhin

31,407
5
33
63

Johnny Farah if it helps accept answer please, as a SO appriciation – Zaynul Abadin Tuhin Aug 17 '18 at 16:03

#1215 - Cannot add foreign key constraint :/

2 Answers2