How to create a foreign key in phpmyadmin

Question

I want to make doctorid a foreign key in my patient table.

So I have all of my tables created - the main problem is that when I go to the table > structure > relation view only the primary key comes up that I can create a foreign key (and it is already the primary key of the 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 (medicineid and patientid) in relation view it enables me to change both

Do I have to chance the index of doctor ID in patient table to something else? both cannot be primary keys as patient ID is the primary for the patient table - doctor is the foreign.

I hope anyone can help

Kind regards

Answer 1

You can do it the old fashioned way... with an SQL statement that looks something 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)     ON DELETE action_name     ON UPDATE action_name; 

For example: If you have books table with column created_by which refere to column id in users table:

ALTER TABLE books ADD CONSTRAINT books_FK_1 FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE; 

Note: CASCADE mean if you delete from users table, related rows from books table will be deleted

This assumes the keys already exist in the relevant table