[MySQL/InnoDB] FK's circuliere referentie? (1005)

[roy-t]

Ik heb de volgende database structuur, omdat ik wat aan het stoeien ben met het maken van een eigen CMS (puur om te leren).

Topic
FK:User (de auteur)

Comment
FK:User, Topic (auteur van het comment, topic waarbij het comment hoort).

User.
(Geen FK's).

Tijdens het aanmaken van de tabel Comment gaat het fout:

SQL:

CREATE TABLE `php`.`comment` (
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `Date` DATETIME NOT NULL,
  `Text` TEXT NOT NULL,
  PRIMARY KEY (`ID`),
  CONSTRAINT `FK_Topic` FOREIGN KEY `FK_Topic` (`ID`)
    REFERENCES `topic` (`ID`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `FK_User` FOREIGN KEY `FK_User` (`ID`)
    REFERENCES `user` (`ID`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT

Ik krijg de foutmelding:

code:

MySQL error number 1005
Can't create table 'php.comment' (errno:121)

Na wat googl-en kwam ik er op uit dat deze fouten vaak duiden op circuliere referenties. (Waarom dit niet mag is me niet 100% duidelijk, maar zal liggen aan de onderliggende indices structuur).

De circuliere referentie is mogelijk: comments->FK_topic->FK_user<-FK_user<-comments, hoewel dit natuurlijk niet echt circulier is (let op de 1 richting pijltjes). Helaas kan ik nu niet de door mij be-oogde datastructuur neerzetten.

Ik dacht dat ik een vrij logische opzet had qua database, hoe kan ik InnoDB laten weten wat ik hier precies wil? Of op een andere manier dezelfde logische database structuur hebben.

Ik wil bij het laden van een topic gewoon alle comments laden (comments waarbij comments.FK_topic = topic) en van de comments de juiste username weergeven (user laden waar user comment.FK_user = user).

Edit: elke tabel heeft een uniek ID die net zo gemaakt is als de tabel hierboven (Primary KEY ('ID'),
Edit2: wat ik ook vergeten was erbij te vertellen is dat het ook fout gaat als ik alleen de FK_user neerzet in comments, het is dus waarschijnlijk niet het probleem met 2FK's wat in veel google-hits op deze error codes naar boven komt.

[ Voor 8% gewijzigd door roy-t op 12-02-2009 15:42 ]

[RikTW]

Waarom zou je User nog opnemen in Comment? Topic ID is al een FK, dan kun je daaruit toch al user achterhalen?
Niet goed gelezen

Kun je niet topics en comments in 1 tabel gooien, met een userID (FK) en een topicID (FK) die wijst naar de PK van diezelfde tabel (en leeg is als een record zelf een topic is)?

[ Voor 51% gewijzigd door RikTW op 12-02-2009 16:32 ]

[roy-t]

RikTW schreef op donderdag 12 februari 2009 @ 15:59:
Waarom zou je User nog opnemen in Comment? Topic ID is al een FK, dan kun je daaruit toch al user achterhalen?
Niet goed gelezen

Kun je niet topics en comments in 1 tabel gooien, met een userID (FK) en een topicID (FK) die wijst naar de PK van diezelfde tabel (en leeg is als een record zelf een topic is)?

Dat zou opzich kunnen hoewel dat design wel iets onhandiger is bij het bijvoorbeeld selecteren van de meest recente topics of comments.

Gelukkig heb ik net het probleem gevonden, en het verbaasde me nogal (ook door de nogal onzinnige foutmelding)

Foreign Keys mogen nooit dezelfde naam hebben als al bestaande Foreign keys, blijkbaar worden die in InnoDB global opgeslagen (hoe dit in andere DB's is weet ik niet). Lijkt me heel vreemd ik dacht dat een ForeignKey gewoon een column was in een tabel met een speciale trigger die er voor zorgde dat als je er iets instopte de waarde wel moest kloppen, maar blijkbaar is dit heel erg anders opgelost.

Ik heb er nu FK_comments_User en FK_topic_User van gemaakt en nu gaat het wel goed.

[RikTW]

roy-t schreef op donderdag 12 februari 2009 @ 16:39:
[...]


Dat zou opzich kunnen hoewel dat design wel iets onhandiger is bij het bijvoorbeeld selecteren van de meest recente topics of comments.

Gelukkig heb ik net het probleem gevonden, en het verbaasde me nogal (ook door de nogal onzinnige foutmelding)

Foreign Keys mogen nooit dezelfde naam hebben als al bestaande Foreign keys, blijkbaar worden die in InnoDB global opgeslagen (hoe dit in andere DB's is weet ik niet). Lijkt me heel vreemd ik dacht dat een ForeignKey gewoon een column was in een tabel met een speciale trigger die er voor zorgde dat als je er iets instopte de waarde wel moest kloppen, maar blijkbaar is dit heel erg anders opgelost.

Ik heb er nu FK_comments_User en FK_topic_User van gemaakt en nu gaat het wel goed.

Haha, idd overheen gekeken. foreign keys zijn natuurlijk ook db objecten die een unieke naam moeten hebben.