Foreign Key Problem errno 150

Hi Leute,

ich habe hier einen MySQL-Server Version 5.0.18-nt
Wenn ich einen Foreign-Key erstellen will dann bekomme ich folgende
Meldung:

MySQL Error 1005
Can't create table '.\projektblatt\#sql-770_bb.frm' (errno: 150)

Das Statement lautet (von MySQL Administrator erstellt):
ALTER TABLE `projektblatt`.`tblartikel` ADD CONSTRAINT `FK_tblartikel_sb`
FOREIGN KEY `FK_tblartikel_sb` (`system_version`)
REFERENCES `tblstammblatt` (`nr`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

Die Datentypen der beiden Spalten sind identisch varchar(4) ('nr' in
tblstammblatt ist Primary Key). In der Datenbank existieren bereits Daten
(93 Datensätze in tblartikel), die konsistent sind (zumindest soweit ich das
hier sehe...). Die Kardinalität soll 1:C sein (also Null-Werte existieren in
'system_version').

show innodb status; ergibt:
LATEST FOREIGN KEY ERROR ------------------------ 070416 10:03:56 Error in
foreign key constraint of table projektblatt/#sql-770_bb: FOREIGN KEY
FK_tblartikel_stammblatt (system_version) REFERENCES
projektblatt.tblstammblatt (nr) ON DELETE RESTRICT ON UPDATE RESTRICT:
Cannot find an index in the referenced table where the referenced columns
appear as the first columns, or column types in the table and the referenced
table do not match for constraint. Note that the internal storage type of
ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such
columns in old tables cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constra ints.html
for correct foreign key definition.

Kapitel 14.2.6.4. aus der Anleitung hilft mir leider nicht weiter.
Wie soll ich folgendes interpretieren?:

- In der referenzierenden Tabelle muss ein Index bestehen, in dem die
Fremdschlüsselspalten als
erste Spalten in derselben Reihenfolge aufgeführt sind. Ein solcher Index
wird automatisch auf
der referenzierenden Tabelle angelegt, wenn er noch nicht existiert.
- In der referenzierten Tabelle muss ein Index bestehen, in dem die
referenzierten Spalten als erste
Spalten in derselben Reihenfolge aufgeführt sind.

Ich weiß nicht mehr weiter, für Tipps wäre ich dankbar.

Ciao,
Roland

Re: Foreign Key Problem errno 150

Roland Aleksa wrote:
> ich habe hier einen MySQL-Server Version 5.0.18-nt
> Wenn ich einen Foreign-Key erstellen will dann bekomme ich folgende
> Meldung:
>
> MySQL Error 1005
> Can't create table '.\projektblatt\#sql-770_bb.frm' (errno: 150)
>
> Das Statement lautet (von MySQL Administrator erstellt):
> ALTER TABLE `projektblatt`.`tblartikel` ADD CONSTRAINT
> `FK_tblartikel_sb` FOREIGN KEY `FK_tblartikel_sb` (`system_version`)
> REFERENCES `tblstammblatt` (`nr`)
> ON DELETE RESTRICT
> ON UPDATE RESTRICT;
>
> Die Datentypen der beiden Spalten sind identisch varchar(4) ('nr' in
> tblstammblatt ist Primary Key). In der Datenbank existieren bereits
> Daten (93 Datensätze in tblartikel), die konsistent sind (zumindest
> soweit ich das hier sehe...). Die Kardinalität soll 1:C sein (also
> Null-Werte existieren in 'system_version').
>
> show innodb status; ergibt:
> LATEST FOREIGN KEY ERROR ------------------------ 070416 10:03:56 Error
> in foreign key constraint of table projektblatt/#sql-770_bb: FOREIGN KEY
> FK_tblartikel_stammblatt (system_version) REFERENCES
> projektblatt.tblstammblatt (nr) ON DELETE RESTRICT ON UPDATE RESTRICT:
> Cannot find an index in the referenced table where the referenced
> columns appear as the first columns, or column types in the table and
> the referenced table do not match for constraint. Note that the internal
> storage type of ENUM and SET changed in tables created with >=
> InnoDB-4.1.12, and such columns in old tables cannot be referenced by
> such columns in new tables. See
> http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constra ints.html
> for correct foreign key definition.

Kontrolliere nochmal, ob `nr` wirklich als erster Eintrag eines Indexes
ist, die Fehlermeldung suggeriert nämlich gegenteiliges.

> Kapitel 14.2.6.4. aus der Anleitung hilft mir leider nicht weiter.
> Wie soll ich folgendes interpretieren?:
>
> - In der referenzierenden Tabelle muss ein Index bestehen, in dem die
> Fremdschlüsselspalten als
> erste Spalten in derselben Reihenfolge aufgeführt sind. Ein solcher
> Index wird automatisch auf
> der referenzierenden Tabelle angelegt, wenn er noch nicht existiert.
> - In der referenzierten Tabelle muss ein Index bestehen, in dem die
> referenzierten Spalten als erste
> Spalten in derselben Reihenfolge aufgeführt sind.

- Einen Index auf `system_version` hast du, denke ich.
- Haben beide Spalten den gleichen Zeichensatz? (mein Favorit)
- Bist du vielleicht von
http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes .html
betroffen für eine der beiden Tabellen (vielleicht ist eine der Tabellen
noch mit 'ner alten Version angelegt worden)?

Grüße
Kai

Re: Foreign Key Problem errno 150

Hallo Kai,

> Kontrolliere nochmal, ob `nr` wirklich als erster Eintrag eines Indexes
> ist, die Fehlermeldung suggeriert nämlich gegenteiliges.

'nr' ist der einzige Eintrag vom Index 'PRIMARY'

siehe Dump von stammblatt:

CREATE TABLE `tblstammblatt` (
`nr` varchar(4) NOT NULL,
`system` varchar(6) NOT NULL,
`desinfektion` int(10) unsigned NOT NULL,
`programm` varchar(10) NOT NULL,
`spannung` int(10) unsigned NOT NULL,
`temp` int(10) unsigned default NULL,
`tempfix` tinyint(1) default '0',
`zeit` int(10) unsigned default NULL,
`zeitfix` tinyint(1) default '0',
`leistung` int(10) unsigned NOT NULL COMMENT 'Watt',
`hinweis` varchar(45) default NULL COMMENT 'frueher Option',
`dosierung` varchar(10) default NULL COMMENT '?nur Auswahl?',
`aufkleber` varchar(255) default NULL COMMENT 'Pfad zur Datei',
`aufsteck` int(10) unsigned default NULL,
`aktuell` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`nr`),
KEY `FK_tblstammblatt_system` (`system`),
KEY `FK_tblstammblatt_prog` (`programm`),
KEY `FK_tblstammblatt_spannung` (`spannung`),
KEY `FK_tblstammblatt_desi` (`desinfektion`),
KEY `FK_tblstammblatt_aufsteck` (`aufsteck`),
CONSTRAINT `FK_tblstammblatt_aufsteck` FOREIGN KEY (`aufsteck`) REFERENCES
`tblaufsteck` (`id`),
CONSTRAINT `FK_tblstammblatt_desi` FOREIGN KEY (`desinfektion`) REFERENCES
`tbldesinfektion` (`id`),
CONSTRAINT `FK_tblstammblatt_prog` FOREIGN KEY (`programm`) REFERENCES
`tblprogramm` (`programm`),
CONSTRAINT `FK_tblstammblatt_spannung` FOREIGN KEY (`spannung`) REFERENCES
`tblspannung` (`id`),
CONSTRAINT `FK_tblstammblatt_system` FOREIGN KEY (`system`) REFERENCES
`tblsystem` (`system`)
) TYPE=InnoDB;

> - Einen Index auf `system_version` hast du, denke ich.

Nein, der sollte laut Anleitung automatisch angelegt werden. So
interpretiere ich die Anleitung.
Aber: Selbst mit, von Hand angelegten Index klappt es auch nicht.

> - Haben beide Spalten den gleichen Zeichensatz? (mein Favorit)

Die Zeichensätze waren tatsächlich verschieden (Latin1, UTF8).
Ich habe mich jetzt für UTF8 entschieden. Das Problem besteht aber weiterhn.

> - Bist du vielleicht von
> http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes .html
> betroffen für eine der beiden Tabellen

Nein, der Server wurde mit Version 5.0.18 aufgesetzt und arbeitet auch jetzt
noch damit.
Die Stammblatt-Tabelle ist mit 5.0.27 auf einem anderen Rechner erstellt
worden, allerdings ist das Dump ja wieder mit 5.0.18 eingespielt worden

Ciao,
Roland

Re: Foreign Key Problem errno 150

Roland Aleksa wrote:
> Hallo Kai,
>
>> Kontrolliere nochmal, ob `nr` wirklich als erster Eintrag eines Indexes
>> ist, die Fehlermeldung suggeriert nämlich gegenteiliges.
>
> 'nr' ist der einzige Eintrag vom Index 'PRIMARY'
>
> siehe Dump von stammblatt:
>
> CREATE TABLE `tblstammblatt` (
> `nr` varchar(4) NOT NULL,
> `system` varchar(6) NOT NULL,
> `desinfektion` int(10) unsigned NOT NULL,
> `programm` varchar(10) NOT NULL,
> `spannung` int(10) unsigned NOT NULL,
> `temp` int(10) unsigned default NULL,
> `tempfix` tinyint(1) default '0',
> `zeit` int(10) unsigned default NULL,
> `zeitfix` tinyint(1) default '0',
> `leistung` int(10) unsigned NOT NULL COMMENT 'Watt',
> `hinweis` varchar(45) default NULL COMMENT 'frueher Option',
> `dosierung` varchar(10) default NULL COMMENT '?nur Auswahl?',
> `aufkleber` varchar(255) default NULL COMMENT 'Pfad zur Datei',
> `aufsteck` int(10) unsigned default NULL,
> `aktuell` tinyint(1) NOT NULL default '1',
> PRIMARY KEY (`nr`),
> KEY `FK_tblstammblatt_system` (`system`),
> KEY `FK_tblstammblatt_prog` (`programm`),
> KEY `FK_tblstammblatt_spannung` (`spannung`),
> KEY `FK_tblstammblatt_desi` (`desinfektion`),
> KEY `FK_tblstammblatt_aufsteck` (`aufsteck`),
> CONSTRAINT `FK_tblstammblatt_aufsteck` FOREIGN KEY (`aufsteck`)
> REFERENCES `tblaufsteck` (`id`),
> CONSTRAINT `FK_tblstammblatt_desi` FOREIGN KEY (`desinfektion`)
> REFERENCES `tbldesinfektion` (`id`),
> CONSTRAINT `FK_tblstammblatt_prog` FOREIGN KEY (`programm`) REFERENCES
> `tblprogramm` (`programm`),
> CONSTRAINT `FK_tblstammblatt_spannung` FOREIGN KEY (`spannung`)
> REFERENCES `tblspannung` (`id`),
> CONSTRAINT `FK_tblstammblatt_system` FOREIGN KEY (`system`) REFERENCES
> `tblsystem` (`system`)
> ) TYPE=InnoDB;

Wer hat denn dieses CREATE TABLE ausgegeben?
- Es heißt seit langem schon ENGINE=... und nicht TYPE=...
- DEFAULT CHARSET fehlt.
- und dieses DEFAULT '0' für Integer-Spalten sollte es unter einem 5.0
nicht mehr automatisch geben...

Wie ist die Tabellendefinition für die andere Tabelle?

>> - Einen Index auf `system_version` hast du, denke ich.
>
> Nein, der sollte laut Anleitung automatisch angelegt werden. So
> interpretiere ich die Anleitung.
> Aber: Selbst mit, von Hand angelegten Index klappt es auch nicht.
>
>> - Haben beide Spalten den gleichen Zeichensatz? (mein Favorit)
>
> Die Zeichensätze waren tatsächlich verschieden (Latin1, UTF8).
> Ich habe mich jetzt für UTF8 entschieden. Das Problem besteht aber
> weiterhn.

Kommt jetzt die gleiche Fehlermeldung von SHOW INNODB STATUS?

>> - Bist du vielleicht von
>> http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes .html
>> betroffen für eine der beiden Tabellen
>
> Nein, der Server wurde mit Version 5.0.18 aufgesetzt und arbeitet auch
> jetzt noch damit.
> Die Stammblatt-Tabelle ist mit 5.0.27 auf einem anderen Rechner erstellt
> worden, allerdings ist das Dump ja wieder mit 5.0.18 eingespielt worden

Gut, da hast du ja (eigentlich) einige Altlasten nicht.

Grüße
Kai

Re: Foreign Key Problem errno 150

Hallo Kai,

> Wer hat denn dieses CREATE TABLE ausgegeben?

Achso, ganz vergessen. Dump im Kompatibilitätsmodus. Sorry.

Ich habs aber hinbekommen.
Es war doch der falsche Zeichensatz. Ich habe zwar den Default-Zeichensatz
geändert, allederings blieben die Spalten latin1.
Bei dem Dump, den ich erstellte habe ich es gemerkt.

Ich danke Dir. Der Tipp:

>>>- Haben beide Spalten den gleichen Zeichensatz? (mein Favorit)

war goldrichtig.

Ciao,
Roland