CODEWITHSUNDEEP
mysql
Rich
Rich

Reputation: 47

MySQL Multiple foreign keys pointing to same table

I have a table called teams and a table called games. teams has id, name, ..... the games has id, hteam_id, vteam_id, loc, .... I want the hteam_id and vteam_id to each be foreign key into the teams table. How do you do it

Upvotes: 4

Views: 6624

Answers (2)

Arnaud Le Blanc
Arnaud Le Blanc

Reputation: 99911

You can add the two foreign key using this:

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);

Upvotes: 5

Naveed
Naveed

Reputation: 42093

First Read This:

FOREIGN KEY Constraints

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;

Upvotes: 2

Related Questions