Robert: MySQL, Foreign Key, Error 1005/Errno 150

Hallöle,

ich versuche gerade, in MySQL ( version: 4.1.10a-nt ) zwei Tabellen zu verknüpfen.
Die eine Tabelle besteht aus einer Liste von Kategorien (id und name - aber da kommen noch weitere Spalten dazu, so daß ENUM nicht verwendet werden kann).
Die zweite Tabelle soll die eigentlichen Daten enthalten (die irrelevanten Spalten hab ich erstmal eliminiert), zu denen eben auch die Kategorie gehört.
Eigentlich ja nichts ungewöhnliches:

CREATE TABLE IF NOT EXISTS categories
(
    id      INT(3) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(20) NOT NULL
) TYPE=INNODB;

CREATE TABLE IF NOT EXISTS places
(
    id          INT(6) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    category    INT(3) NOT NULL,
    INDEX (category),
    FOREIGN KEY (category) REFERENCES categories (id)
) TYPE=INNODB;

Das Resultat ist:
Die erste Tabelle läßt sich problemlos erzeugen.
Bei der zweiten kommt:
ERROR 1005 (HY000): Can't create table '.\testdb\places.frm' (errno: 150)

Ok, im Manual nach 1005 gesucht, ergibt:

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message string refers to errno 150, this means that the table creation failed because a foreign key constraint was not correctly formed.

Also ist mein Foreign Key schlecht geformt. Aha.
Weiter sagt das Manual:

The syntax of a foreign key constraint definition in InnoDB looks like this:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

Sehe nur ich nicht, was da falsch sein kann?
CONSTRAINT symbol ist optional, hab ich weggelassen.
FOREIGN KEY hab ich hingeschrieben.
id ist optional, hab ich weggelassen.
(index_col_name,...) hab ich als (category) angegeben - ich will ja nur die eine Spalte benutzen.
REFERENCES tbl_name hab ich als REFERENCES categories angegeben.
(index_col_name,...) hab ich als (id) angegeben, denn ich will ja die Spalte id verknüpfen.
Der ganze Rest ist optional, hab ich (vorläufig) weggelassen.

Ok, weitergucken.

Foreign keys definitions are subject to the following conditions:

1 Both tables must be InnoDB type.
    2 In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.
    3 In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
    4 Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key, because indexes on those columns must always include a prefix length.

1 ist erfüllt, steht ja TYPE=INNODB hinter den Create Tables.
2 ist erfüllt, hab ja extra noch INDEX(category) angegeben.
3 sollte aufgrund des Primary Key für id erfüllt sein (aber auch ein explizites zusätzliches INDEX(id) ändert nichts
4 versteh ich nicht 100%, aber die zu verknüpfenden Spalten sind INT(3), also weder BLOB noch TEXT.

Außerdem heißt es an anderer Stelle:
Functionality added or changed:

* [...]
    * Automatically create a suitable index on a FOREIGN KEY, if the user does not create one. Removes most of the cases of Error 1005 (errno 150) in table creation.

Der Index würde also automatisch erzeugt, wenn ich ihn nicht angäbe.

SHOW INNODB STATUS zur Anzeige des Fehlers beim Foreign key sagt:

LATEST FOREIGN KEY ERROR
------------------------
050324 10:42:18 Error in foreign key constraint of table irland/places:
FOREIGN KEY (category) REFERENCES categories (id)

) TYPE=INNODB:
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.
See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
for correct foreign key definition.

Hm. Der Verweis geht auf genau die Stelle, die ich schon gefunden hatte - wo ich der Meinung bin, daß alles ok ist.

Was mache ich falsch?
Ich kann anhand des Manuals nicht erkennen, wo das Problem ist - das Archiv hier gibt auch nichts her.

Vielen Dank im Voraus!
cu,
Robert

  1. Hallo Robert

    ich versuche gerade, in MySQL ( version: 4.1.10a-nt ) zwei Tabellen zu verknüpfen.

    CREATE TABLE IF NOT EXISTS categories
    (
        id      INT(3) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name     VARCHAR(20) NOT NULL
    ) TYPE=INNODB;

    Wenn Du einen bestimmten Namen für Deinen Index haben möchtest, dann solltest Du diesen im CREATE-TABLE-Statement angeben:

    CREATE  TABLE  IF  NOT  EXISTS categories
    (
        id INT( 3  ) ZEROFILL NOT  NULL  AUTO_INCREMENT,
        name VARCHAR( 20  )  NOT  NULL ,
        PRIMARY KEY id( id )
    ) TYPE  = INNODB;

    Nun sollte die nächste Anweisung erfolgreich ausgeführt werden können.
    Vergiss bitte nicht vorher, die vorhandene Tabelle zu löschen :-)

    CREATE TABLE IF NOT EXISTS places
    (
        id          INT(6) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name        VARCHAR(100) NOT NULL,
        category    INT(3) NOT NULL,
        INDEX (category),
        FOREIGN KEY (category) REFERENCES categories (id)
    ) TYPE=INNODB;

    Erfolgreich getestet unter MySQL 3.23.58

    [... ellenlange Zitate aus dem Handbuch ...]
    Bitte gib' in Zukunft nur die relevanten Links an, zitiere ggf. ein, zwei Sätze mit Angabe der Quelle (URL und evtl. Abschnitt).

    Freundliche Grüße

    Vinzenz

    1. Hallöle Vinzenz,

      CREATE TABLE IF NOT EXISTS categories
      (
          id      INT(3) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
          name     VARCHAR(20) NOT NULL
      ) TYPE=INNODB;
      Wenn Du einen bestimmten Namen für Deinen Index haben möchtest, dann solltest Du diesen im CREATE-TABLE-Statement angeben:

      Ich will keinen bestimmten Namen für meinen Index. Ich will eine FOREIGN KEY - Verknüpfung.

      CREATE  TABLE  IF  NOT  EXISTS categories
      (
          id INT( 3  ) ZEROFILL NOT  NULL  AUTO_INCREMENT,
          name VARCHAR( 20  )  NOT  NULL ,
          PRIMARY KEY id( id )
      ) TYPE  = INNODB;

      Das Verschieben des PRIMARY KEY aus der Spaltendeklaration nach unten bringt keine Änderung.

      Vergiss bitte nicht vorher, die vorhandene Tabelle zu löschen :-)

      Natürlich nicht. Zu Beginn dieses Scripts droppe ich alle Tables.
      Sonst würd ich ja wahnsinnig.

      Erfolgreich getestet unter MySQL 3.23.58

      Schön. Nützt mir aber gar nichts. Denn unter 4.1.10 geht es bei mir nach wie vor nicht.

      [... ellenlange Zitate aus dem Handbuch ...]
      Bitte gib' in Zukunft nur die relevanten Links an, zitiere ggf. ein, zwei Sätze mit Angabe der Quelle (URL und evtl. Abschnitt).

      Ich hab jeweils die Stellen zitiert, auf die ich mich direkt bezog. Und nicht mehr.

      Robert

      1. Hallo Robert

        Ich will keinen bestimmten Namen für meinen Index. Ich will eine FOREIGN KEY - Verknüpfung.

        Sämtliche Beispiele im MySQL-Handbuch arbeiten mit benanntem Index. Warum willst Du unbedingt auf einen solchen Namen verzichten? Was erwartest Du davon?

        Der automatisch vergebene Name besitzt das Präfix pk. Infolgedessen solltest Du

        CREATE TABLE IF NOT EXISTS places
        (
            id          INT(6) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
            name        VARCHAR(100) NOT NULL,
            category    INT(3) NOT NULL,
            INDEX (category),
            FOREIGN KEY (category) REFERENCES categories (pk_id)
        ) TYPE=INNODB;

        verwenden.

        Freundliche Grüße

        Vinzenz

  2. Hallöle,

    Fehler gefunden.

    CREATE TABLE IF NOT EXISTS categories
    (
        id      INT(3) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,

    CREATE TABLE IF NOT EXISTS places
    (
        category    INT(3) NOT NULL,

    Ich weiß nicht, was mich dazu gebracht hat, in der categories-Tabelle bei id das ZEROFILL hinzuschreiben (bzw. nach Copy/Paste nicht wegzulöschen).

    Da in der places-Tabelle bei category KEIN ZEROFILL steht, passen die Spaltentypen nicht zusammen.

    Nach Entfernen des ZEROFILL klappt es jetzt endlich.

    Danke, Vinzenz, für Deine Bemühungen.

    cu,
    Robert