CODEWITHSUNDEEP
mysql
vector8188
vector8188

Reputation: 1393

not able to add a foreign key constraint

I am not able to add a foreign key constraint to a database I am creating it errors out like this, also please let me know if there are issue with my logical design, I am trying to create an REST api harnessing this database structure. I am trying to practice my database design skills so this is a toy example.

current database schema

use ToolsDB;
create table player(
   player_id INT NOT NULL AUTO_INCREMENT,
   firstname VARCHAR(100) NOT NULL,
   lastname VARCHAR(40) NOT NULL,
   nickname VARCHAR(40) NOT NULL,
   wins integer,
   losses integer,
   current_win_streak integer,
   created DATETIME,
   last_seen DATETIME,
   PRIMARY KEY ( player_id )
   );


create table Attacker_Battles(
    attacker_id INT NOT NULL AUTO_INCREMENT,
    battle_id INT NOT NULL,
    player_id INT NOT NULL,
    PRIMARY KEY ( attacker_id ),
    FOREIGN KEY (battle_id) REFERENCES Battles (battle_id),
    FOREIGN KEY (attacker_id) REFERENCES player (attacker_id)
    );
create table Defender_Battles(
    defender_id INT NOT NULL AUTO_INCREMENT,
    battle_id INT NOT NULL,
    player_id INT NOT NULL,
    PRIMARY KEY ( defender_id ),
    FOREIGN KEY (battle_id) REFERENCES Battles (battle_id),
    FOREIGN KEY (defender_id) REFERENCES player (defender_id)
    );
create table Winner_Battles(
    winner_id INT NOT NULL AUTO_INCREMENT,
    battle_id INT NOT NULL,
    player_id INT NOT NULL,
    PRIMARY KEY ( winner_id ),
    FOREIGN KEY (battle_id) REFERENCES Battles (battle_id),
    FOREIGN KEY (winner_id) REFERENCES player (player_id)
    );

create table Battles(
    battle_id INT NOT NULL AUTO_INCREMENT,
    starttime DATETIME,
    endtime DATETIME,
    PRIMARY KEY ( battle_id )
    );

10:32:30    create table Attacker_Battles(  attacker_id INT NOT NULL AUTO_INCREMENT,  battle_id INT NOT NULL,  player_id INT NOT NULL,     PRIMARY KEY ( attacker_id ),  FOREIGN KEY (battle_id) REFERENCES Battles (battle_id),     FOREIGN KEY (attacker_id) REFERENCES player (attacker_id)     )    Error Code: 1215. Cannot add foreign key constraint 0.020 sec

Upvotes: 0

Views: 24

Answers (1)

Jens G
Jens G

Reputation: 63

You don't need the attacker_id as a foreign key in the attacker_battles-table, because it is the primary key. I think you wanted to link the player_id to the player table. So use the player_id for the foreign key.

Upvotes: 1

Related Questions