Question 174347 in Mysql Question posted in
The official MySQL documentation can be found here.

Why am I getting MySQL error 1822 when creating a new table with a FOREIGN KEY?

I tried

CREATE TABLE `valorant`.`abilities` (
`PlayerId` INT NOT NULL,
`AgentName` VARCHAR(45) NOT NULL,
`AbilityName` VARCHAR(45) NOT NULL,
`KeyBind` VARCHAR(45) NULL,
`Cost` INT NULL,
PRIMARY KEY (`PlayerId`, `AgentName`, `AbilityName`));

CREATE TABLE `valorant`.`abilitystats` (
`PlayerId` INT NOT NULL,
`AgentName` VARCHAR(45) NOT NULL,
`AbilityName` VARCHAR(45) NOT NULL,
`EnemiesAffected` INT NULL,
`AlliesAffected` INT NULL,

CONSTRAINT `FK_abilitystats_abilities`
FOREIGN KEY (`AbilityName`, `AgentName`)
REFERENCES `abilities` ( `AgentName`, `AbilityName`),
PRIMARY KEY (`PlayerId`)
);

This is the error i got

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint ‘FK_abilitystats_abilities’ in the referenced table ‘abilities’

I have no idea what is wrong with this. i follow a tutorial for FOREIGN KEY

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Like @Barmar said The primary key begins with PlayerId, so AgentName, AbilityName isn't a key by itself. All i did was to move PlayerId to end in PRIMARY KEY ( AgentName, AbilityName,PlayerId));


  2. 0

    You need to add an index that matches the foreign key:

    ALTER TABLE abilities ADD INDEX (`AgentName`, `AbilityName`);

    Note that usually it’s better for a foreign key to reference the primary key of the other table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search