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

User · Answer

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

User · Answer

The key must be indexed to apply foreign key constraint  To do that follow the steps    Open table structure   2nd tab  See the last column action where multiples action options are there  Click on Index  this will make the column indexed  Open relation view and add foreign key constraint     You will be able to assign DOCTOR ID as foreign now

User · Answer

When you create table than you can give like follows   CREATE TABLE categories  cat id int not null auto increment primary key  cat name varchar 255  not null  cat description text   ENGINE InnoDB    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    and when after the table create like this     ALTER table name     ADD CONSTRAINT constraint name     FOREIGN KEY foreign key name columns      REFERENCES parent table columns      ON DELETE action     ON UPDATE action    Following on example for it   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 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    For drop the key  ALTER TABLE table name  DROP FOREIGN KEY constraint name    Hope this help to learn FOREIGN keys works

User · Answer

A simple SQL example would be like this  ALTER TABLE   lt table name gt   ADD   lt column name gt   INT 11  NULL DEFAULT NULL    Make sure you use back ticks    in table name and column name

User · Answer

You can do it the old fashioned way    with an SQL statement that looks something like this  ALTER TABLE table name     ADD CONSTRAINT fk foreign key name     FOREIGN KEY  foreign key name      REFERENCES target table target key name     This assumes the keys already exist in the relevant table

[php] How to create a foreign key in phpmyadmin

Examples related to php

Examples related to mysql

Examples related to database

Examples related to phpmyadmin