How can insert a value in foreign key column?

Question

I want to insert the symptoms to database which the user selected it's 4 symptoms, pat_id is foreign key which is a primary key in patient table, I need pat_id to know this symptoms for which user, but it show me an error, who can help me and tell me where is the problem?

String Query = "INSERT INTO `patient_symptom`(`symptom1`, `symptom2`, `symptom3`, `symptom4` , `pat_id` ) VALUES ('"+symptom_1.getSelectedItem()+"' , '"+symptom_2.getSelectedItem()+"' ,'"+symptom_3.getSelectedItem()+"' ,'"+symptom_4.getSelectedItem()+"')";
            

Answer 1

The query looks good to me.

EDIT: OOPS! I overlooked the fact that it is missing a value for pat_id.

Answer 2

I think I should clear up a couple concepts, and I hope it helps. As you probably know every insert into the patient_symptoms table should include a value for pat_id that matches a record in the patient table. Without it, the DBMS cannot determine to which patient the new record belongs. Please understand that the DBMS cannot determine pat_id for the new symptom record unless you supply it. Though pat_id might be generated automatically in the patient table, you must copy pat_id into patient_symptoms when you select a patient and add symptoms.

Please note that the INSERT statement lists the field names, and their order, of the values which are about to be inserted. This defines where to place the arguments that follow in VALUES. One for one, the VALUES must match the list of field names in the same order. In this case, the pat_id field name is listed as the last field, but there is no value for it.

Answer 3

There must be a place where the program prompts the user to enter a new patient or else select an existing patient and it performs a DB lookup. If the user enters a new patient then the program must refresh the patient information from the DB afterward in order to retrieve any automatic fields, such as the newly-assigned pat_id. In either case, the patient.pat_id field should be available wherever the patient selection occurs. Find it in the program, append it to the INSERT statement VALUES list, then it should work.

Answer 4

Thanks a lot Brian and David

Answer 5

The record might already exist. If so, then either use UPDATE instead of INSERT, or else check the table definition of patient_symptom to ensure the pat_id field allows duplicate values.

Answer 6

I corrected my previous response after you saw it. The query is missing the pat_id in the values.

Answer 7

👩🏻__🇴🇲 Expanding on what Brian has already explained, consider the following logical steps to accomplish what you are attempting:

========
Scenario A: Insert Patient Symptoms for Existing Patient Record
====
Step 1: Query for the pat_id from the patient table.
Step 2: If pat_id is not 0, then proceed with your Insert into the patient_system table.

========
Scenario B: Insert both Patient and Patient Symptoms records:
====
Step 1: Insert new patient record into the patient table.
Step 2: Load the pat_id if this is a generated key.

------------
-Options to consider for Step 2:
--------

var rs = preparedStmt.getGeneratedKeys();
int pat_id = rs.next() ? rs.getInt(1) : 0;

-- or --

var pat_id = Integer.parseInt(readOneValue("SELECT @@IDENTITY"));

--------

Step 3: If pat_id is not 0, then proceed with your Insert into the patient_system table.

--------

There are a number of other options that can be considered. However, I'm just responding to what I can see of your current approach.

Answer 8

So the query is correct?

Answer 9

Okay I will try 2 use update.. and thank u for your response

Answer 10

But the value will take it from the primary key from other table

Answer 11

I understand what u r mean
But how can insert value for pat_id that matches a record ID in the patient table.