assigning Foreign Key?

[ok]

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.

 

 

[Fadion]

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.