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
This question is related to
php
mysql
database
phpmyadmin
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
The key must be indexed to apply foreign key constraint. To do that follow the steps.
You will be able to assign DOCTOR_ID as foreign now.
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.
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
A simple SQL example would be like this:
ALTER TABLE `<table_name>` ADD `<column_name>` INT(11) NULL DEFAULT NULL ;
Make sure you use back ticks `` in table name and column name
Source: Stackoverflow.com