Problemer med foreign key i MySQL

Bruger du direkte SQL eller PHPMyAdmin?

ERROR 1822: Failed to add the foreign key constraint. Missing index for constraint 'teamid' in the referenced table 'teamtable'

virker mystisk da teamtable.teamid er primary key og derfor har et index.

Hmm, er SQL software og PHP over nettet?

I så fald så bruger jeg Workbench software.

Jeg har faktisk aldrig brugt workbench. Skriver du SQL ind og klikker execute? Eller bruger du en GUI til at vælge FK?

Skriver ikke SQL, det gør den selv.

ALTER TABLE `bttest`.`match`
ADD INDEX `teamid_idx` (`teamid.a` ASC, `teamid.b` ASC) VISIBLE;
;
ALTER TABLE `bttest`.`match`
ADD CONSTRAINT `teamid`
  FOREIGN KEY (`teamid.a` , `teamid.b`)
  REFERENCES `bttest`.`teamtable` (`teamid` , `teamid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Og så her med fejlen...

Operation failed: There was an error while applying the SQL script to the database.
Executing:
ALTER TABLE `bttest`.`match`
ADD INDEX `teamid_idx` (`teamid.a` ASC, `teamid.b` ASC) VISIBLE;
;
ALTER TABLE `bttest`.`match`
ADD CONSTRAINT `teamid`
  FOREIGN KEY (`teamid.a` , `teamid.b`)
  REFERENCES `bttest`.`teamtable` (`teamid` , `teamid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ERROR 1822: Failed to add the foreign key constraint. Missing index for constraint 'teamid' in the referenced table 'teamtable'
SQL Statement:
ALTER TABLE `bttest`.`match`
ADD CONSTRAINT `teamid`
  FOREIGN KEY (`teamid.a` , `teamid.b`)
  REFERENCES `bttest`.`teamtable` (`teamid` , `teamid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION

Jeg tror at du skal have 2 foreign keys med 1 felt hver i.s.f. 1 foreign key med 2 felter.

Teknisk er problemet at der er index paa teamid men ikke paa (teamid,teamid).

Men du vil ikke lave index paa (teamid,teamid) - du vil have 2 FK.

Som jeg forstår din løsning, så vil du have at jeg laver to connections via foreign key.
Hvor jeg prøvede at lave
(teamtable) teamid <> (matchtable) teamid.a & teamid.b

Så vil du have
(teamtable) teamid <> (matchtable) teamid.a
(teamtable) teamid <> (matchtable) teamid.b

Hvis det er forstået korrekt, så virkede det ikke. Jeg har nu også prøvet at ændre navn til teamid, så det er helt ens og KUN på den ene. Virker stadig ikke.


Operation failed: There was an error while applying the SQL script to the database.
Executing:
CREATE TABLE `bttest`.`match` (
  `matchid` INT NOT NULL,
  `teamid` INT NULL,
  `teamid` INT NULL,
  PRIMARY KEY (`matchid`),
  INDEX `teamid_idx` (`teamid` ASC) VISIBLE,
  CONSTRAINT `teamid`
    FOREIGN KEY (`teamid`)
    REFERENCES `bttest`.`teamtable` (`teamid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ERROR 1060: Duplicate column name 'teamid'
SQL Statement:
CREATE TABLE `bttest`.`match` (
  `matchid` INT NOT NULL,
  `teamid` INT NULL,
  `teamid` INT NULL,
  PRIMARY KEY (`matchid`),
  INDEX `teamid_idx` (`teamid` ASC) VISIBLE,
  CONSTRAINT `teamid`
    FOREIGN KEY (`teamid`)
    REFERENCES `bttest`.`teamtable` (`teamid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

Eksempel der umiddelbart ser ud til at virke:

CREATE TABLE teamtable (
  teamid int NOT NULL PRIMARY KEY,
  teamname VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE `match` (
  `matchid` INT NOT NULL PRIMARY KEY,
  `teamida` INT NULL,
  `teamidb` INT NULL, 
  CONSTRAINT `fk_teamida`
    FOREIGN KEY (`teamida`)
    REFERENCES teamtable (`teamid`),
  CONSTRAINT `fk_teamidb`
    FOREIGN KEY (`teamidb`)
    REFERENCES teamtable (`teamid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    )ENGINE=InnoDB;

#9

Man kan ikke have 2 felter med samme navn.

Løsningen er 2 FK.

match.teama -> team.teamid
match.teamb -> team.teamid

betyder at man kun kan oprette en match hvis begge teams eksisterer. Det giver mening!

(match.teama,match.teamb) -> (team.teamid,team.teamid)

betyder at man kun kan oprette matche hvor et team spiller mod sig selv. Det giver ikke mening.

Eneste alternativ til de 2 FK er ingen FK og så lade applikations logikken håndtere integriteten.

Tak for jeres input, det lykkedes aldrig at få til at virke så jeg tænker at det bliver som arne's sidste forslag, ingen FK til den.

Erik's eksempel burde virke.

Jeg kan ogsaa godt lave et eksempel hvis du vil.

PS: Hvad bruger du for at tilgaa databasen?

Jeg bruger IntelliJ til programmering, hvori jeg selvfølgelig tilgår databasen og MySQL Workbench til at opsætte tables osv.

Via JDBC eller JPA eller?

JDBC

Engang når du har god tid bør du nok kigge på JPA.

:-)

Hvis du vil see en masse kode:
    http://external.vajhoej.dk:81/arne/articles/javadb.html