I have a table named 'Patient_detail'. There are 4 columns in this table. They are:
patient_id primary key(not null)visit_serial_ID primary key(not null)examinationplanNow, 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)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.
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.
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.
As darasd said you can't. What you're looking for is call normalization.
