How to insert data when there are multiple foreign keys in table

Question

How to insert data whenever multiple foreign keys in table means a database which contains patient, Doctor, and staff Patient: an appointment must be with a patient Doctor: every appointment must have a doctor

My question how to insert data within appointment and another related table like patient, doctor at same time?

Give me an example please?

CREATE TABLE IF NOT EXISTS `doctor` (
  `doctor_id` int(11) NOT NULL AUTO_INCREMENT,
  `doc_name` varchar(100) NOT NULL,
  `contactNum` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `qulification` varchar(100) NOT NULL,
  `joiningDate` varchar(50) NOT NULL,
  `u_id` int(11) NOT NULL,
  PRIMARY KEY (`doctor_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Doctor doctor_id: 1 doctor_name : Ali Qulification : mbbs

CREATE TABLE IF NOT EXISTS `patient` (
  `patient_id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `sex` varchar(11) NOT NULL,
  `diagnosis` text NOT NULL,
  `DOB` varchar(100) NOT NULL,
  `address` varchar(200) NOT NULL,
  `Contact_No` varchar(111) NOT NULL,
  PRIMARY KEY (`patient_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

patient

patient_id:1 Name: Ahmed Zubair sex: Male diagnosis : test DOB : 20/6/2000 address : islamabad

CREATE TABLE appointment 
(
  appointment_id int NOT NULL,
  doctor_id int NOT NULL,
  patient_id int NOT NULL,
  Date int NOT NULL,
  time int,

  PRIMARY KEY (appointment_id),
  CONSTRAINT fk_ap_pa_id FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
  CONSTRAINT fk_ap_do_id FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id)
);

Answer 1

As mentioned in the first comment, you need to first insert records into the patient and doctor tables, and then you can insert your appointment record. If these need to happen in a single operation (e.g., all succeed or all fail) then wrap the insert statements in a transaction.

I don't have a MySQL instance available to completely vet the code required, so I can't post it, but follow this process:

1. INSERT your doctor record
2. Save the primary key generated using LAST_INSERT_ID() into a local variable
3. INSERT your patient record
4. Save this primary key into a new variable using the same process as step 2
5. Now you can INSERT your appointment record using these two variables in the statement to ensure you satisfy the foreign keys

Again...if this is an atomic operation, then wrap these statements in a transaction that includes any validation or error checking your situation requires.

More info about LAST_INSERT_ID() here - https://stackoverflow.com/a/17112962/571237