ok Posted September 2, 2008 Share Posted September 2, 2008 Hi guys i want to assign a foreign key. I have two tables below, CREATE TABLE `joealex3_contest`.`user_join` ( `id` INT NOT NULL AUTO_INCREMENT , `user_name` VARCHAR( 100 ) NOT NULL , `email` VARCHAR( 100 ) NOT NULL , `date_join` DATETIME NOT NULL , PRIMARY KEY ( `id` ) , INDEX ( `user_name` , `email` , `date_join` ) ) ENGINE = MYISAM CREATE TABLE `joealex3_contest`.`invitation` ( `id` INT NOT NULL AUTO_INCREMENT , `user_name` VARCHAR( 100 ) NOT NULL , `invited` VARCHAR( 100 ) NOT NULL , PRIMARY KEY ( `id` ) , INDEX ( `user_name` , `invited` ) ) ENGINE = MYISAM Noticed that the user_name field column exist on both table. I want the user_name column field in the invitation table to have a foreign key.can you show me the exact changes or table structure for this. And another question is it really necessary to make the column field user_name that has the foreign key to have primary key? if yes why? Thank you. Quote Link to comment Share on other sites More sharing options...
Fadion Posted September 2, 2008 Share Posted September 2, 2008 I'm not very sure as i'm not a MySQL expert, but from what I know, foreign keys are supported only by the InnoDB storage engine. The syntax should be: INDEX (user_id, id), FOREIGN KEY (user_id) REFERENCES user_join(id) ON UPDATE CASCADE ON DELETE CASCADE As you can see, i assigned a user_id field as the foreign key because a foreign key must reference a primary key of the parent table. The "on update cascade" and "on delete cascade" make it so that when a row is deleted in the parent table, every row that references it in the child table is deleted or updated too. Take a look here and here for some detailed information. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.