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

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

Answer 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.

Answer 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.

Answer 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;

Answer 5

In phpmyadmin, Go to Structure tab, select Relation view as shown in image below. Here you will find the tabular form to add foreign key constrain name, current table column, foreign key database, table and column