ERROR 1005 (HY000): Can't create table when using foreign keys

what is wrong with the two foreign keys which I have marked with comments?

create database db;
use db;

create table Flug(
  Flugbez varchar(20),
  FDatum Date,
  Ziel va开发者_StackOverflow中文版rchar(20),
  Flugzeit int,
  Entfernung int,
  Primary Key (Flugbez, FDatum)
);

create table Flugzeugtyp(
  Typ varchar(20),
  Hersteller varchar(20),
  SitzAnzahl int,
  Reisegeschw int,
  primary key (Typ)
);

create table flugzeug(
  Typ varchar(20),
  SerienNr int,
  AnschDatum Date,
  FlugStd int,
  primary key(Typ,SerienNr),
  foreign key(Typ) references Flugzeugtyp(Typ)
);

create table Abflug(
  Flugbez varchar(20),
  FDatum Date,
  Typ varchar(20),
  Seriennr int,
  Kaptaen varchar(20),
  Primary key(Flugbez,FDatum,Typ,SerienNr),
  Foreign key(Flugbez) references Flug(Flugbez),
  -- Foreign key(FDatum) references Flug(FDatum),
  Foreign key(Typ) references Flugzeugtyp(Typ)
  -- ,Foreign key(SerienNr) references Flugzeug(SerienNr)
);

When I uncomment the foreign keys, I get the following error:

ERROR 1005 (HY000): Can't create table 'db.abflug' (errno: 150)

I use a Standard Installation of MySQL Server 5.5.

---

The problem is that the primary keys you're referencing are compound keys, but you're only trying to reference one of the columns (not the first) in the foreign keys.

For instance, you define the primary key primary key(Typ,SerienNr) in table flugzeug but in table Abflug you try to reference Foreign key(SerienNr) references Flugzeug(SerienNr).

You must reference the whole key (Foreign key(Typ, SerienNr) references Flugzeug(Typ,SerienNr)).

---

When executing your SQL code, the full message I get is:

[HY000][1822] Failed to add the foreign key constraint. Missing index for constraint 'abflug_ibfk_2' in the referenced table 'flug'

This means, that your column Flug.Flugbez is not indexed in Flug. You only created an index for the primary key which is a combination of Flugbez and FDatum. To achieve what you need, you have to create an index for Flug.Flugbez or reference the primary key Flugbez, FDatum.

Same applies for Flug.FDatum and Flugzeug.SerienNr that are referenced with Abflug.FDatum, Abflug.SerienNr.

To solve your issue you can create indexes for each column you are referencing. Example index creation you may adjust a little:

CREATE UNIQUE INDEX index_flugbez ON Flug (Flugbez);
CREATE UNIQUE INDEX index_flugdatum ON Flug (FDatum);
CREATE UNIQUE INDEX index_seriennr ON flugzeug (SerienNr);