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.

Answer 2

You can't. That's what a primary key is - something that is unique to every row. You would need a separate table with its own primary key, and with a foreign key relationship to this table, in order to store multiple entries. This would then be a straightforward one-to-many relationship.

Answer 3

You can do this but your primary key would cease to be a primary key and you data would be denormalized. The best way to do this would be to split this table into two separate tables like this:

Patients
----------
PatientId

Visits
----------
VisitSerialId
Examination
Pland
PatientId

Notice that the Visits table has a foreign key realtionship to the Patients table. This will allow you to have the 1:M relationship you are looking for.

Answer 4

As darasd said you can't. What you're looking for is call normalization.