[MySQL] Foreign key wil niet aangemaakt worden

[_Thanatos_]

Ik heb twee tabellen die ik aan elkaar wil knopen met een foreign key. De tabellen zien er zo uit:

`classes`

`class`	CHAR(1) NOT NULL	(PK)
`name`	VARCHAR(25) NOT NULL

`codes`

`class`	CHAR(1) NOT NULL	(indexed)
`code`	CHAR(5) NOT NULL
`name`	VARCHAR(25) NOT NULL

Vervolgens maak ik de foreign key aan. Dat doe ik met de query browser, en die probeert de volgende SQL uit te voeren:

SQL:

ALTER TABLE `codes`
    ADD CONSTRAINT `fk_codes_class`
    FOREIGN KEY `fk_codes_class` (`class`)
    REFERENCES `classes` (`class`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

En dan krijg ik deze error:

code:

Can't create table 'db.#sql-518_bc' (errno: 150)

Ik heb al wat rondgezocht en ben erachter gekomen dat het belangrijk schijnt te zijn dat de datatypes van de primary en foreign keys hetzelfde zijn. Dat zijn ze. Ook moeten de collations van beide velden (of tabellen?) hetzelfde zijn. Is ook allemaal zo. Ik zie geen verschillen. Ik kan ook nergens aanwijzingen vinden dat een primary/foreign key combo altijd integer moet zijn, dus CHAR(1) in mijn geval zou gewoon moeten werken (de error zou ook anders moeten zijn als datatypes het probleem was).

En of er nou op codes.class een index staat of niet, maakt niets uit. Ook die ON DELETE en ON UPATE clauses weglaten maakt niets uit. De naam van de foreign key weglaten maakt niets uit. Ook een SHOW CREATE TABLE `codes`; geeft aan dat er niet toevallig al een foreign key op het betreffende veld zit die de query browser niet ziet, dus dat is het probleem ook niet...

Hoe nu verder?

Wat specs:
Windows Server 2008 R2
MySQL 5.1.52-community
Alles is utf8 en utf8_general_ci
Alles is InnoDB

[cariolive23]

Heb je toevallig al data in de tabel staan die voor conflicten zorgt? Check ook nog even een keertje of er wel een index is aangemaakt, zonder index accepteert MySQL geen foreign key.

[_Thanatos_]

Nee helaas, het probleem blijft na dit gechecked te hebben. Dit geeft 0 rows:

SQL:

SELECT * FROM `codes` WHERE `class` NOT IN (SELECT `class` FROM `classes`);

En hiermee verifiëer ik dat die index echt gemaakt is:

SQL:

SHOW INDEXES IN `codes`;

Andere mogelijkheden nog?

Ik heb trouwens ook al geprobeerd om foreign key constraints tijdelijk uit te zetten met:

SQL:

SET FOREIGN_KEY_CHECKS = 0;

En dat helpt ook niet. Op zich logisch, want de data is gewoon in orde.

[gvdh]

Heb je al geprobeerd de data te exporteren en de foreign key te laten aanmaken bij het creëren van de tabellen?

[_Thanatos_]

Ja dat heb ik gedaan. En dat werkt gewoon

maargoed, ik heb nog een tabel van ongeveer een GB aan 7,5mln records, waar ik ook een foreign key in moet metselen... en daarbij is het transplanteren van al die data niet echt een handige optie.

Bovendien moet je een foreign key gewoon achteraf kunnen toevoegen. Het zit volgens de documentatie in het alter-table statement toch?

[Soultaker]

In je eerste query, moet die tweede `fk_codes_class` (achter FOREIGN KEY dus) niet gewoon `codes` zijn, of helemaal weggelaten worden? Je bent een constraint aan het toevoegen op een kolom van een tabel, meestal is de syntax daarvoor "kolom" of "tabel(kolom)" maar bij "key(kolom)" kan ik me niets voorstellen.

edit:
Volgens de docs is de syntax als volgt:

SQL:

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)

Dus na FOREIGN KEY volgt optioneel een index name. `fk_codes_class` lijkt me hoe dan ook verkeerd want dat is de naam van de foreign key die je probeert te maken hier; het is geen index en je kunt er sowieso nog niet aan refereren voor 'ie gedefinieerd is.

[ Voor 82% gewijzigd door Soultaker op 25-11-2010 13:51 ]

[_Thanatos_]

Ja hey, ik heb het niet verzonnen
Dat maakt die query browser ervan. Ik heb me ook al af zitten vragen waarom hij op de plek van [index_name] niet de naam van de index maar de naam van de foreign key neerzet. Maar kennelijk is het toch correct, want bij een nieuwe lege schone kale tabel doet ie dat ook, en dan gaat het wel goed. Alleen dan is die constructie onderdeel van een CREATE TABLE ipv een ALTER TABLE.

Misschien dat in MySQL een foreign key toch stiekem impliciet een index is? Dat is in MSSQL nml ook zo, iirc.

/edit
Als ik een nieuw veld aan een bestaande tabel toevoeg (dat nullable is), dan kan ik daar gewoon een foreign key aan toevoegen. En de indexnaam is dan de foreign-key-naam. Werkt dus kennelijk wel.

[ Voor 23% gewijzigd door _Thanatos_ op 25-11-2010 14:13 ]

[Soultaker]

Maar het werkt dus ook niet als je 'm helemaal weglaat? (Documentatie suggereert dat 'ie optioneel is.)

Dan weet ik 't verder ook niet. De documentatie suggereert dat dit gewoon moet kunnen, maar als 't niet werkt, dan is er blijkbaar iets vreemds aan de hand ("error 150" helpt ook niet echt natuurlijk).

[ Voor 22% gewijzigd door Soultaker op 25-11-2010 14:33 ]

[cariolive23]

index_name represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if InnoDB creates an index for the foreign key, it uses index_name for the index name.

Wat je kan doen, maar het is niet meer dan een workaround, is het volgende:
- Maak een tabel "codes_nieuw" aan, mét foreign key
- kopieer alle data van "codes" naar "codes_nieuw"
- hernoem "codes" naar "codes_oud"
- hernoem "codes_nieuw" naar "codes".

Het is wat omslachtig, maar hiermee maak je uiteindelijk een nieuwe tabel "codes" aan mét daarin alle benodigde data én een foreign key. Wanneer alles werkt, kun je "codes_oud" weggooien.

De foutmeldingen van MySQL zijn inderdaad een drama, heb je vrijwel niets aan.

[_Thanatos_]

Dat had ik dus gedaan, en daarmee is het inmiddels gelukt.

Bedankt iig

[Killemov]

cariolive23 schreef op donderdag 25 november 2010 @ 14:49:
[...]


Wat je kan doen, maar het is niet meer dan een workaround, is het volgende:
- Maak een tabel "codes_nieuw" aan, mét foreign key
- kopieer alle data van "codes" naar "codes_nieuw"
- hernoem "codes" naar "codes_oud"
- hernoem "codes_nieuw" naar "codes".

Het is wat omslachtig, maar hiermee maak je uiteindelijk een nieuwe tabel "codes" aan mét daarin alle benodigde data én een foreign key. Wanneer alles werkt, kun je "codes_oud" weggooien.

De foutmeldingen van MySQL zijn inderdaad een drama, heb je vrijwel niets aan.

Dat werkt dus uitsluitend met een leaf-table. Als er andere tabellen zijn die aan deze tabel refereren ga je mank. Ik begrijp werkelijk niet waarom het niet met een alter table zou kunnen met MySQL. Maar goed, het is voor mij al weer even geleden dat ik MySQL vaarwel heb gezegd en Postgresql heb omarmd.

[Soultaker]

Je kunt foreign key constraints tijdelijk uitzetten in de database server; dan kun je je database verwijderen en opnieuw aanmaken, ook als andere tabellen er naar verwijderen. Geen idee of de checks dan alsnog uitgevoerd worden als je ze weer aan zet, maar als je de inhoud niet veranderd zou dat niet uit hoeven maken.

In plaats van tabellen te verwijderen en opnieuw aan te maken, kun je ook nieuwe kolommen toevoegen (met de juiste constraints en een kopie van de oude data), dan de oude verwijderen, en de nieuwe naar de oude hernoemen. Dat voorkomt dat je overige data in de tabel kwijtraakt, en je vermijdt problemen met references van andere tabellen als die niet naar de oude kolommen refereren. (Meestal refereren foreign key constraints aan kolommen die zelf geen foreign key constraints hebben, dus dan gaat dit goed.)

Ik kwam trouwens ook dit tegen op de server:

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

Misschien kun je daar iets mee?

[cariolive23]

Soultaker schreef op vrijdag 26 november 2010 @ 17:58:
Je kunt foreign key constraints tijdelijk uitzetten in de database server; dan kun je je database verwijderen en opnieuw aanmaken, ook als andere tabellen er naar verwijderen. Geen idee of de checks dan alsnog uitgevoerd worden als je ze weer aan zet, maar als je de inhoud niet veranderd zou dat niet uit hoeven maken.

Nee, deze controle wordt niet meer uitgevoerd. Het is dus mogelijk dat je met een corrupte database komt te zitten. Ga dus vooral geen data toevoegen of wijzigen.

Note
Setting foreign_key_checks to 1 does not trigger a scan of the existing table data. Therefore, rows added to the table while foreign_key_checks = 0 will not be verified for consistency.

RTFM

[_Thanatos_]

Je kunt foreign key constraints tijdelijk uitzetten in de database server

Zoals ik al zei, dat hielp helemaal niet, en dat was het probleem ook niet. Het probleem is dat MySQL geen foutmelding kan uitpoepen waar we wat mee kunnen, dus heb ik met _old en _new tabellen moeten goochelen.

Er was vast een hele goeie reden te verzinnen dat wat ik wilde, niet kon/mocht, maar MySQL wilde me helaas niet laten weten wat er nou écht aan de hand was. Maar dat betekent niet automatisch dat die foreign keys ook de oorzaak zijn.

[Soultaker]

_Thanatos_ schreef op zondag 28 november 2010 @ 04:23:
Zoals ik al zei, dat hielp helemaal niet, en dat was het probleem ook niet.

Dat begrijp ik; ik reageerde op Killemov.

Het probleem is dat MySQL geen foutmelding kan uitpoepen waar we wat mee kunnen, dus heb ik met _old en _new tabellen moeten goochelen. [..] Er was vast een hele goeie reden te verzinnen dat wat ik wilde, niet kon/mocht, maar MySQL wilde me helaas niet laten weten wat er nou écht aan de hand was.

Leverde die "SHOW ENGINE INNODB STATUS" ook niets op?