How to insert multiple rows for a single primary key?

Question

I have a table named 'Patient_detail'. There are 4 columns in this table. They are:

1. patient_id primary key(not null)
2. visit_serial_ID primary key(not null)
3. examination
4. plan

Now, how can I insert multiple records in 'examination' and 'plan' column against the single primary key 'patient_id' and 'visit_serial_ID' of the table 'Patient_detail'?

The datatypes of the fields are as follows:

• patient_id: number(11)
• visit_serial_ID: number(5)
• examination: varchar2(50)
• plan: varchar2(50)

Answer 1

You can't (that's the whole point of a primary key - you have one row per value). You need to split this into a couple of tables, one for visits and one for patients. The visit table would have a foreign key relationship to the patient table's primary key column.

EDIT

You need to think about what actual real-life things you are representing, and how they interrelate. For each separate real-life thing, you would usually have a separate table. This allows the one-to-one, many-to-one, many-to-many relationships that you are after. For instance, you are representing a patient who has a visit, during which there are a number of examinations, each of which has a corresponding plan (is this correct?). You should therefore have a patient table containing patient data, a visit table, containing visit data, and an examination table containing examination data, and maybe even a plan table containing plan data.

The visit table has a many-to-one relationship with the patient table (a patient can visit a number of time). To achieve this, it should have a foreign key to the patient_id column. Similarly, the examination table has a many-to-one relationship with the visit table, so, again, it should have a foreign key to the visit table's primary key.

There are further ways that the can be separated. As Sascha said, look up normalisation.