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

you can't. you have insert data into the patient AND doctor tables first, THEN you can create an appointment. — Marc B, May 22 '15 at 18:19

score 0 · Accepted Answer · edited May 23 '17 at 11:58

0

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:

INSERT your doctor record
Save the primary key generated using LAST_INSERT_ID() into a local variable
INSERT your patient record
Save this primary key into a new variable using the same process as step 2
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

edited May 23 '17 at 11:58

Community

1
1

answered May 22 '15 at 18:34

Sam Storie

4,444
4
48
74

Local variable? How explain me please – Asad May 22 '15 at 19:21
Take a look at the documentation - https://dev.mysql.com/doc/refman/5.7/en/user-variables.html. I think you should be able to write something like `SET @doctorPK = LAST_INSERT_ID()` and then use that variable in your final insert statement. – Sam Storie May 22 '15 at 19:26
Tell me easiest way please.. I'm very new in mysql – Asad May 22 '15 at 19:33
I've tried to do that with my answer and follow up comments. – Sam Storie May 22 '15 at 19:34
Trying...if give one example it's much better to understand things – Asad May 23 '15 at 03:53

score 0 · Answer 2 · answered May 25 '15 at 12:13

0

In order to achive this you should use transaction, more detail about this you can read

Thanks

answered May 25 '15 at 12:13

The Reason

7,705
4
24
42

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

2 Answers2