-2

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

user3783243
  • 5,368
  • 5
  • 22
  • 41
Era
  • 29
  • 4
  • i tried add all PK from abilities table to FK in abilitystats and it works, but i don't really know why ``` FOREIGN KEY (`PlayerId`,`AbilityName`, `AgentName`) REFERENCES `abilities` ( `PlayerId`,`AgentName`, `AbilityName`), ``` – Era May 23 '23 at 19:46
  • 1
    Add additional information to your question, not as a comment. – Progman May 23 '23 at 19:47
  • 1
    Your schema is very confusing. In `abilitystats` you can't have duplicate `PlayerId`, but you can have duplicates in `abilities`. – Barmar May 23 '23 at 19:52
  • Clearly when pinned down this will be a faq. Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] Reflect research in posts. – philipxy May 24 '23 at 03:29

2 Answers2

3

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

Era
  • 29
  • 4
2

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I have them as PK in the abilities table. PRIMARY KEY (`PlayerId`, `AgentName`, `AbilityName`));. that's why I'm confused. it is supposed to work fine..... – Era May 23 '23 at 20:08
  • The primary key begins with `PlayerId`, so `AgentName, AbilityName` isn't a key by itself. – Barmar May 23 '23 at 20:11
  • Only the prefix of a key is a key by itself. – Barmar May 23 '23 at 20:11
  • Thank you sir! it works for me. can you recommend me where should I read about that. Im new to database and how do I upvote this as well – Era May 23 '23 at 20:21
  • See the [documentation of multi-column indexes](https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html) – Barmar May 23 '23 at 20:27