In my patient table, I want to add a foreign key for doctorid.
I have all of my tables established, but the main issue is that only the primary key appears when I go to the table > structure > relation view so that I may build a foreign key (and it is already the primary key of certain table that I want to keep - i.e Patient table patient is enabled to be changed but the doctor Id -I have a doctor table also- is not enabled).
I have another table with two composite keys (patientid and medicineid) that allow me to alter both in relation view. Should I change the doctor ID index in the patient table to anything else? Given that patient ID is the primary key for the patient table and doctor is the foreign key, neither can be the primary key.
Can someone please help me with this?