table with no primary key

Posted on

Question :

I have two tables

  • doctor (doc_id, Doc_name, Doc_phone)
  • Specialization (doc_id, specialization)

a doctor can have zero or many specializations, but I’m not sure what the primary key for Specialization would be.

Answer :

Can a Specialization exist without a Doctor? I suspect it can, in which case you need a third table, to “master” these Specializations and your Doctor/Specialization becomes a Weak Entity joining those two:

select * from doctor ; 

+----+-----------------+ 
| id | name            | 
+----+-----------------+ 
|  1 | Fred Flintstone |  They retrained :-) 
|  2 | Barney Rubble   | 
+----+-----------------+ 

select * from specialization ; 

+----+------------+
| id | name       | 
+----+------------+
| 33 | Neurology  | 
| 44 | Psychiatry |
| 55 | Radiology  |
+----+------------+

select * from doctor_specialization ; 

+--------+---------+
| doc_id | spec_id | 
+--------+---------+
|      1 |      33 |
|      2 |      55 | 
+--------+---------+

Your Primary Key for doctor_specialization is the entire record – you can’t have a record that doesn’t have both values present, nor can you have duplicates of their combination.
Further, each field has a Foreign Key back to its [own] parent table.

primary key ( doc_id, spec_id )
foreign key ( doc_id ) references doctor ( id ) 
foreign key ( spec_id ) references specialization (id ) 

If you needed to find a doctor with a given Specialization, you might consider a Covering Index going back the other way:

create index i_ds_spec 
on doctor_specialization ( spec_id, doc_id ) ; 

A primary key can be compound (consist of multiple columns), and even cover the entire table (be all of the table’s columns).

In your case, (doc_id, specialization) is the primary key.

The key for this should be DOC_ID & SPECIALIZATION (ID).

This should be able to identify records for this table uniquely. Read this as “For every doctor there is a specialization and for every specialization there is a doctor in this table

Leave a Reply

Your email address will not be published. Required fields are marked *