Fremdschlüssel Error 150

[DerLohmi]

Hallo Gemeinde,

ich Doktor nun schon ein paar Stunden an 'nem SQL-Script rum. Die Datenbank habe ich erstellt mit DBDesignerFork. Wenn ich die Tabellen nun anlege, bekomme ich immer folgenden Fehler:

SQL-Befehl:

CREATE TABLE fahrzeug(
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT ,
einsatztyp_id INTEGER UNSIGNED NOT NULL ,
kennzeichen VARCHAR( 25 ) NULL ,
sitzplaetze INTEGER UNSIGNED NULL ,
bemerkung TEXT NULL ,
PRIMARY KEY ( id, einsatztyp_id ) ,
INDEX FK_einsatztyp( einsatztyp_id ) ,
UNIQUE INDEX fahrzeug_unique( kennzeichen ) ,
FOREIGN KEY ( einsatztyp_id ) REFERENCES einsatztyp( id ) ON DELETE SET NULL ON UPDATE SET NULL
) TYPE = InnoDB;

MySQL meldet: Dokumentation
#1005 - Can't create table './itprojekt/fahrzeug.frm' (errno: 150)

Die beiden Tabellen Mitarbeiter und Abteilung sind doch genauso aufgebaut wie Fahrzeuge und einsatztyp. Wieso funktioniert das dort nicht?? Hier nochmal der gesamte Code:

CREATE TABLE kunden (

  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,

  vorname VARCHAR(25)  NULL  ,

  nachname VARCHAR(25)  NULL  ,

  strasse VARCHAR(25)  NULL  ,

  plz INT  NULL  ,

  ort VARCHAR(25)  NULL    ,

PRIMARY KEY(id))

TYPE=InnoDB;







CREATE TABLE linien (

  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,

  name VARCHAR(25)  NULL  ,

  beschreibung TEXT  NULL    ,

PRIMARY KEY(id))

TYPE=InnoDB;







CREATE TABLE reisen (

  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,

  von DATE  NOT NULL  ,

  bis DATE  NOT NULL  ,

  name VARCHAR(25)  NULL  ,

  beschreibung TEXT  NULL  ,

  maxteiln TINYINT UNSIGNED  NULL    ,

PRIMARY KEY(id, von, bis))

TYPE=InnoDB;







CREATE TABLE abteilung (

  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,

  name VARCHAR(25)  NULL    ,

PRIMARY KEY(id))

TYPE=InnoDB;







CREATE TABLE mitarbeiter (

  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,

  abteilung_id INTEGER UNSIGNED  NOT NULL  ,

  vorname VARCHAR(25)  NULL  ,

  nachname VARCHAR(25)  NULL  ,

  username VARCHAR(12)  NOT NULL  ,

  passwd VARCHAR(12)  NULL    ,

PRIMARY KEY(id, abteilung_id)  ,

INDEX FK_abteilung(abteilung_id)  ,

UNIQUE INDEX mitarbeiter_unique(username),

  FOREIGN KEY(abteilung_id)

    REFERENCES abteilung(id)

      ON DELETE NO ACTION

      ON UPDATE CASCADE)

TYPE=InnoDB;




CREATE TABLE einsatztyp (

  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,

  name VARCHAR(25)  NULL    ,

PRIMARY KEY(id))

TYPE=InnoDB;





CREATE TABLE fahrzeug (

  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,

  einsatztyp_id INTEGER UNSIGNED  NOT NULL  ,

  kennzeichen VARCHAR(25)  NULL  ,

  sitzplaetze INTEGER UNSIGNED  NULL  ,

  bemerkung TEXT  NULL  ,

PRIMARY KEY(id, einsatztyp_id)  ,

INDEX FK_einsatztyp(einsatztyp_id)  ,

UNIQUE INDEX fahrzeug_unique(kennzeichen),

  FOREIGN KEY(einsatztyp_id)

    REFERENCES einsatztyp(id)

      ON DELETE SET NULL

      ON UPDATE SET NULL)

TYPE=InnoDB;







CREATE TABLE reisen_has_kunden (

  reisen_id INTEGER UNSIGNED  NOT NULL  ,

  kunden_id INTEGER UNSIGNED  NOT NULL  ,

  reisen_bis DATE  NOT NULL  ,

  reisen_von DATE  NOT NULL    ,

PRIMARY KEY(reisen_id, kunden_id, reisen_bis, reisen_von)  ,

INDEX FK_reisen(reisen_id, reisen_von, reisen_bis)  ,

INDEX FK_kunden(kunden_id),

  FOREIGN KEY(reisen_id, reisen_von, reisen_bis)

    REFERENCES reisen(id, von, bis)

      ON DELETE CASCADE

      ON UPDATE CASCADE,

  FOREIGN KEY(kunden_id)

    REFERENCES kunden(id)

      ON DELETE CASCADE

      ON UPDATE CASCADE);







CREATE TABLE reisen_has_mitarbeiter (

  reisen_id INTEGER UNSIGNED  NOT NULL  ,

  mitarbeiter_abteilung_id INTEGER UNSIGNED  NOT NULL  ,

  mitarbeiter_id INTEGER UNSIGNED  NOT NULL  ,

  reisen_bis DATE  NOT NULL  ,

  reisen_von DATE  NOT NULL    ,

PRIMARY KEY(reisen_id, mitarbeiter_abteilung_id, mitarbeiter_id, reisen_bis, reisen_von)  ,

INDEX FK_reisen(reisen_id, reisen_von, reisen_bis)  ,

INDEX FK_mitarbeiter(mitarbeiter_id, mitarbeiter_abteilung_id),

  FOREIGN KEY(reisen_id, reisen_von, reisen_bis)

    REFERENCES reisen(id, von, bis)

      ON DELETE CASCADE

      ON UPDATE CASCADE,

  FOREIGN KEY(mitarbeiter_id, mitarbeiter_abteilung_id)

    REFERENCES mitarbeiter(id, abteilung_id)

      ON DELETE CASCADE

      ON UPDATE CASCADE);







CREATE TABLE fahrzeug_linien (

  linien_id INTEGER UNSIGNED  NOT NULL  ,

  fahrzeug_id INTEGER UNSIGNED  NOT NULL  ,

  fahrzeug_einsatztyp_id INTEGER UNSIGNED  NOT NULL    ,

PRIMARY KEY(linien_id, fahrzeug_id, fahrzeug_einsatztyp_id)  ,

INDEX FK_linien(linien_id)  ,

INDEX FK_fahrzeug(fahrzeug_id, fahrzeug_einsatztyp_id),

  FOREIGN KEY(linien_id)

    REFERENCES linien(id)

      ON DELETE CASCADE

      ON UPDATE CASCADE,

  FOREIGN KEY(fahrzeug_id, fahrzeug_einsatztyp_id)

    REFERENCES fahrzeug(id, einsatztyp_id)

      ON DELETE CASCADE

      ON UPDATE CASCADE)

TYPE=InnoDB;







CREATE TABLE fahrzeug_reisen (

  reisen_id INTEGER UNSIGNED  NOT NULL  ,

  fahrzeug_id INTEGER UNSIGNED  NOT NULL  ,

  reisen_bis DATE  NOT NULL  ,

  reisen_von DATE  NOT NULL  ,

  fahrzeug_einsatztyp_id INTEGER UNSIGNED  NOT NULL    ,

PRIMARY KEY(reisen_id, fahrzeug_id, reisen_bis, reisen_von, fahrzeug_einsatztyp_id)  ,

INDEX FK_reisen(reisen_id, reisen_von, reisen_bis)  ,

INDEX FK_fahrzeug(fahrzeug_id, fahrzeug_einsatztyp_id),

  FOREIGN KEY(reisen_id, reisen_von, reisen_bis)

    REFERENCES reisen(id, von, bis)

      ON DELETE CASCADE

      ON UPDATE CASCADE,

  FOREIGN KEY(fahrzeug_id, fahrzeug_einsatztyp_id)

    REFERENCES fahrzeug(id, einsatztyp_id)

      ON DELETE CASCADE

      ON UPDATE CASCADE);







CREATE TABLE mitarbeiter_linien (

  linien_id INTEGER UNSIGNED  NOT NULL  ,

  mitarbeiter_abteilung_id INTEGER UNSIGNED  NOT NULL  ,

  mitarbeiter_id INTEGER UNSIGNED  NOT NULL    ,

PRIMARY KEY(linien_id, mitarbeiter_abteilung_id, mitarbeiter_id)  ,

INDEX FK_linien(linien_id)  ,

INDEX FK_mitarbeiter(mitarbeiter_id, mitarbeiter_abteilung_id),

  FOREIGN KEY(linien_id)

    REFERENCES linien(id)

      ON DELETE CASCADE

      ON UPDATE CASCADE,

  FOREIGN KEY(mitarbeiter_id, mitarbeiter_abteilung_id)

    REFERENCES mitarbeiter(id, abteilung_id)

      ON DELETE CASCADE

      ON UPDATE CASCADE)

TYPE=InnoDB;

Bin für jede Hilfe dankbar

Gruß Dennis

[blackfreeze]

ich glaub es liegt an der tatsache das du deinen primärschlüsselaus id & fremdschlüssel baust

reicht dir id als primärschlüssel nicht?

ps kennzeichen ist doch auch ein guter primärschlüssel....

[DerLohmi]

Fehler gefunden. Attribute und Schlüssel sind schon okay so. Der Fehler war, dass, wenn ein Einsatztyp gelöscht wird, ON DELETE SET NULL war. Allerdings kann ich die einsatztyp_id in fahrzeuge nicht auf Null setzten , da es ein PK ist. Ein einfach ON DELETE auf NO ACTION gesetzt und schon funktioniert der Müll.

Trotzdem vielen Dank
Gruß Dennis

[blackfreeze]

ja stimmt wenn der primärschlussel in einsatztyp gelöscht wird würde der fremdschlüssel bei dir null werden, wenn du es aber auf no action lässt könntest du mit der konsistenz probleme bekommen bsp:

Tabelle EinsatzTyp
ID       Bez
-------------
1        Miete
2        Verkauf
3        Leasing 

Tabelle Fahrzeug
ID       Kennzeichen     Typ_ID
-------------------------------
1        DD-AS 234        2
2        M-ED 1000        1
3        D-GH  34          3
4        M-KL  145         1 

Das bedeutet dein PS sieht so aus : (1|2) (2|1) (3|3) (4|1)
Wenn in EinsatzTyp ein Eintrag gelöscht wird oder sich der PS ändert, wird onDelete bzw. onUpdate getriggert und wenn die beiden gar nix machen, sieht es schlecht aus

DELETE FROM EinsatzTyp WHERE ID=3
--> kein Trigger
SELECT Bez FROM Fahrzeug, Einsatztyp WHERE Kennzeichen="D-GH 34" AND Typ_ID = Einsatztyp.ID
--> würde voll ins Lehre greifen 

im einfachstenfall sollte onDelete-Trigger prüfen ob noch ein FS in Fahrzeug existiert und gegebenenfalls abbrechen und onUpdate dieÄnderung durchreichen.

noch was: so richtig versteh ich nicht wieso du dir den ps aus 2 spalten zusammen stellst, id ist doch schon eineindeutig oder etwa nicht, nen fremdschlüsselmit als ps zu definieren bringt doch nur mehr stress...