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

Solution 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);

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

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

This assumes the keys already exist in the relevant table

Solution 2

The key must be indexed to apply foreign key constraint. To do that follow the steps.

1. Open table structure. (2nd tab)
2. See the last column action where multiples action options are there. Click on Index, this will make the column indexed.
3. Open relation view and add foreign key constraint.

You will be able to assign DOCTOR_ID as foreign now.

Solution 3

To be able to create a relation, the table Storage Engine must be InnoDB. You can edit in Operations tab.

Then, you need to be sure that the id column in your main table has been indexed. It should appear at Index section in Structure tab.

Finally, you could see the option Relations View in Structure tab. When edditing, you will be able to select the parent column in foreign table to create the relation.

See attachments. I hope this could be useful for anyone.

Solution 4

Create a categories table:

CREATE TABLE categories(
    cat_id int not null auto_increment primary key,
    cat_name varchar(255) not null,
    cat_description text
) ENGINE=InnoDB;

Create a products table and reference categories table:

CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

Create a vendors table and modify products table:

CREATE TABLE vendors(
    vdr_id int not null auto_increment primary key,
    vdr_name varchar(255)
)ENGINE=InnoDB;
 
ALTER TABLE products 
ADD COLUMN vdr_id int not null AFTER cat_id;

To add a foreign key (referencing vendors table) to the products table, you use the following statement:

ALTER TABLE products
ADD FOREIGN KEY fk_vendor(vdr_id)
REFERENCES vendors(vdr_id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

If you wish to drop that key then:

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

View more solutions

Comments

[laurajs]

Hi Jon!! thank you for your answer!! I am new to using php and this is how I done the relationship using SQL when I used visual studio but just wanted to make sure I am doing it right - So If I use the SQL statement this will create the foreign key in the table? will I have to make any changes to the column doctor id in te patient table?

[laurajs]

ALTER TABLE Patient ADD CONSTRAINT fk_to_Doctorid FOREIGN KEY (Doctorid) REFERENCES Doctor(DoctorId); - is this correct?

[Jon Story]

Yes, SQL statements directly affect the underlying structure: PHPMyAdmin is just a graphical interface that produces the SQL statements for you. You shouldn't have to change anything in the doctor_id table as long as it's already indexed. If it isn't, you'll need to turn it into an index as per @Alok's answer

[laurajs]

Thank you Alok! great answer I am new to using php so this is a great help - If I opt to create the foreign key using an sql statement - ` ALTER TABLE Patient ADD CONSTRAINT fk_to_Doctorid FOREIGN KEY (Doctorid) REFERENCES Doctor(DoctorId); ` will I have to index the column before doing this?

[laurajs]

so before applying the SQL statement index the columns that are going to be foreign keys ?

[laurajs]

Great Yagnik thank you for all of this information and help :)

[Jon Story]

Yes, exactly, or you'll get an error when running the SQL statement

[laurajs]

Jon thank you - great answer - thank you for your great guidance and help

[laurajs]

this now works john thank you - do I now give the contraint name a unique name for the specific relationship - also what options would you give for ON DELETE and ON UPDATE?

[Alok Patel]

No, The column needs to be indexed before adding foreign key constraint. You can add index using MySql statement. Refer this : dev.mysql.com/doc/refman/5.7/en/create-index.html

[laurajs]

I have indexed and ran the statement - this came up after - does it mean it was successful? - MySQL returned an empty result set (i.e. zero rows). (Query took 0.2090 sec)

[laurajs]

also my table with two IDs from other tables (composite keys) will I have to index them aswell

[Jon Story]

You don't need an entirely unique name for the relationship, typically you'd use fk_doctor_id, assuming your foreign key is doctor_id. As for on delete and on update... that would depend on your specific setup. I suspect you want to prevent deletion of a doctor if there are any patients assigned, therefore forcing the user to re-assign all patients before removing their dotor, which would avoid "orphaned" patients

[Alok Patel]

Yeah! Check in relation view for confirmation!

[Luke Savefrogs]

Note that this method creates the corresponding indexes if it doesn't find any... So if you're facing the problem of phpmyadmin asking you to create them before trying to add a relation, this is the way to go..

[mLstudent33]

Great answer @Alok.

[Rahul Kumar]

Changing Storage Engine locks the tables for transactions. The lock period depends upon the server. Keep this in mind while changing the Storage Engine.