FOREIGN KEYS - 1005 Error

[hstoellinger]

Guten Abend!
Kann mir jemand sagen, was ich bei der Definition der Foreign Keys zwischen den untenstehenden zwei Tabellen
falsch mache:
Tabelle (1):
CREATE TABLE stuecke
(
id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key: Unique record ID.',
heft_id int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'ID des Hefts für das Stück.',
seite int(11) NOT NULL DEFAULT 0,
titel varchar(255) NOT NULL DEFAULT '' COMMENT 'der Stücktitel',
genre_id int(11) NOT NULL DEFAULT 0,
szene_id int(11) NOT NULL DEFAULT 0,
status_id int(11) NOT NULL DEFAULT 0,
verlag_id int(11) NOT NULL DEFAULT 0,
komponist_id int(11) NOT NULL DEFAULT 0,
arrangeur_id int(11) NOT NULL DEFAULT 0,
arrangement_id int(11) NOT NULL DEFAULT 0,
besetzung_id int(11) NOT NULL DEFAULT 0,
tonartabfolge varchar(64) NOT NULL DEFAULT '' COMMENT 'die Abfolge der Tonart (z.B. bei Volksmusikstücken)',
bemerkung varchar(255) NOT NULL DEFAULT '' COMMENT 'Allgemeine Bemerkungen',
PRIMARY KEY (id),
KEY titel (titel(191)),
FOREIGN KEY (heft_id) REFERENCES hefte(id),
FOREIGN KEY (szene_id) REFERENCES szenen(id),
FOREIGN KEY (status_id) REFERENCES status(id),
FOREIGN KEY (verlag_id) REFERENCES verlage(id),
FOREIGN KEY (komponist_id) REFERENCES komponisten(id),
FOREIGN KEY (arrangeur_id) REFERENCES arrangeure(id),
FOREIGN KEY (arrangement_id) REFERENCES arrangements(id),
FOREIGN KEY (besetzung_id) REFERENCES besetzungen(id)
)
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB
;

Tabelle (2):
use musikdb;
DROP TABLE if exists ausgaben;
CREATE TABLE ausgaben
(
id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key: Unique record ID.',
stck_id int(11) unsigned NOT NULL COMMENT 'Stueck-ID',
heft_id int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'ID des Hefts für das Stück.',
seite int(11) NOT NULL DEFAULT 0,
status_id int(11) NOT NULL DEFAULT 0,
verlag_id int(11) NOT NULL DEFAULT 0,
arrangeur_id int(11) NOT NULL DEFAULT 0,
arrangement_id int(11) NOT NULL DEFAULT 0,
besetzung_id int(11) NOT NULL DEFAULT 0,
tonartabfolge varchar(64) NOT NULL DEFAULT '' COMMENT 'die Abfolge der Tonart (z.B. bei Volksmusikstücken)',
bemerkung varchar(255) NOT NULL DEFAULT '' COMMENT 'Allgemeine Bemerkungen',
PRIMARY KEY (id),
FOREIGN KEY (stck_id) REFERENCES stuecke(id),
FOREIGN KEY (heft_id) REFERENCES hefte(id),
FOREIGN KEY (status_id) REFERENCES status(id),
FOREIGN KEY (verlag_id) REFERENCES verlage(id),
FOREIGN KEY (arrangeur_id) REFERENCES arrangeure(id),
FOREIGN KEY (arrangement_id) REFERENCES arrangements(id),
FOREIGN KEY (besetzung_id) REFERENCES besetzungen(id)
)
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB
;
Die erste Tabelle existiert natürlich bereits.
Danke für gute Hinweise.
H. Stöllinger

 

[hstoellinger]

Auch alle anderen referenzierten Tabellen existieren

 

[dabadepdu]

Nur eine Idee:
Du verwendest bei der 2. Tabelle die Anweisung
use musikdb;
Bei der ersten nicht. Mglw. liegt damit eine Inkonsistenz vor. Beim Anlegen der ersten Tabelle, befindest Du Dich noch in einer anderen DB. In der DB musikDB ist wahrscheinlich (laut der Fehlermeldung) eine richtige Tabelle, aber mit anderer PK Definition vorhanden (ältere Version)

Wirklich nachvollziehbar ist Dein Problem nicht, weil es nur ein unvollständiges Fragement mit Fließtext dazwischen ist. Man kann also nicht davon ausgehen, dass der Code so wie Du ihn hier darstellst auch bei Dir abläuft und den Fehler produziert. Eigentlich ist nicht mal klar, ob der Fehler aus dem Titel sich auf den FK stck_id bezieht. Ich weiß nicht, in welcher Reihenfolge das bearbeitet wird und die originale Fehlermeldung fehlt.

 

[hstoellinger]

Danke, dass Du Dich sogar am Samstag bemühst!
Zu "use musikdb": die statements "
use musikdb; DROP TABLE if exists stuecke;
hatte ich leider bei der Problembeschreibung ausgelassen. Sie werden aber in BEIDEN Fällen verwendet.
Die Definitionen werden übrigens aus einem von der Linux-Commandline über bash-script mit symbolischen Parameters gestarteten MariaDB client abgegeben (mysql -d $db ...).
Die erste Tabelle existiert ohnedies (wie erwähnt) bereits in DB musikdb und funktioniert problemlos, z. B. in diversen VIEWS mit anderen darin erwähnten Tabellen. Ich will eigentlich nur die Tatsache berücksichtigen, dass bei manchen Stücken mehrere Editionen (z. B. von verschiedenen Arrangeuren, Verlagen, in verschiedenen Tonarten, etc.) existieren. Verschiedene "ausgaben" beziehen sich dann eben auf ein und dasselbe Stück. Letztlich werde ich dann alle db-fields aus der Tabelle "stuecke" entfernen, die logisch zur Tabelle "ausgaben" gehören. Die Beziehung von Stücken zu Komponisten sollte nebenbei bemerkt eigentlich als eine m:n-Beziehung abgebildet werden.

 

[dabadepdu]

Also schick doch den Teil des Scripts, der nicht funktioniert und die Fehlermeldung, sonst ist es nur Spekulation.

 

[akretschmer]

aufgrund des konsequenten Verschweigens wichtiger Informationen wie exakter und vollständiger Fhlermeldung und Aufbau der anderen beteiligten Tabellen sowie der exorbitant nichtssagenden Fehlermeldungen an sich von MySQL & dessen Derivaten kann, wie schon gesagt, nur erraten werden, was das Problem ist. Ich werfr mal in den Ring: inkompatible Datentypen und/oder fehlende PK-Definitionen für die FK in den verheimlichten Tabellen.

 

[hstoellinger]

Also:
(1) Fehlermeldung:
ERROR 1005 (HY000): Can't create table `musikdb`.`ausgaben` (errno: 150 "Foreign key constraint is incorrectly formed
")
(2) "Aufbau der anderen beteiligten Tabellen":
Die Foreign Key Definitionen innerhalb der Tabelle "stuecke" zu allen anderen "verheimlichten" (??) Tabellen funktionieren klaglos. Nicht so allerdings bei der Definition derselben innerhalb "ausgaben", auch dann, wenn ich die FK-Definition stck_id zu id in "stuecke" weglasse. OHNE FK-Definitionen lässt sich Tabelle "ausgaben" klaglos definieren.
(3) Bzgl. "inkompatible Datentypen":
die Definition von stck_id in "ausgaben" unterscheidet sich vom referenzierten PK in "stuecke" (=id) NUR durch AUTO_INCREMENT (was aber in der referenzierenden Tabelle nicht angegeben werden darf, bzw. durch den COMMENT.
Also nochmals - danke, aber vergessen Sie's! Ich finde den Grund schon noch heraus - nachdem ich mich mit relationalen DBs schon seit ca. 30 Jahren befasse (DB2, dann MySQL)...

 

[akretschmer]

nun denn... wenn ma nach 30 Jahren SQL bei MySQL landet ...

Wenn man bessere Systeme nutzt, bekommt man bei Fehlern wenigstens sinnvolle Fehlermeldungen:

postgres=# create table a (id int);
CREATE TABLE
postgres=# create table b(aid int references a);
ERROR:  there is no primary key for referenced table "a"
postgres=# drop table a;
DROP TABLE
postgres=# create table a (id text primary key);
CREATE TABLE
postgres=# create table b(aid int references a);
ERROR:  foreign key constraint "b_aid_fkey" cannot be implemented
DETAIL:  Key columns "aid" and "id" are of incompatible types: integer and text.
postgres=#

Kannst ja berichten, was es nun war - wenn Du es gefunden hast.

 

[hstoellinger]

Ist ja gut! Wäre interessant was Postgres in meinem Fall zurückmelden würde! Der geschilderte Fall wäre ja ganz einfach (auch OHNE speziellem Hinweis auf den inkcompatiblen Datentyp!) zu klären. Ich habe den Fall jetzt im englischen MySQL-Forum eingegeben. Sollte sich etwas daraus ergeben dann berichte ich natürlich davon. Sie könnten aber natürlich die Definitionen auch im Postgres eingeben. Vielleicht käme wirklich eine sinnvollere Fehlermeldung zurück -- oder auch gar keine, sollte es sich um einen Bug im MariaDB/MySQL handeln.

 

[akretschmer]

ich kann deine Fragmente aus #1 nicht 1:1 in PG machen, da passen Datentypen nicht, Syntax, Tabellen fehlen. Ich hab Dir gezeigt, daß PG sinnvolle Fehlermeldungen gibt, bei Fehlern, wie ich vermutet habe.

 

[hstoellinger]

Vergiss es!

 

[BerndB]

Vergiss es!

Ohne weutere Infos riecht das wirklich nach einem Type Fehler. Hast du schon mal alle typen auf signed / Unsigned überprüft.

 

[dabadepdu]

(1) Fehlermeldung:
ERROR 1005 (HY000): Can't create table `musikdb`.`ausgaben` (errno: 150 "Foreign key constraint is incorrectly formed
")

Ist das die komplette Meldung?

Nicht so allerdings bei der Definition derselben innerhalb "ausgaben", auch dann, wenn ich die FK-Definition stck_id zu id in "stuecke" weglasse. OHNE FK-Definitionen lässt sich Tabelle "ausgaben" klaglos definieren.

Vielleicht habe ich das falsch verstanden, aber das klingt danach, dass ein anderer Constraint der Übeltäter ist, nicht der auf stuecke.
Deswegen hatte ich geschrieben:

Man kann also nicht davon ausgehen, dass der Code so wie Du ihn hier darstellst auch bei Dir abläuft und den Fehler produziert

Andere nennen es "verheimlicht".

Der Spaß, einen Fehler zu finden, endet da, wo es Raten ist. Ich nutze kein mySQL, deswegen kann ich nicht sagen, ob es nun tatsächlich die vollständige Fehlermeldung ist - ich fürchte fast, ja-, aber so einem Phantom nachzurennen, damit verschwenden nur wenige gern ihre Zeit.
Du sitzt an den Quellen und hast es in der Hand.

Ich hoffe, Dir ist klar, was ich meine.