Mysql delete mit join schlägt fehl

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Hallo zusammen,
ich habe vereinfacht folgende Tabellen (Mysql 5.5 - Innodb):

verlauf
id int (primary key)

beleg
id int (primary key)

posten
verlauf_id int foreign key (id) references verlauf
beleg_id int foreign key (id) references beleg

verlauf und beleg sind also über posten verbunden und posten hat einen zusammengesetzten primary key aus zwei fremdschlüsseln.

Nun möchte ich z.B. Datensatz x aus beleg und alle damit zusammenhängende Sätze aus posten und verlauf löschen.

Wenn ich nun folgendes ausführe:

DELETE p,v,b FROM posten p
JOIN verlauf v ON p.verlauf_id = v.id
JOIN beleg b ON b.id = p.beleg_id
WHERE p.beleg_id = ‚7‘

Dann schlägt die Abfrage mit „#1451 - Cannot delete or update a parent row: a foreign key constraint fails (cystore.posten, CONSTRAINT posten_ibfk_2 FOREIGN KEY (beleg_id) REFERENCES beleg (id))“
fehl.

Wenn ich aber „WHERE p.beleg_id = ‚7‘“ weg lasse funktioniert es, nur dass dann halt alle Datensätze gelöscht werden.

Hat da einer eine Lösung oder Idee für mich?

Viele Grüße

Friendly

#2

Hallo,

von den gegebenen Informationen her, würde ich sagen, hier gehen wohl mehrere Dinge (Ansatz, Syntax) schief.

Gelöscht wird in dem Beispiel aus „Posten“ alles mit der Beleg_ID 7. Gleichzeitig soll aber aus „Beleg“ (und „Verlauf“) der referenzierte Datensatz gelöscht werden. Die in diesem Augenblick gelöschte ID in „Beleg“ (und „Verlauf“) könnte aber in „Posten“ noch durch andere Beleg_IDs referenziert werden, denen dann der Bezugspunkt fehlt und offenbar ist keine passende Fremdschlüssel-Constraint gesetzt, die eine adäquate Aktion auf die referenzierenden Tabellen anwendet. Gemeint ist z.B. „ON DELETE“ bzw. „ON UPDATE“, die man beim Referenzieren mit angeben sollte.

Siehe: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreig…

So long.

#3

Ich vermute mal, dass ohne die „WHERE“ Anweisung intern in einer anderen (korrekten) Reihenfolge gearbeitet wird, so dass die Abhängigkeitsbeziehung zwischen den Datensätzen nicht verletzt wird.

#4

Vielen Dank für deine Antwort,
habe das Problem gelöst indem ich den Fremdschlüsseln ‚ON DELETE CASCADE‘ angefügt habe.

Friendly

#5

Hallo,

das Problem dürfte in der Reference von posten.beleg_id auf beleg.id liegen.
Aus beleg darf nichts gelöscht werden was noch in posten enthalten ist.
Das spielt offenbar hingegen keine Rolle wenn man alles löschen will.

Gruß
Erhard

verlauf
id int (primary key)

beleg
id int (primary key)

posten
verlauf_id int foreign key (id) references verlauf
beleg_id int foreign key (id) references beleg

verlauf und beleg sind also über posten verbunden und posten
hat einen zusammengesetzten primary key aus zwei
fremdschlüsseln.

Nun möchte ich z.B. Datensatz x aus beleg und alle damit
zusammenhängende Sätze aus posten und verlauf löschen.

Wenn ich nun folgendes ausführe:

DELETE p,v,b FROM posten p
JOIN verlauf v ON p.verlauf_id = v.id
JOIN beleg b ON b.id = p.beleg_id
WHERE p.beleg_id = ‚7‘

Dann schlägt die Abfrage mit „#1451 - Cannot delete or update
a parent row: a foreign key constraint fails
(cystore.posten, CONSTRAINT posten_ibfk_2 FOREIGN KEY
(beleg_id) REFERENCES beleg (id))“
fehl.

Wenn ich aber „WHERE p.beleg_id = ‚7‘“ weg lasse funktioniert
es, nur dass dann halt alle Datensätze gelöscht werden.

Hat da einer eine Lösung oder Idee für mich?

Viele Grüße

Friendly

#6

Wenn ich nun folgendes ausführe:

DELETE p,v,b FROM posten p
JOIN verlauf v ON p.verlauf_id = v.id
JOIN beleg b ON b.id = p.beleg_id
WHERE p.beleg_id = ‚7‘

Dann schlägt die Abfrage mit „#1451 - Cannot delete or update
a parent row: a foreign key constraint fails
(cystore.posten, CONSTRAINT posten_ibfk_2 FOREIGN KEY
(beleg_id) REFERENCES beleg (id))“
fehl.

Wenn ich aber „WHERE p.beleg_id = ‚7‘“ weg lasse funktioniert
es, nur dass dann halt alle Datensätze gelöscht werden.

Meines Wissens wirkt DELETE immer nur auf genau
eine Tabelle, d.h. man kann nicht gleichzeitig Daten in
mehreren Tabellen löschen. Ausnahme: Die referentiellen
Constraints wie posten_ibfk_2 wurden mit der Option
„on delete cascade“ erzeugt. Diese Option kenne ich aber
nur bei kommerziellen DBs wie Oracle. Solch ein delete
cascade würde im Beispiel aber auch nur Einträge in
zwei der drei Tabellen löschen. Für Löschoperationen in
der dritten Tabelle braucht man auf jeden Fall einen
weiteren SQL-Befehl.

Der „sichere“ Weg:

  1. Zuerst diejenigen Datensätze löschen, die auf Inhalte einer
    anderen Tabelle verweisen, also im Beispiel:

delete from posten p where p.beleg_id = ‚7‘

  1. falls gewünscht, den Beleg löschen:

delete from beleg b where b.id = ‚7‘

  1. falls gewünscht, alle Datensätze aus Verlauf löschen, für
    die in Posten jetzt kein Eintrag mehr existiert:

delete from verlauf v where v.id not in
(select p.verlauf_id from posten p)

Achtung: Dieser Befehl löscht auch alle anderen Einträge in
Verlauf, die nicht in Posten auftauchen.

Mit freundlichen Grüßen

Michael