Thomas Wiedmann https://twiedmann.de
• Sie befinden sich hier: Startseite > SQL-Backstube > ERROR 1005 (HY000): ... (errno: 150)

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

18.09.2010: ERROR 1005 (HY000): ... (errno: 150)

Problemstellung

Übrigens, auch schon mal über die Fehlermeldung ERROR 1005 (HY000): Can't create table 'xyz' (errno: 150) gestolpert? Bestimmt! Hier finden Sie ein paar Hinweise und Lösungen dazu. UPDATE 14.10.2012


Getestet mit folgender Datenbank:

* MySQL 5.1.30


Tabellen mit Fremdschlüsseln erzeugen

Listing 1:

  1. CREATE TABLE master (
  2. m_id INT NOT NULL,
  3. PRIMARY KEY (m_id)
  4. );
  5. Query OK, 0 rows affected (0.14 sec)
  7. CREATE TABLE client (
  8. c_id INT NOT NULL,
  9. m_id INT NOT NULL,
  10. PRIMARY KEY (c_id),
  11. CONSTRAINT fk_master
  12. FOREIGN KEY (m_id)
  13. REFERENCES masker (m_id)
  14. ) ENGINE=InnoDB;
  15. ERROR 1005 (HY000): Can't create table 'sample.client' (errno: 150)

Kein Problem, denken wird uns. Wozu gibt es denn den Befehl SHOW WARNINGS oder SHOW ERRORS.


SHOW WARNINGS / SHOW ERRORS

Listing 2:

  1. mysql> show warnings;
  2. +-------+------+-------------------------------------------------+
  3. | Level | Code | Message |
  4. +-------+------+-------------------------------------------------+
  5. | Error | 1005 | Can't create table 'sample.client' (errno: 150) |
  6. +-------+------+-------------------------------------------------+
  7. 1 row in set (0.02 sec)
  9. mysql> show errors;
  10. +-------+------+-------------------------------------------------+
  11. | Level | Code | Message |
  12. +-------+------+-------------------------------------------------+
  13. | Error | 1005 | Can't create table 'sample.client' (errno: 150) |
  14. +-------+------+-------------------------------------------------+
  15. 1 row in set (0.00 sec)

Leider ist diese Information nicht so aussagekräftig wie gehofft und was bedeutet schon wieder errno: 150 ? Ach ja, schon wieder "losgooglen.." ist der stille (oder weniger stille), aber wohl übliche Stoßseuzer. Wohl dem, der das kleine PERROR Tool von MySQL kennt. Zu finden ist es (bei Windows) im /bin Verzeichnis.


PERROR

Listing 3:

  1. ../bin>perror 150
  2. MySQL error code 150: Foreign key constraint is incorrectly formed

Ah ja, irgend was stimmt mit dem Fremdschlüssel nicht und richtig, der Tippfehlerteufel hat wieder zugeschlagen. Anstatt ..REFERENCES masker (m_id).. muss es natürlich ..REFERENCES master (m_id).. lauten. Gesagt getan geändert und ...


Listing 4:

  1. CREATE TABLE client (
  2. c_id INT NOT NULL,
  3. m_id INT NOT NULL,
  4. PRIMARY KEY (c_id),
  5. CONSTRAINT fk_master
  6. FOREIGN KEY (m_id)
  7. REFERENCES master (m_id)
  8. ) ENGINE=InnoDB;
  9. ERROR 1005 (HY000): Can't create table 'sample.client' (errno: 150)

Wieder der selbe Fehler...! Nun ist wirklich guter Rat teuer oder.. Schon wollen wir doch noch losgooglen, da fällt der Blick auf ..) ENGINE=InnoDB;... Stimmt, bei der Tabelle MASTER habe wir keine Engine vorgegeben. Welche Engine wird eigentlich per Default zugewiesen?


MY.INI und die default-storage-engine

Listing 5:

  1. /**
  2. * MY.INI
  3. */
  4. ...
  5. # The default storage engine that will be used when create new tables when
  6. # default-storage-engine=INNODB
  7. default-storage-engine=MYISAM
  8. ..

Da hat doch mal wieder einer schnell die Default-Einstellung in der MY.INI angepaßt und richtig, die Tabelle MASTER wurde mit der ENGINE MyISAM erzeugt.

Listing 6:

  1. mysql> show create table master;
  2. +--------+-------------------------------------------+
  3. | Table | Create Table |
  4. +--------+-------------------------------------------+
  5. | master | CREATE TABLE `master` ( |
  6. `m_id` int(11) NOT NULL,
  7. PRIMARY KEY (`m_id`)
  8. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
  9. +--------+-------------------------------------------+
  10. 1 row in set (0.00 sec)

ALTER ENGINE

Passen wir schnell die Storage Engine an mit..(Listing 7:)

Listing 7:

  1. ALTER TABLE master
  2. ENGINE = InnoDB;
  3. Query OK, 0 rows affected (0.09 sec)
  4. Records: 0 Duplicates: 0 Warnings: 0

Nun muss es einfach klappen mit dem CREATE TABLE, sonst ...

Listing 8:

  1. CREATE TABLE client (
  2. c_id INT NOT NULL,
  3. m_id INT NOT NULL,
  4. PRIMARY KEY (c_id),
  5. CONSTRAINT fk_master
  6. FOREIGN KEY (m_id)
  7. REFERENCES master (m_id)
  8. ) ENGINE=InnoDB;
  9. Query OK, 0 rows affected (0.05 sec)

UPDATE 14.10.2012


NOT NULL und ON DELETE SET NULL (Tipp von Jörg Häfner)

Dass NULL und NOT NULL in Verbindung mit Fremdschlüsseln und DELETE Regeln, auch so seine Tücken hat, läßt sich mit Fehler beim ERROR 1005 (HY000) gut darstellen.

Listing 8:

  1. CREATE TABLE master (
  2. m_id INT NOT NULL,
  3. PRIMARY KEY (m_id)
  4. ) ENGINE InnoDB;
  5. Query OK, 0 rows affected (0.14 sec)
  7. CREATE TABLE client (
  8. c_id INT NOT NULL,
  9. m_id INT NOT NULL,
  10. PRIMARY KEY (c_id),
  11. CONSTRAINT fk_master
  12. FOREIGN KEY (m_id)
  13. REFERENCES master (m_id)
  14. ON DELETE SET NULL
  15. ) ENGINE=InnoDB;
  16. ERROR 1005 (HY000): Can't create table 'sample.client' (errno: 150)

Wo liegt hier das Problem? Durch die Fremdschlüsselbeziehung (FOREIGN KEY) soll die Spalte m_id in der Tabelle CLIENT auf NULL gesetzt werden, wenn der Hauptdatensatz in der Tabelle MASTER gelöscht wird. Dies widerspricht allerdings der NOT NULL Regel der Spalte m_id und löst den Fehler 1005 aus.

Listing 9:

  1. CREATE TABLE client (
  2. c_id INT NOT NULL,
  3. m_id INT NOT NULL,
  4. PRIMARY KEY (c_id),
  5. CONSTRAINT fk_master
  6. FOREIGN KEY (m_id)
  7. REFERENCES master (m_id)
  8. ON DELETE RESTRICT
  9. ) ENGINE=InnoDB;

Listing 10:

  1. CREATE TABLE client (
  2. c_id INT NOT NULL,
  3. m_id INT,
  4. PRIMARY KEY (c_id),
  5. CONSTRAINT fk_master
  6. FOREIGN KEY (m_id)
  7. REFERENCES master (m_id)
  8. ON DELETE SET NULL
  9. ) ENGINE=InnoDB;

Zwei Beispiele zeigen jeweils eine Lösung aus dem Dilemma. Bei Listing 9: wird aus dem ON DELETE SET NULL ein ON DELETE RESTRICT, d. h. der Datensatz in der Tabelle CLIENT blockiert das Löschen des Hauptdatensatzes in der Tabelle MASTER. Bei Listing 10: erhält die Spalte m_id die Möglichkeit auf den Wert NULL gesetzt zu werden. Somit kann MySQL ein ON DELETE SET NULL korrekt ausführen.


Nicht ganz identische Datentypen

Ist ein Datentyp INT identisch zu einem INT UNSIGNED? MySQL meint NEIN und sagt ERROR 1005 (HY000).

Listing 11:

  1. CREATE TABLE master (
  2. m_id INT UNSIGNED NOT NULL,
  3. PRIMARY KEY (m_id)
  4. ) ENGINE InnoDB;
  5. Query OK, 0 rows affected (0.14 sec)
  7. CREATE TABLE client (
  8. c_id INT NOT NULL,
  9. m_id INT NOT NULL,
  10. PRIMARY KEY (c_id),
  11. CONSTRAINT fk_master
  12. FOREIGN KEY (m_id)
  13. REFERENCES master (m_id)
  14. ON DELETE CASCADE
  15. ) ENGINE=InnoDB;
  16. ERROR 1005 (HY000): Can't create table 'sample.client' (errno: 150)

MySQL erzwingt bei Fremdschlüsselbeziehungen korrekte und identisch Datentypen bei der Spalte m_id. Wer in der Tabelle MASTER einen INT UNSIGNED als Datentyp festlegt, muss dies eben auch bei der Tabelle CLIENT für die Spalte m_id berücksichtigen.

Listing 12:

  1. CREATE TABLE client (
  2. c_id INT NOT NULL,
  3. m_id INT UNSIGNED NOT NULL,
  4. PRIMARY KEY (c_id),
  5. CONSTRAINT fk_master
  6. FOREIGN KEY (m_id)
  7. REFERENCES master (m_id)
  8. ON DELETE CASCADE
  9. ) ENGINE=InnoDB;
  10. Query OK, 0 rows affected (0.00 sec)

Resumee

Wie immer gibt es viele Fehlerquellen und wie immer wird kein Fettnäpfchen ausgelassen. Also nicht nur die korrekte Angabe des FOREIGN KEY ist wichtig, sondern auch die Speicherengine InnoDB muss stimmen. Auch diesmal gibt es eine Lösung für den, der die richtigen Tools und Einstellungen kennt und findet.


https://twiedmann.de


Sitemap - Inhaltsverzeichnis

© 2002-2017 by Thomas Wiedmann : (Stand : 11.01.2015).