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