[SQL Server / database] Cycle in relaties

[licensed]

De volende tabellen en relaties heb ik gemaakt:


Hier zit ondermeer de volgende cycle (Persoon, Docent, Aanbod, Keuze, Leerling) in. Daar is op zich niks mis mee, maar zodra ik alle relaties "ON DELETE CASCADE" maak krijg ik de fout:

- Unable to create relationship 'FK_Keuze_Aanbod'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_Keuze_Aanbod' on table 'Keuze' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.

Hoe kan dit? Als je de tabellen en relaties ziet als een gerichte graaf is dit toch geen cycle? Hoe los ik dit op?

[shades]

Ben zelf niet zo'n voorstander van delete cascades omdat je dan soms meer kwijt raakt dan je zou willen. M.i. kan je beter een trigger gebruiken.

Om op de fout melding terug te komen.
Kennelijk heb je op iedere releatie het vinkje cascade delete aan staan en daar gaat het mis. Je moet de chain zeg maar stoppen/verbreken daar waar de cascading dient op te houden. Als je cascading echt aanhoud dan de integriteit in het geding komen.

[ Voor 55% gewijzigd door shades op 22-02-2005 20:32 ]

[licensed]

Het is zo dat geen van de instanties kan bestaan zonder de ander. Geen persoon betekent geen docent. Geen aanbod is geen keuze. Vandaar dat ik overal cascade delete gebruik. Kan ik één van die cascading relaties vervangen door een trigger die vervolgens delete?

[Jaspertje]

Dat is een fout in SQL Server... Daar kan je niks aan doen. Een cascading delete gebruiken is alleen maar goed, aangezien je daarmee je database correct houdt...

Ik zal ff zoeken naar het topic waar ik er ook achter kwam
voila:
[rml][ SQL Server/SQL]Cascade Delete Foreign Keys[/rml]

[ Voor 16% gewijzigd door Jaspertje op 22-02-2005 21:15 ]

[whoami]

Jaspertje schreef op dinsdag 22 februari 2005 @ 21:14:
Dat is een fout in SQL Server... Daar kan je niks aan doen. Een cascading delete gebruiken is alleen maar goed, aangezien je daarmee je database correct houdt...

Ik zal ff zoeken naar het topic waar ik er ook achter kwam
voila:
[rml][ SQL Server/SQL]Cascade Delete Foreign Keys[/rml]

Inderdaad, ik heb het ook al eens tegengekomen.
Dat je cascaded deletes beter niet gebruikt, en daarvoor beter triggers schrijft is onzin. Cascaded deletes zijn sinds sql server2000 ondersteunt, en als je kan, gebruik je dan ook beter die ipv triggers.

[shades]

Jaspertje schreef op dinsdag 22 februari 2005 @ 21:14:
Dat is een fout in SQL Server... Daar kan je niks aan doen. Een cascading delete gebruiken is alleen maar goed, aangezien je daarmee je database correct houdt...

Ik zal ff zoeken naar het topic waar ik er ook achter kwam
voila:
[rml][ SQL Server/SQL]Cascade Delete Foreign Keys[/rml]

Met de juiste constrains doe je dat al bewerkstelligen. Als je meer controlle over je cascading deletes wilt hebben kan je kiezen voor een trigger FOR DELETE. of zelf in een stored proc kiezen om eerst de 'child' rows weg te gooien en dan de 'parent' row.
Om nou te zeggen dat het een fout is vind ik wel wat makkelijk. Je kan veel dingen regelen met sqlserver maar je moet je wel aan de regels houden.

[ Voor 4% gewijzigd door shades op 22-02-2005 22:44 ]

[licensed]

Jaspertje schreef op dinsdag 22 februari 2005 @ 21:14:
Dat is een fout in SQL Server... Daar kan je niks aan doen. Een cascading delete gebruiken is alleen maar goed, aangezien je daarmee je database correct houdt...

Ik zal ff zoeken naar het topic waar ik er ook achter kwam
voila:
[rml][ SQL Server/SQL]Cascade Delete Foreign Keys[/rml]

Deze heb ik ook gelezen, maar ik wist niet dat dit hetzelfde probleem was. Ik heb nog nooit een trigger geschreven dus daar moet ik me maar eens in gaan verdiepen.

[Verwijderd]

Jaspertje schreef op dinsdag 22 februari 2005 @ 21:14:
Een cascading delete gebruiken is alleen maar goed, aangezien je daarmee je database correct houdt...

"is alleen maar goed" gaat me een tikje te ver...
Wat heb je aan een correcte database met corrupte data?

Stel, je hebt een artikel-tabel en een order-tabel. Als je een artikel verwijdert, en 't aan cascaded delete overlaat, zullen de records met dat artikel ook uit je order-tabel worden verwijderd. Een hoop klanten die nog op hun (nu niet meer bestaande) bestelling zitten te wachten zullen daar niet zo blij mee zijn, en de boekhouding ook al niet, omdat factuurgegevens "opeens" wijzigen...

Oftewel, cascaded deletes kunnen soms best handig zijn, maar vaak is er meer intelligentie nodig. Dat kan client side, of in een trigger.

[shades]

Verwijderd schreef op dinsdag 22 februari 2005 @ 22:59:
[...]

Oftewel, cascaded deletes kunnen soms best handig zijn, maar vaak is er meer intelligentie nodig. Dat kan client side, of in een trigger.

Dat is precies waar ik op doel.

[EfBe]

licensed schreef op dinsdag 22 februari 2005 @ 16:11:
De volende tabellen en relaties heb ik gemaakt:
[afbeelding]

Hier zit ondermeer de volgende cycle (Persoon, Docent, Aanbod, Keuze, Leerling) in. Daar is op zich niks mis mee, maar zodra ik alle relaties "ON DELETE CASCADE" maak krijg ik de fout:
Hoe kan dit? Als je de tabellen en relaties ziet als een gerichte graaf is dit toch geen cycle? Hoe los ik dit op?

De error praat ook over multiple cascade paths. Die heb je wel, via docent persoon en leerling persoon. Sowieso snap ik je model acties mbt persoon niet echt. Qua entity voegt die persoon niets toe. want hij heeft geen relaties verder, dus kun je die attributes gewoon migreren met docent en leerling. Opgelost.

[ Voor 16% gewijzigd door EfBe op 22-02-2005 23:23 ]

[licensed]

EfBe schreef op dinsdag 22 februari 2005 @ 23:22:
[...]

De error praat ook over multiple cascade paths. Die heb je wel, via docent persoon en leerling persoon. Sowieso snap ik je model acties mbt persoon niet echt. Qua entity voegt die persoon niets toe. want hij heeft geen relaties verder, dus kun je die attributes gewoon migreren met docent en leerling. Opgelost.

Dat is juist wat je niet wilt. Je wilt geen gegevens dubbel in je database. Je zou dan 3 tabellen krijgen, Admin, Docent en Leerling. In principe kan iemand elk van deze rollen hebben waardoor je van diegene 3 keer zijn naam etc. opslaat.

[EfBe]

licensed schreef op woensdag 23 februari 2005 @ 08:36:
[...]

Dat is juist wat je niet wilt. Je wilt geen gegevens dubbel in je database. Je zou dan 3 tabellen krijgen, Admin, Docent en Leerling. In principe kan iemand elk van deze rollen hebben waardoor je van diegene 3 keer zijn naam etc. opslaat.

Ok, in dat geval zou ik het inderdaad zo doen, dat was niet duidelijk uit je plaatje.

Een attribute in 2 entities kan wel dezelfde waarde hebben in alle entity instances maar dat betekent nog niet dat het dezelfde semantische waarde heeft, waardoor je niet kunt zeggen: ik normaliseer die column weg zodat ik minder redundante info heb.

Je probleem is echter wel degelijk die docent/persoon constructie:
Wanneer je persoon verwijdert, wordt cascaded ook docent verwijderd. Daarna dus leerling, maar leerling wordt ook verwijderd wanneer je persoon verwijderd. multiple paths.

[licensed]

EfBe schreef op woensdag 23 februari 2005 @ 09:00:
[...]

Ok, in dat geval zou ik het inderdaad zo doen, dat was niet duidelijk uit je plaatje.

Een attribute in 2 entities kan wel dezelfde waarde hebben in alle entity instances maar dat betekent nog niet dat het dezelfde semantische waarde heeft, waardoor je niet kunt zeggen: ik normaliseer die column weg zodat ik minder redundante info heb.

Je probleem is echter wel degelijk die docent/persoon constructie:
Wanneer je persoon verwijdert, wordt cascaded ook docent verwijderd. Daarna dus leerling, maar leerling wordt ook verwijderd wanneer je persoon verwijderd. multiple paths.

Nee, de relatie Docent - Leerling is niet CASCADE. Het gaat om het pad (Persoon, Docent, Aanbod, Keuze, Leerling).
Als je een persoon verwijderd en die is docent dan verwijder je die row (is een 1 op 1 relatie) ook. Als je een docent verwijderd moeten de vakken die hij geeft ook verwijderd worden. Als die vakken worden verwijderd zijn er ook geen leerlingen die dat vak kunnen volgen dus moeten die keuzes ook verwijderd. Als je een leerling verwijderd moeten zijn gemaakte keuzes ook verwijderd worden. Die laatste is dus "tegen de draad in", maar dit geeft toch problemen.