[SOLVED] How to create a foreign key in phpmyadmin

Issue

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

Solution

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

Answered By – Jon Story

Answer Checked By – Robin (BugsFixing Admin)