Adding foreign key in MySQL

Adding foreign key in MySQL

This sql sintax can create link betwen key in some tables. Please used in InnoDB only. Here the code:

ALTER TABLE child_table_name
ADD FOREIGN KEY (P_ID)
REFERENCES parent_table_name (P_ID)

#example:
ALTER TABLE member_detail
ADD FOREIGN KEY (username)
REFERENCES member (username)

#foreign key will be deleted/updated if primary key deleted/updated
ON DELETE CASCADE ON UPDATE CASCADE

#parent will not be deleted if any child row exist
ON DELETE RESTRICT

#The script will be:
ALTER TABLE member_detail
ADD FOREIGN KEY (username)
REFERENCES member (username)
ON DELETE CASCADE ON UPDATE CASCADE

#Delete exist foreign key
ALTER TABLE member_detail DROP FOREIGN KEY member_detail_ibfk_1;

Good Luck !!!

Read More