[SQL] Constraint alle gekoppelde tabellen aanpassen

[Icelus]

Als voorbeeld twee tabellen, A en B. Er kunnen nul of meer verwijzingen van tabel A naar B bestaan via een koppeltabel. Als een rij in A of B wordt gewist wordt ook de koppeltabel aangepast. Is het mogelijk om als een rij uit A wordt gewist de ‘bijbehorende’ rij uit B te verwijderen? Of moet dit in de code worden opgelost.

SQL:

CREATE TABLE a (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(id)
) TYPE=InnoDB;

CREATE TABLE b (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(id)
) TYPE=InnoDB;

CREATE TABLE a_has_b (
  a_id INTEGER UNSIGNED NOT NULL,
  b_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(a_id, b_id),
  FOREIGN KEY(a_id)
    REFERENCES a(id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(b_id)
    REFERENCES b(id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) TYPE=InnoDB;

[P_de_B]

Referentie van a, naar a_has_b naar b met allen de ON DELETE CASCADE optie?

[Verwijderd]

er bestaat zoiets als ON DELETE CASCADE, maar ik weet niet of die enkel uit de koppeltabel gaat deleten of dit ook helemaal doortrekt tot tabel B.

[RobIII]

Verwijderd schreef op dinsdag 21 oktober 2008 @ 11:51:
er bestaat zoiets als ON DELETE CASCADE, maar ik weet niet of die enkel uit de koppeltabel gaat deleten of dit ook helemaal doortrekt tot tabel B.

Een cascade zal, in principe en bij een beetje degelijk RDBMS, zorgen dat alle constraints enforced blijven en dus de referentiële integriteit gewaarborgd blijft; als je tabel A hebt, tabel B en koppeltabel K met daarin K.A_FK en K.B_FK dan is er geen reden om verder dan de koppeltabel te kijken en daar in te verwijderen als er uit tabel A een record verwijderd wordt. Met het verwijderen van een koppelrecord is het dan dus klaar want B bevat geen referenties naar A. Heeft B echter FK's in A dan zal dat wél effect (moeten) hebben en dus zal de cascade ook aan tabel B komen.

Het gevaar van cascade deletes is dat je soms niet goed het overzicht hebt wat er allemaal verwijderd kan/gaat worden. Heb ik een tabel met "aanhef" met daarin "dhr." en "mevr." en ik verwijder "dhr." dan verdwijnen al mijn mannelijke contactpersonen (wat al shit an sich is ), maar wegens die cascade bestaat ook het gevaar dat alle facturen die aan heren gekoppeld zijn verdwijnen, omdat de contactpersonen van die facturen verdwijnen omdat de aanhef verdwijnt. Oei En dat is nog maar met 3 tabelletjes, moet je nagaan wat voor effect dat heeft bij -tig tabellen; je ziet zo door de bomen het bos niet meer.

Persoonlijk raad ik cascades dan ook meestal af. De topictitel zegt [DB], maar het gaat zo te zien om MySQL (zal het even aanpassen, vermeld dat voortaan even duidelijk a.u.b.), en hoe het bij MySQL precies geregeld is durf ik niet met zekerheid te zeggen; sowieso zal het alleen werken met InnoDB want die kent überhaupt relaties i.t.t. MyISAM. De manual heeft het echter alleen over parent en child relaties; ik heb geen idee hoe 'ver' zo'n cascade op MySQL kan doorwerken, maar ik neem aan dat het zoals hierboven beschreven dus gewoon "diep" doorwerkt omdat elke relatie altijd een 'parent' en 'child' heeft. Ik zie echter wel daar dat triggers door een cascade niet afgevuurd worden.

Edit: Ah, ik lees ook:

quote: http://dev.mysql.com/doc/...eign-key-constraints.html

Cascading operations may not be nested more than 15 levels deep

Afhankelijk van hoe en waar je je cascades zet krijg je dus verschillende mogelijkheden waarop iets (cascade) verwijderd wordt. Echter zal het verwijderen van een FK record record met FK niet leiden tot het verwijderen uit B zoals TS dat graag zou willen.

[ Voor 34% gewijzigd door RobIII op 21-10-2008 12:25 ]

[Icelus]

RobIII schreef op dinsdag 21 oktober 2008 @ 12:02:
Met het verwijderen van een koppelrecord is het dan dus klaar want B bevat geen referenties naar A. Heeft B echter FK's in A dan zal dat wél effect (moeten) hebben en dus zal de cascade ook aan tabel B komen.

Ja, maar dat is nu niet het geval.
Na de volgende code zit er in B nog een (wees)rij:

SQL:

INSERT INTO a (id) VALUES (1);
INSERT INTO b (id) VALUES (1);
INSERT INTO a_has_b (a_id,b_id) VALUES (1,1);
DELETE FROM a WHERE id=1;

Het gevaar van cascade deletes is dat je soms niet goed het overzicht hebt wat er allemaal verwijderd kan/gaat worden. Heb ik een tabel met "aanhef" met daarin "dhr." en "mevr." en ik verwijder "dhr." dan verdwijnen al mijn mannelijke contactpersonen (wat al shit an sich is ), maar wegens die cascade bestaat ook het gevaar dat alle facturen die aan heren gekoppeld zijn verdwijnen, omdat de contactpersonen van die facturen verdwijnen omdat de aanhef verdwijnt. Oei En dat is nog maar met 3 tabelletjes, moet je nagaan wat voor effect dat heeft bij -tig tabellen; je ziet zo door de bomen het bos niet meer.

Klopt, maar op dat soort relaties zou een RESTRICT of NO ACTION gezet kunnen worden.

De topictitel zegt [DB], maar het gaat zo te zien om MySQL (zal het even aanpassen, vermeld dat voortaan even duidelijk a.u.b.)

Expres gedaan omdat ik dacht dat het een universele vraag is.

[RobIII]

Icelus schreef op dinsdag 21 oktober 2008 @ 12:18:
[...]
Ja, maar dat is nu niet het geval.
Na de volgende code zit er in B nog een (wees)rij:

SQL:

INSERT INTO a (id) VALUES (1);
INSERT INTO b (id) VALUES (1);
INSERT INTO a_has_b (a_id,b_id) VALUES (1,1);
DELETE FROM a WHERE id=1;

Klopt, ik leg dat ook uit:

RobIII schreef op dinsdag 21 oktober 2008 @ 12:02:
Met het verwijderen van een koppelrecord is het dan dus klaar want B bevat geen referenties naar A.
En dus zal je cascade stoppen bij de koppeltabel.
...
...
Echter zal het verwijderen van een FK record record met FK niet leiden tot het verwijderen uit B zoals TS dat graag zou willen.

Icelus schreef op dinsdag 21 oktober 2008 @ 12:18:
Klopt, maar op dat soort relaties zou een RESTRICT of NO ACTION gezet kunnen worden.

True, maar dat komt het overzicht ook niet ten goede

Icelus schreef op dinsdag 21 oktober 2008 @ 12:18:
Expres gedaan omdat ik dacht dat het een universele vraag is.

Dan lijkt [SQL] me beter passen dan [DB]

[ Voor 40% gewijzigd door RobIII op 21-10-2008 12:24 ]

[Patriot]

RobIII schreef op dinsdag 21 oktober 2008 @ 12:02:
[...]

Het gevaar van cascade deletes is dat je soms niet goed het overzicht hebt wat er allemaal verwijderd kan/gaat worden. Heb ik een tabel met "aanhef" met daarin "dhr." en "mevr." en ik verwijder "dhr." dan verdwijnen al mijn mannelijke contactpersonen (wat al shit an sich is ), maar wegens die cascade bestaat ook het gevaar dat alle facturen die aan heren gekoppeld zijn verdwijnen, omdat de contactpersonen van die facturen verdwijnen omdat de aanhef verdwijnt. Oei En dat is nog maar met 3 tabelletjes, moet je nagaan wat voor effect dat heeft bij -tig tabellen; je ziet zo door de bomen het bos niet meer.

Hmm, ik dacht dat constraints maar één kant op werkten?

[RobIII]

Patriot schreef op dinsdag 21 oktober 2008 @ 12:24:
[...]


Hmm, ik dacht dat constraints maar één kant op werkten?

Ja, ze cascaden (waterval effect). Maar als een factuur aan een klant hangt die verdwijnt omdat de aanhef voor de klant verdwijnt is dat toch 1 kant op? Aanhef -> Klant -> Factuur.

[Patriot]

RobIII schreef op dinsdag 21 oktober 2008 @ 12:26:
[...]

Ja, ze cascaden (waterval effect). Maar als een factuur aan een klant hangt die verdwijnt omdat de aanhef voor de klant verdwijnt is dat toch 1 kant op? Aanhef -> Klant -> Factuur.

Ja ok, maar dan heb je toch een domme constraint aangelegd? Dat is namelijk een constraint die er volgens mij niet hoort te zijn.. Een constraint leg je meestal aan op zo'n manier dat het ene record altijd alleen maar gekoppeld is aan het andere record, en er één record overduidelijk afhankelijk is van de andere.

[RobIII]

Patriot schreef op dinsdag 21 oktober 2008 @ 12:34:
Dat is namelijk een constraint die er volgens mij niet hoort te zijn.. Een constraint leg je meestal aan op zo'n manier dat het ene record altijd alleen maar gekoppeld is aan het andere record, en er één record overduidelijk afhankelijk is van de andere.

Dat is toch ook zo Een factuur is overduidelijk afhankelijk van een klant en een klant is overduidelijk afhankelijk van een aanhef (of aanhef zo'n goed voorbeeld is in deze is een tweede, maar het zou ook een accountmanager kunnen zijn of whatever).

Verder horen tabellen die FK's bevatten gewoon altijd* constraints te bevatten naar hun PK tegenhangers.
_{* Met een enkele uitzondering daargelaten misschien...}

[ Voor 12% gewijzigd door RobIII op 21-10-2008 12:50 ]

[Patriot]

RobIII schreef op dinsdag 21 oktober 2008 @ 12:42:
[...]

Dat is toch ook zo Een factuur is overduidelijk afhankelijk van een klant en een klant is overduidelijk afhankelijk van een aanhef (of aanhef zo'n goed voorbeeld is in deze is een tweede, maar het zou ook een accountmanager kunnen zijn of whatever).

Mja, ik zag de klant niet als overduidelijk afhankelijk van de aanhef hoor. Je hebt een tabel met daarin verschillende aanhef (heeft dat woord überhaupt een meervoudsvorm? ) en met een id geef je bij de gebruiker aan welke aanhef er gebruikt moet worden. Als de aanhef verdwijnt moet de klant dan toch niet opeens óók verdwijnen!? En waar je precies met die accountmanager heen wilt is mij ook niet echt duidelijk.

[RobIII]

Patriot schreef op dinsdag 21 oktober 2008 @ 12:49:
Mja, ik zag de klant niet als overduidelijk afhankelijk van de aanhef hoor. Je hebt een tabel met daarin verschillende aanhef (heeft dat woord überhaupt een meervoudsvorm? ) en met een id geef je bij de gebruiker aan welke aanhef er gebruikt moet worden. Als de aanhef verdwijnt moet de klant dan toch niet opeens óók verdwijnen!?

Het was dan ook een voorbeeld om de werking van een cascade te verduidelijken, niet over wat zinnig is en wat niet Als die aanhef verdwijnt heb ik dus orphan FK's in mijn klantentabel, dus er zal toch iets moeten gebeuren om dat op te lossen. Een cascade delete is dus inderdaad lomp (hence: voorbeeld), maar er zal wél actie ondernomen moeten worden om de orphans te voorkomen. Of dat dan op NULL zetten is, of wijzigen naar "Meneertje koekepeertje", of heel de delete op een aanhef niet toestaan is wurst. Doe je dat niet dan is het concept referentiële integriteit je niet duidelijk.

Patriot schreef op dinsdag 21 oktober 2008 @ 12:49:
En waar je precies met die accountmanager heen wilt is mij ook niet echt duidelijk.

Accountmanager -> Klant -> Factuur. Praat ik echt zo moeilijk

[ Voor 12% gewijzigd door RobIII op 21-10-2008 12:58 ]

[Patriot]

RobIII schreef op dinsdag 21 oktober 2008 @ 12:52:
[...]

Het was dan ook een voorbeeld... En als die aanhef verdwijnt heb ik dus orphan FK's in mijn klantentabel, dus er zal iets moeten gebeuren om dat op te lossen. Een cascade delete is dus inderdaad lomp (hence: voorbeeld), maar er zal wel actie ondernomen moeten worden om de orphans te voorkomen.

Ok, ok, ik zie waar je heen wilt. Ik snap nu ook wel waarom je het als voorbeeld stelde, maar mijn punt was dat cascade deletes er niet per sé onduidelijker op hoeven te worden. Als je iets op de verkeerde manier gebruikt, kun je in mijn ogen moeilijk datgene wat je gebruikt het slechte resultaat verwijten.

[...]

Accountmanager -> Klant -> Factuur. Praat ik echt zo moeilijk

Mja, ik snap niet helemaal wat je bedoelt met accountmanager, wat moet ik daar precies onder verstaan? Het is absoluut niet zo dat je moeilijk praat, voor mij is de functie/betekenis van "een accountmanager" gewoon niet zo vanzelfsprekend als voor jou

[RobIII]

Patriot schreef op dinsdag 21 oktober 2008 @ 12:58:
Ok, ok, ik zie waar je heen wilt. Ik snap nu ook wel waarom je het als voorbeeld stelde, maar mijn punt was dat cascade deletes er niet per sé onduidelijker op hoeven te worden. Als je iets op de verkeerde manier gebruikt, kun je in mijn ogen moeilijk datgene wat je gebruikt het slechte resultaat verwijten.

Zie mijn sig Ik vind cascade deletes echter gewoon dusdanig gevaarlijk dat ze (IMHO) rustig verboden mogen worden. Again; bij 3 tabelletjes is het prima te overzien en beheren, maar als je op 50+ of, God forbid, 500+ tabellen zit wordt het andere koek als het stikt van de cascades overal.

Patriot schreef op dinsdag 21 oktober 2008 @ 12:58:
Mja, ik snap niet helemaal wat je bedoelt met accountmanager, wat moet ik daar precies onder verstaan? Het is absoluut niet zo dat je moeilijk praat, voor mij is de functie/betekenis van "een accountmanager" gewoon niet zo vanzelfsprekend als voor jou

Wat de functie van een accountmanager is, is mij ook nooit duidelijk geworden Ik heb het nu gewoon even over een 'medewerker' die die bepaalde klant beheert. Boeie. Als die zou vertrekken en dus (voor het voorbeeld) deleted zou worden ben je al die klanten kwijt. Nu zul je in de praktijk nooit dat record deleten maar op 'inactief' zetten of whatever en de klanten assignen aan een andere manager, maar again: voor-beeld.

[ Voor 21% gewijzigd door RobIII op 21-10-2008 13:04 ]

[Patriot]

RobIII schreef op dinsdag 21 oktober 2008 @ 13:01:
[...]

Zie mijn sig Ik vind cascade deletes echter gewoon dusdanig gevaarlijk dat ze (IMHO) rustig verboden mogen worden. Again; bij 3 tabelletjes is het prima te overzien en beheren, maar als je op 50+ of, God forbid, 500+ tabellen zit wordt het andere koek als het stikt van de cascades overal.

Ik ben het niet met je eens dat het verboden moet worden, maar ik snap wel waarom je dat vind. Ik vraag me af of de problemen die je beschrijft in de praktijk vaak voorkomen trouwens, want constraints zijn over het algemeen dingen die de gemiddelde beginner niet direct voor zijn kiezen krijgt. De mensen waarvan ik weet dat ze het gebruiken (met daarbij de kanttekening dat dat natuurlijk niet representatief is voor de grote groep) schat ik over het algemeen zo in dat ze het overzicht nog wel kunnen behouden. Dat zegt natuurlijk niets over de mensen die in de toekomst aan hun projecten moeten werken, die kunnen er nog wel eens een kluif aan hebben.

[...]

Wat de functie van een accountmanager is, is mij ook nooit duidelijk geworden Ik heb het nu gewoon even over een 'medewerker' die die bepaalde klant beheert. Boeie. Als die zou vertrekken en dus (voor het voorbeeld) deleted zou worden ben je al die klanten kwijt. Nu zul je in de praktijk nooit dat record deleten maar op 'inactief' zetten of whatever en de klanten assignen aan een andere manager, maar again: voor-beeld.

Ah zo, nee ok, dan snap ik je wel hoor. Ik weet ook wel dat het maar een voorbeeld was, maar wilde hem toch graag snappen

[RobIII]

Patriot schreef op dinsdag 21 oktober 2008 @ 13:14:
Ik ben het niet met je eens dat het verboden moet worden

Verbieden is misschien wat cru gesteld, maar je moet het gewoon ont-zet-tend voorzichtig toepassen, als je het al toepast. En documenteren alsof je leven er van af hangt

Patriot schreef op dinsdag 21 oktober 2008 @ 13:14:
Ik vraag me af of de problemen die je beschrijft in de praktijk vaak voorkomen trouwens, want constraints zijn over het algemeen dingen die de gemiddelde beginner niet direct voor zijn kiezen krijgt.

Gelukkig zijn er meer groepen dan beginners Daarbij vind ik dat ook of misschien wel juist beginners met constraints moeten leren werken. En, zover ik me kan herinneren, heb ik dat 'vroegah' op school toch ook geleerd in het eerste jaar, naast/met normaliseren. Wat is het nut van een RDBMS zonder die dingen? Gebruik dan flat-files

Patriot schreef op dinsdag 21 oktober 2008 @ 13:14:
De mensen waarvan ik weet dat ze het gebruiken (met daarbij de kanttekening dat dat natuurlijk niet representatief is voor de grote groep) schat ik over het algemeen zo in dat ze het overzicht nog wel kunnen behouden.

Als je in je uppie op een projectje zit met 8 tabelletjes misschien wel, maar als je met tig man aan een groot project werkt met tig tabellen en de documentie is er niet naar heb je dus andere koek. Een foutje is dan zo gemaakt.

Patriot schreef op dinsdag 21 oktober 2008 @ 13:14:
Dat zegt natuurlijk niets over de mensen die in de toekomst aan hun projecten moeten werken, die kunnen er nog wel eens een kluif aan hebben.

Ook nog eens ja.

[ Voor 4% gewijzigd door RobIII op 21-10-2008 13:20 ]

[Icelus]

P_de_B schreef op dinsdag 21 oktober 2008 @ 11:50:
Referentie van a, naar a_has_b naar b met allen de ON DELETE CASCADE optie?

Dat wordt lastig. Om een rij in A toe te voegen moet er al een rij in K bestaan, de rij in K kan pas worden toegevoegd wanneer A bestaat.

RobIII schreef op dinsdag 21 oktober 2008 @ 12:02:
[...]
als je tabel A hebt, tabel B en koppeltabel K met daarin K.A_FK en K.B_FK dan is er geen reden om verder dan de koppeltabel te kijken en daar in te verwijderen als er uit tabel A een record verwijderd wordt.

Ja, dat is logisch.

Ik heb o.a. een gebruikers- en adressentabel. De adressen zitten nu alleen aan gebruikers gekoppeld maar dat kan uiteraard nog veranderen in de toekomst.
Wanneer een gebruiker wordt gewist moeten eventuele adressen en koppelingen worden gewist. Als een adres wordt gewist moet de koppeltabel worden aangepast maar moet de gebruiker blijven bestaan.

Misschien in een stored procedure nagaan of het adres nog gekoppeld is en dan verwijderen?

[MSalters]

't Is gewoon dubieus om klanten te verwijderen. Zet een vlaggetje dat ze niet meer actief zijn, als je ze al zou moeten inactiveren. Maar normaal gesproken is een klant eenieder met wie je zaken doet of hebt gedaan.

Een database kan in het algemeen twee verschillende dingen opslaan: de huidige toestand, of een geschiedenis. In het eerste geval kun je altijd dingen verwijderen, als die niet meer met de huidige situatie overeenkomen. De aanhef "Dhr" verwijderen? Kan, als je besluit om met onmiddelijke ingang niets meer voor mannen te doen. Zolang die beslissing juist is, is de cascaded delete correct.

[P_de_B]

Je moet gewoon goed nadenken wanneer je cascading deletes gebruikt. Als je een tabel met factuurkoppen en een tabel met factuurregels hebt kan het bijvoorbeeld goed van pas komen. Als je een factuur verwijdert wil je ook alle detailregels vewijderen. Voor tabellen als klant of titulatuur zou ik het niet gebruiken.

[djlinsen]

In de applicatie waar ik aan werk zaten de cascading delete krengen ook, een erfenis van pak en beet 8 jaar terug toen de applicatie nog in de kinderschoenen stond. En dan ineens krijg je een telefoontje van een klant die de helft van zijn gegevens mist, oeps.... We hebben ze er direct uit gegooit en het een en ander viel gelukkig met wat backups nog recht te breien.

[mocean]

In de database opzet van de topicstarter, kunnen meerdere records uit tabel A gekoppeld zijn aan 1 record in tabel B. Dus het verwijderen uit tabel B op basis van 1 record uit tabel A lijkt me onlogisch.

Als een record uit tabel B altijd maar aan 1 record uit tabel A zit gekoppeld, dan heb je de hele koppeltabel niet nodig en stop je de FK van A in tabel B (En dan is de ON DELETE CASCADE wel weer logisc).

[Icelus]

mocean schreef op woensdag 22 oktober 2008 @ 15:55:
In de database opzet van de topicstarter, kunnen meerdere records uit tabel A gekoppeld zijn aan 1 record in tabel B. Dus het verwijderen uit tabel B op basis van 1 record uit tabel A lijkt me onlogisch.

Klopt, maar zo wilde ik 'm niet gebruiken.
Iedere record uit B wijst eenmaal naar een record uit A (er kunnen meerdere B-records naar eenzelfde A-record wijzen).

Als een record uit tabel B altijd maar aan 1 record uit tabel A zit gekoppeld, dan heb je de hele koppeltabel niet nodig en stop je de FK van A in tabel B (En dan is de ON DELETE CASCADE wel weer logisc).

Ja, maar ik wil tabel B in de toekomst misschien ook aan een tabel C koppelen, vandaar de koppeltabel.