How to create a foreign key in phpmyadmin

Question

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?

Answer 1

You can execute it using a traditional SQL statement that appears somewhat like this.

ALTER TABLE table_1_name
    ADD CONSTRAINT fk_foreign_key_name
    FOREIGN KEY (table_1_column_name)
    REFERENCES target_table(target_table_column_name);

For instance, if the field created_by in your books database refers to the column id in your user table:

ALTER TABLE books
    ADD CONSTRAINT books_FK_1
    FOREIGN KEY (created_by)
    REFERENCES users(id);

I hope this helps you.