MySQL Multiple foreign keys pointing to the same table

Question

I have a table called teams and a table called games. teams have id, name, ..... games have id, hteam_id, vteam_id, loc, .... I want hteam_id and vteam_id to be a foreign key in the command table. How do you do this?

Answer 1

You can add two foreign keys using the following:

alter table game add foreign key game_hteam_id(hteam_id) references teams(id) , add foreign key game_vteam_id(vteam_id) references teams(id); 

Answer 2

Read this first:

FOREIGN KEY LIMITATIONS

Example:

 CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB;