MySQL trigger: update same table after insert or update

[umask]

Probleemstelling
Ik heb de volgende tabellen:

• products
• credits
• tmp^{*edit: kan genegeerd worden (werd alleen medegedeeld, omdat de tabel wel aanwezig is)}

De kolommen ‘cID’ en ‘cName’ zijn zowel te vinden in ‘products’ als in ‘credits’. Wat ik wil bereiken is het volgende: na het aanmaken van een record, dus als ik een product toevoeg (INSERT via C#), dan wordt cName in products niet automatisch ingevuld. Daarom wil ik met een trigger ervoor zorgen dat cName automatisch wordt ingevuld bij het toevoegen van een record of dat de tabel van 'products' ook wordt geüpdatet als ik een UPDATE uitvoer via een MySQL Query, dus niet alleen bij een INSERT (daar heb ik dan zowel een UPDATE als een INSERT trigger voor nodig)

Database structuur: products

Naam	Type	Collatie	Leeg	Standaardwaarde
pID (Primary Key)	int(11)	N/A	Nee	Geen
cID (Index)	int(11)	N/A	Nee	Geen
cName	varchar(20)	latin1_swedish_ci	Ja	NULL
pName	varchar(50)	latin1_swedish_ci	Ja	NULL

Database structuur: credits

Naam	Type	Collatie	Leeg	Standaardwaarde
cID	int(11)	N/A	Nee	Geen
cName	varchar(20)	latin1_swedish_ci	Nee	Geen

Database structuur: tmp

Naam	Type	Collatie	Leeg	Standaardwaarde
pID	int(11)	N/A	Nee	Geen
cID	int(11)	N/A	Nee	Geen
cName	varchar(20)	latin1_swedish_ci	Ja	NULL

Wat gebruik ik?
Ik gebruik een wamp server waarin ik de triggers aanmaak voor de verschillende tabellen en de records maak ik aan in een (C-sharp) WPF applicatie.

Versies

• phpMyAdmin: 4.7.9
• PHP: 5.6.35
• MySQL: 5.7.21
• VisualStudio: 2017

De foutmelding die ik dan meestal krijg, is:
Can't update 'products' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Wat heb ik al uitgeprobeerd?

Nr.	Definitie	Trigger van tabel	MySQL Query
1	AFTER/BEFORE UPDATE of INSERT	products	UPDATE Products INNER JOIN credits ON Products.cID = Credits.cID SET Products.cName = Credits.cName WHERE Products.cID = Credits.cID;
2	BEFORE INSERT	products	UPDATE Products INNER JOIN Credits ON Products.cID = Credits.cID SET NEW.cName = Credits.cName WHERE Products.cID = Credits.cID;
3	AFTER INSERT	products	SET Products.cName = (SELECT Credits.cName FROM credits) WHERE Products.cID = Credits.cID;
4	AFTER/BEFORE UPDATE of INSERT	products	BEGIN CREATE Table tmp (pID int(11),cID int(11),cName nvarchar(20)); INSERT INTO tmp (products.cName) SELECT tmp.cName FROM tmp INNER JOIN products ON tmp.pID = products.pID WHERE tmp.pID = products.pID; DROP TABLE tmp; END
5	AFTER/BEFORE INSERT	products	UPDATE products AS pd INNER JOIN credits AS cg ON pd.cID = cg.cID SET pd.cName = cg.cName WHERE pd.cName = cg.cName;
6	AFTER/BEFORE INSERT	products	UPDATE products AS pd INNER JOIN credits AS cg ON pd.cID = cg.cID SET NEW.cName = cg.cName WHERE pd.cName = cg.cName;
7	AFTER/BEFORE INSERT	products	CREATE TABLE tmp (pID int(11), cName varchar(22)) SELECT pID FROM products; INSERT INTO products (cName) SELECT pID, name FROM tmp; DROP TABLE IF EXISTS tmp;

Ik heb geprobeerd om ook de waardes in een andere tabel te zetten, waarnaar ik vanuit de andere tabel de waardes weer heb geprobeerd terug in te voeren, maar ook dat is niet gelukt:

Nr.	Definitie	Trigger van tabel	MySQL Query
8	AFTER INSERT	products	BEGIN INSERT INTO tmp (tmp.pID, tmp.cID, tmp.cName) VALUES (NEW.pID, NEW.cID, NEW.cName); UPDATE tmp INNER JOIN Credits ON tmp.cID = Credits.cID SET tmp.cName = Credits.cName WHERE tmp.cID = Credits.cID; END
9	AFTER INSERT	tmp	UPDATE Products INNER JOIN tmp ON Products.cID = tmp.cID SET Products.cName = tmp.cName WHERE Products.cID = tmp.cID;
10	AFTER INSERT	tmp	INSERT INTO products (cName) SELECT cName FROM tmp;
11	BEFORE INSERT	tmp	INSERT INTO products (cName); SELECT NEW.cName FROM tmp;
12	AFTER/BEFORE INSERT	tmp	INSERT INTO products (products.cName) SELECT tmp.cName FROM tmp INNER JOIN products ON products.pID = tmp.pID WHERE products.pID = tmp.pID;
13	AFTER/BEFORE INSERT	tmp	INSERT INTO products (products.cName) SELECT tmp.cName FROM tmp INNER JOIN products ON tmp.pID = products.pID WHERE products.pID = tmp.pID;
14	AFTER/BEFORE INSERT	tmp	INSERT INTO products(products.cName) VALUES ( SELECT tmp.cName FROM tmp INNER JOIN products ON products.pID = tmp.pID WHERE products.pID = tmp.pID );
15	AFTER/BEFORE INSERT	tmp	INSERT INTO products (cName) SELECT cName FROM credits WHERE NEW.cID = credits.cID;

Het zou heel erg fijn zijn als iemand van jullie mij hiermee kan helpen.
Alvast dank ik jullie voor het nemen van de moeite hiervoor.

Met vriendelijke groet,
umask

[ Voor 21% gewijzigd door umask op 21-05-2019 23:09 ]

[Wintervacht]

Als je dezelfde waarde in verschillende tabellen nodig hebt deugt er iets niet aan je data structuur.
IS de Product wel een FK in de Credits tabel?

[umask]

Wintervacht schreef op woensdag 15 mei 2019 @ 15:03:
Als je dezelfde waarde in verschillende tabellen nodig hebt deugt er iets niet aan je data structuur.
IS de Product wel een FK in de Credits tabel?

Ja, dat is een FK. In de credits tabel is cID een FK. PS naar mijn idee moet dit kunnen, je voegt simpelweg data van de ene tabel naar de andere ... maar dan ligt dit toch ingewikkeld, omdat ik dit op een andere manier dan gewoonlijk probeer te doen. Ik gebruik namelijk dezelfde tabel en een of andere reden werkt dit niet..

[ Voor 29% gewijzigd door umask op 15-05-2019 15:08 ]

[Wintervacht]

Dan kun je de naam toch uit de gekoppelde record halen?
Dus iets in de richting van credit.product.name in je code aanroepen?

Ik mis even de context waarom de naam in beide tabellen zou moeten staan...

[KabouterSuper]

Ik ben geen expert in MySQL, maar in oracle heb ik de nodige ervaring met triggers. Wat je moet doen is voordat je de insert/update doet de waarde ophalen en je kolom overrulen, in plaats van achteraf de hele tabel te updaten.

Met andere woorden: in je before-row trigger haal de naam van je cID op:

code:

select cName from credits 
 WHERE Credits.cID=NEW.cID;

Vervolgens gebruik je deze cName om je waarde te overrulen:

code:

SET NEW.cName=<hier de waarde van je select>

Je trigger zou er ongeveer als volgt uit moeten zien:

code:

CREATE TRIGGER Products_BEF_IN BEFORE INSERT ON Products
  FOR EACH ROW BEGIN
    DECLARE vcName varchar(20);
    select cName INTO vcName from credits WHERE Credits.cID=NEW.cID;
    SET NEW.cName = vcName;
  END;

Wat je nu probeert te doen is bij een insert van de products-tabel de hele tabel proberen te updaten. Dit levert een oneindige loop op want elke update van elke regel in de tabel triggert weer een volledige update.

[ Voor 17% gewijzigd door KabouterSuper op 15-05-2019 15:30 . Reden: De hele trigger toegevoegd ]

[CurlyMo]

Wat @Wintervacht zegt. Je hebt nu 2 problemen:
1. Je datamodel hoort deze vorm van redundantie tegen te gaan.
2. Door hiervoor triggers te gebruiken begrijpt je opvolger er niks meer van, wat het is totaal onlogisch.

Zo'n foutmelding is ook vaak genoeg indicatie. MySQL zegt hier: "What the fuck probeer je nu eigenlijk te doen". En als MySQL het al zegt dan moet het wel bizar zijn, want zo strict is het niet.

[umask]

Wintervacht schreef op woensdag 15 mei 2019 @ 15:15:
Dan kun je de naam toch uit de gekoppelde record halen?
Dus iets in de richting van credit.product.name in je code aanroepen?

Ik mis even de context waarom de naam in beide tabellen zou moeten staan...

Het is namelijk zo dat ik de naam ook in die andere tabel nodig heb... Dat is het juist, ik probeer dit dus met een trigger op dezelfde tabel uit te voeren, dan krijg ik een foutmelding... Ik probeer door middel van een INNER JOIN oftewel een koppeling (en heel veel andere zoals die ik hierboven heb vermeld) de record te verkrijgen, maar dat lukt dan niet

[CurlyMo]

umask schreef op woensdag 15 mei 2019 @ 15:25:
[...]


Het is namelijk zo dat ik de naam ook in die andere tabel nodig heb...

Je legt nog steeds niet uit waarom.

In 99.9% van de gevallen wijst dit toch echt op een modelfout. Kan zijn dat jij 0.1% vertegenwoordigd, maar je helpt jezelf als je ons kan laten weten of je toch echt niet bij die 99.9% hoort.

[DataGhost]

Maar wat is er dan precies mis met je product-info ophalen met

SQL:

SELECT credits.cName, products.dinges FROM products INNER JOIN credits ON products.cID=credits.cID

? Dan hoef je helemaal niet te klooien met triggers of wat dan ook.

[umask]

CurlyMo schreef op woensdag 15 mei 2019 @ 15:28:
[...]

Je legt nog steeds niet uit waarom.

In 99.9% van de gevallen wijst dit toch echt op een modelfout. Kan zijn dat jij 0.1% vertegenwoordigd, maar je helpt jezelf als je ons kan laten weten of je toch echt niet bij die 99.9% hoort.

Ik wil het namelijk binnen de WPF app aan de gebruiker laten zien wat de Credits naam is zeg maar ...

[umask]

DataGhost schreef op woensdag 15 mei 2019 @ 15:29:
Maar wat is er dan precies mis met je product-info ophalen met

SQL:

SELECT credits.cName, products.dinges FROM products INNER JOIN credits ON products.cID=credits.cID

? Dan hoef je helemaal niet te klooien met triggers of wat dan ook.

Wil je zeggen dat ik dit in de C# WPF applicatie recht moet zetten oftewel moet uitproberen i.p.v. met triggers?

[DataGhost]

Ja, dat is wel de gangbare manier van werken.

[CurlyMo]

umask schreef op woensdag 15 mei 2019 @ 15:30:
[...]


Ik wil het namelijk binnen de WPF app aan de gebruiker laten zien wat de Credits naam is zeg maar ...

Dan doe je dat toch met een select statement zoals hierboven al is gezegd. En als je om wat voor een exotische reden geen joins kan maken in je select (omdat bijv. je ORM dat niet slikt), maak dan desnoods een view met die products tabel incl. naam. Dan gebruik je die view in je WPF app.

Overigens zegt mij WPF niks dus kan nog steeds niet beoordelen of je 0.1% bent.

[ Voor 3% gewijzigd door CurlyMo op 15-05-2019 15:35 ]

[tekken4111]

umask schreef op woensdag 15 mei 2019 @ 15:30:
[...]


Ik wil het namelijk binnen de WPF app aan de gebruiker laten zien wat de Credits naam is zeg maar ...

Dan doe je gewoon:

Select products.cName where products.cID == credits.cID. of een join zoals hierboven wordt aangegeven? Dat is het hele nut van een fk. Anders krijg je dubbele data (name in zowel products als credits).

[umask]

KabouterSuper schreef op woensdag 15 mei 2019 @ 15:19:
Je trigger zou er ongeveer als volgt uit moeten zien:

code:

CREATE TRIGGER Products_BEF_IN BEFORE INSERT ON Products
  FOR EACH ROW BEGIN
    DECLARE vcName varchar(20);
    select cName INTO vcName from credits WHERE Credits.cID=NEW.cID;
    SET NEW.cName = vcName;
  END;

Wat je nu probeert te doen is bij een insert van de products-tabel de hele tabel proberen te updaten. Dit levert een oneindige loop op want elke update van elke regel in de tabel triggert weer een volledige update.

Hartelijk bedankt!!! Dit was de oplossing. Wat ik heb gedaan is het volgende:
Ik heb handmatig een trigger (tijdstip: BEFORE, gebeurtenis: INSERT) aangemaakt, vervolgens heb ik de volgende MySQL code erin gezet:

MySQL:

BEGIN
    DECLARE vcName varchar(20);
    select cName INTO vcName from credits WHERE Credits.cID=NEW.cID;
    SET NEW.cName = vcName;
END

Ik dank iedereen voor alle moeite, maar dit was de oplossing. Ik dank u zeer.

[umask]

umask: Wil je zeggen dat ik dit in de C# WPF applicatie recht moet zetten oftewel moet uitproberen i.p.v. met triggers?

DataGhost schreef op woensdag 15 mei 2019 @ 15:32:
Ja, dat is wel de gangbare manier van werken.

Dat is goed om te weten, dit zal ik in de toekomst dan proberen aan te houden.
Dank u wel.

[ Voor 21% gewijzigd door umask op 23-05-2019 09:18 ]

[umask]

CurlyMo schreef op woensdag 15 mei 2019 @ 15:33:
[...]

Dan doe je dat toch met een select statement zoals hierboven al is gezegd. En als je om wat voor een exotische reden geen joins kan maken in je select (omdat bijv. je ORM dat niet slikt), maak dan desnoods een view met die products tabel incl. naam. Dan gebruik je die view in je WPF app.

Overigens zegt mij WPF niks dus kan nog steeds niet beoordelen of je 0.1% bent.

Mijn excuses. WPF is een applicatie dat u kunt aanmaken binnen het programma Visual Studio 2017. Dit programma wordt gebruikt bij het ontwikkelen en maken van verschillende applicaties. Ik dank u alvast voor de tip.

[CurlyMo]

Prima wanneer ja, maar je neemt het voor lief dat je waarschijnlijk een zeer atypische oplossing kiest voor een waarschijnlijk zeer simpel (initieel) probleem?

[DataGhost]

umask schreef op woensdag 15 mei 2019 @ 15:43:
[...]


Hartelijk bedankt!!! Dit was de oplossing. Wat ik heb gedaan is het volgende:
Ik heb handmatig een trigger (tijdstip: BEFORE, gebeurtenis: INSERT) aangemaakt, vervolgens heb ik de volgende MySQL code erin gezet:

code:

BEGIN
    DECLARE vcName varchar(20);
    select cName INTO vcName from credits WHERE Credits.cID=NEW.cID;
    SET NEW.cName = vcName;
END

Ik dank iedereen voor alle moeite, maar dit was de oplossing. Ik dank u zeer.

Okee, en nu besluit je over een tijdje dat de cName in credits moet veranderen, moet je er nog aan denken om dat in products (en alle andere plekken die je ongetwijfeld op dezelfde manier in gaat richten) ook te doen. Zeker als je 1..n-relaties hebt kan het aantal updates behoorlijk in de papieren lopen. En voor elk veld wat je "in meerdere tabellen wilt hebben" moet je een trigger gaan maken per tabel. Terwijl je het met een goed datamodel maar op 1 plek hoeft te wijzigen en alles wijzigt magisch mee zonder dat je moeilijk hoeft te doen met triggers, omdat je de naam met een join ophaalt.

Heb je uberhaupt geprobeerd om de query die ik je voorkauwde in je applicatie te stoppen en te kijken wat er gebeurde?

[ Voor 9% gewijzigd door DataGhost op 15-05-2019 15:56 ]

[umask]

CurlyMo schreef op woensdag 15 mei 2019 @ 15:51:
Prima wanneer ja, maar je neemt het voor lief dat je waarschijnlijk een zeer atypische oplossing kiest voor een waarschijnlijk zeer simpel (initieel) probleem?

Nee, dit is precies wat ik nodig had. Het werkt heel goed, ondanks dat het een simpele oplossing is. Misschien was dit een simpel probleem, maar ik probeer mezelf op het gebied van MySQL en SQL te ontwikkelen, daarom dat ik dit nog niet zo goed snapte..

[umask]

DataGhost schreef op woensdag 15 mei 2019 @ 15:54:
[...]

Okee, en nu besluit je over een tijdje dat de cName in credits moet veranderen, moet je er nog aan denken om dat in products (en alle andere plekken die je ongetwijfeld op dezelfde manier in gaat richten) ook te doen. Zeker als je 1..n-relaties hebt kan het aantal updates behoorlijk in de papieren lopen. Terwijl je het met een goed datamodel maar op 1 plek hoeft te wijzigen en alles wijzigt magisch mee zonder dat je moeilijk hoeft te doen met triggers, omdat je de naam met een join ophaalt.

Nee, dat klopt. Voortaan zal ik dit ook in de code zelf te doen, heel erg bedankt voor de tip.

[CurlyMo]

umask schreef op woensdag 15 mei 2019 @ 15:55:
[...]


Nee, dit is precies wat ik nodig had. Het werkt heel goed, ondanks dat het een simpele oplossing is.

Nee, de oplossing is enorm complex voor de simpliciteit van het eigenlijke probleem. Dat is dat je join niet werkte.

Misschien was dit een simpel probleem, maar ik probeer mezelf op het gebied van MySQL en SQL te ontwikkelen, daarom dat ik dit nog niet zo goed snapte..

Sta je er dan voor open om ons je te laten helpen een courante oplossing toe te passen en daarmee ook SQL te leren zoals het over de hele wereld door experts wordt toegepast?

[umask]

DataGhost schreef op woensdag 15 mei 2019 @ 15:54:
[...]

Heb je uberhaupt geprobeerd om de query die ik je voorkauwde in je applicatie te stoppen en te kijken wat er gebeurde?

Ook dit zal ik nog eens gaan uitproberen natuurlijk, maar dat is op het moment niet mijn doelstelling. Ik ben namelijk op het moment met triggers, etc. bezig om dat goed te snappen..

[umask]

CurlyMo schreef op woensdag 15 mei 2019 @ 15:57:
[...]

Nee, de oplossing is enorm complex voor de simpliciteit van het eigenlijke probleem. Dat is dat je join niet werkte.


[...]

Sta je er dan voor open om ons je te laten helpen een courante oplossing toe te passen en daarmee ook SQL te leren zoals het over de hele wereld door experts wordt toegepast?

Natuurlijk sta ik daarvoor open. Als er een betere oplossing is, zou ik dat zeker willen weten. Ik wil namelijk hier meer over weten.

[Bas170]

@umask begin maar eens met meerdere reacties in 1 post te beantwoorden, i.p.v. per reactie een antwoord te plaatsen

Hint: gebruik de wijzig knop boven je reactie

[CurlyMo]

umask schreef op woensdag 15 mei 2019 @ 15:59:
[...]


Natuurlijk sta ik daarvoor open. Als er een betere oplossing is, zou ik dat zeker willen weten. Ik wil namelijk hier meer over weten.

Even voor ons begrip:
- Weet je wat een relationeel database model is?
- Weet je welke rol foreign keys daarin innemen?

Zou je dat voor ons kunnen samenvatten? Dan sturen wij je wel bij waar nodig

[umask]

Bas170 schreef op woensdag 15 mei 2019 @ 16:00:
@umask begin maar eens met meerdere reacties in 1 post te beantwoorden, i.p.v. per reactie een antwoord te plaatsen

Hint: gebruik de wijzig knop boven je reactie

Ik stond hier niet bij stil, sorry. Dank u wel voor de tip.

CurlyMo schreef op woensdag 15 mei 2019 @ 16:01:
[...]

Even voor ons begrip:
- Weet je wat een relationeel database model is?
- Weet je welke rol foreign keys daarin innemen?

Zou je dat voor ons kunnen samenvatten? Dan sturen wij je wel bij waar nodig

Goed, ik zal het even proberen samen te vatten:
Een relationeel database model houdt in dat het bestaat uit data waarbij de data vervolgens gekoppeld kan worden aan andere tabellen binnen de database?

Foreign Keys zorgen ervoor dat de gegevens unique opgeslagen staan in een tabel?

[ Voor 83% gewijzigd door umask op 15-05-2019 17:21 ]

[CurlyMo]

umask schreef op woensdag 15 mei 2019 @ 16:07:
[...]
Een relationeel database model houdt in dat het bestaat uit data waarbij de data vervolgens gekoppeld kan worden aan andere tabellen binnen de database?

Welke voordelen zou dat kunnen hebben? Waarom stop je niet gewoon alles in één grote tabel. Dan hoef je ook niet te koppelen.

Foreign Keys zorgen ervoor dat de gegevens unique opgeslagen staan in een tabel?

Nee, dat doet de unique constraint. De FK constraint zegt dat de waarde van een kolom in tabel A ook moet bestaan in een specifiek kolom in tabel B. Ook zegt het wat er moet gebeuren wil je die regel breken. Dus wanneer je uit tabel B die waarde wilt verwijderen of bijwerken terwijl er vanuit tabel A een relatie mee is gelegd. Ook kan je prima meerdere rijen uit tabel A laten refereren aan meerdere rijen uit tabel B. Dat in tegenstelling tot een unique constraint. Die dwingt af dat een bepaalde waarde in een kolom maar één keer voorkomt binnen(!) een specifieke tabel.

[CurlyMo]

@umask als je meerdere keren achter elkaar reageert, dan samenvoegen. Als iemand anders weer eerst heeft gereageerd, dan graag een nieuwe reactie.

umask schreef op woensdag 15 mei 2019 @ 16:07:
[...]
ah, oké. Dank u wel voor de uitleg.

Ik stelde je ook een vraag in mijn uitleg. Zou je daar op in kunnen gaan?


Noodgedwongen even twee keer achter elkaar gereageerd

[ Voor 34% gewijzigd door CurlyMo op 15-05-2019 16:49 ]

[umask]

CurlyMo schreef op woensdag 15 mei 2019 @ 16:12:
[...]

Welke voordelen zou dat kunnen hebben? Waarom stop je niet gewoon alles in één grote tabel. Dan hoef je ook niet te koppelen.

umask: ik denk dat je database anders traag wordt en heb je meer structuur (+ overzicht) in je database, dan alles in een tabel..

CurlyMo schreef op woensdag 15 mei 2019 @ 16:12:
[...]

Nee, dat doet de unique constraint. De FK constraint zegt dat de waarde van een kolom in tabel A ook moet bestaan in een specifiek kolom in tabel B. Ook zegt het wat er moet gebeuren wil je die regel breken. Dus wanneer je uit tabel B die waarde wilt verwijderen of bijwerken terwijl er vanuit tabel A een relatie mee is gelegd. Ook kan je prima meerdere rijen uit tabel A laten refereren aan meerdere rijen uit tabel B. Dat in tegenstelling tot een unique constraint. Die dwingt af dat een bepaalde waarde in een kolom maar één keer voorkomt binnen(!) een specifieke tabel.

umask: ah, oké. Dank u wel voor de uitleg.

Ik had per ongeluk mijn antwoord op uw vraag binnen de quote gezet, waardoor het bijna niet te lezen was. Nu staat het hierboven, zodat het wat leesbaarder is.

[ Voor 14% gewijzigd door umask op 15-05-2019 17:22 ]

[CurlyMo]

umask schreef op woensdag 15 mei 2019 @ 17:21:
[...]
ik denk dat je database anders traag wordt en heb je meer structuur (+ overzicht) in je database, dan alles in een tabel..

Traag is in jouw situatie nog lang geen probleem

Kan je aangegeven waarom jij meerdere tabellen hebt gemaakt? Waarom dacht je dat dat een goed idee was? Kan je dat toelichten?

[umask]

CurlyMo schreef op woensdag 15 mei 2019 @ 19:06:
[...]

Traag is in jouw situatie nog lang geen probleem

Kan je aangegeven waarom jij meerdere tabellen hebt gemaakt? Waarom dacht je dat dat een goed idee was? Kan je dat toelichten?

Ik dacht dat ik daardoor verschillende informatie daardoor kon onderscheiden, want niet alle informatie is relevant om die overal te gebruiken?

[CurlyMo]

umask schreef op donderdag 16 mei 2019 @ 08:20:
[...]


Ik dacht dat ik daardoor verschillende informatie daardoor kon onderscheiden, want niet alle informatie is relevant om die overal te gebruiken?

Dat is op zich óók een goede reden om het te doen, maar alleen gebruik je daarbij niet de kracht die een relationeel database model kan hebben. Laten we het voor nu simpel houden en ons alleen focussen op het voorkomen van redundantie. Dat is uiteindelijk waar anderen hier ook op reageren.

Wat je met een relationeel model wil is zoveel mogelijk inhoudelijke informatie zoveel mogelijk enkelvoudig opslaan en waar informatie met elkaar relateert, koppelingen leggen met sleutels. Dat doe je al netjes met je cID's. Zodat je bij het bijwerken van data je dit zoveel mogelijk op één plek hoeft te doen. Daarnaast zorgt het ervoor dat wanneer data zoveel mogelijk enkelvoudig is opgeslagen, je bij het selecteren ook niet hoeft te twijfelen over de juistheid van meerdere versies van hetzelfde gegeven over meerdere tabellen.

Dus terug naar jouw situatie:

Products
pID
cID
pName
cName

Credits
cID
cName

Wat de tmp tabel moet doen is me niet helemaal helder.

Door cName óók in Products te zetten creëer je dus redundantie en verlies dus je een belangrijke meerwaarde van een relationeel model. Bij het opslaan, omdat je bezig bent met het onnodige synchronisaties tussen twee tabellen. Kan je garanderen dat dat altijd goed gaat? Welke garantie heb je dat niet iemand ook bij het alleen invoeren in Products ook de cName vult, waardoor er een cName in products staat die niet in Credits te vinden is? Of dat iemand de cName in Products aanpast zonder dat ook in Credits aan te passen. Je hebt dus een enorm complex synchronisatie probleem gecreëerd en dat is ook waar je op vast gelopen bent bij het openen van dit topic. En bij het selecteren: Heb ik de garantie dat de cName in Products altijd hetzelfde is als die in Credits?

Het is dus goed dat je je bewust bent van deze risico en ze met een trigger probeert te tackelen. Daarvoor complimenten. Alleen zijn die risico's te vermijden waardoor ook je oplossingen daartoe niet nodig zijn.

De volgende stap is dus zorgen dat je je data opslaat in de tabellen zoals ik ze in deze reactie neer heb gezet. Dus zonder cName in Products.

Als je dat hebt gedaan, dan zullen we nogmaals focussen op de selectie kant Succes.

[umask]

CurlyMo schreef op donderdag 16 mei 2019 @ 08:36:
[...]


Wat de tmp tabel moet doen is me niet helemaal helder.

Door cName óók in Products te zetten creëer je dus redundantie en verlies dus je een belangrijke meerwaarde van een relationeel model. Bij het opslaan, omdat je bezig bent met het onnodige synchronisaties tussen twee tabellen. Kan je garanderen dat dat altijd goed gaat? Welke garantie heb je dat niet iemand ook bij het alleen invoeren in Products ook de cName vult, waardoor er een cName in products staat die niet in Credits te vinden is? Of dat iemand de cName in Products aanpast zonder dat ook in Credits aan te passen. Je hebt dus een enorm complex synchronisatie probleem gecreëerd en dat is ook waar je op vast gelopen bent bij het openen van dit topic. En bij het selecteren: Heb ik de garantie dat de cName in Products altijd hetzelfde is als die in Credits?

Oh, wacht even. Volgens mij zijn er onduidelijkheden in mijn context die ik heb geformuleerd. Ik zal even uitleggen hoe het systeem werkt dat ik aan het bouwen ben.

tmp tabel
Deze tabel kunt u negeren, deze gebruik ik voor testdoeleinden. Eigenlijk kon ik deze tabel weglaten, maar dit was ik even vergeten, omdat ik de werkelijke situatie wilde schetsen.

Overzicht van producten
De gebruiker kan op dit scherm alleen pID, cID en pName toevoegen. De cName wordt dus uit de credits tabel gehaald, vandaar dat ik ook een extra kolom heb in products waar ik het kan opslaan. Op basis van de cID kijkt het systeem wat de cName is in credits en vervolgens neemt hij die desbetreffende cName.

Antwoord op uw vraag: hierdoor creëer ik geen redundantie en weet ik zeker dat de informatie klopt.

Overzicht van credits
Ook hier heb ik gebruikgemaakt van een trigger. Zodra de cName hier wordt aangepast, zorg ik er met een trigger voor dat de cName in products wordt aangepast.

Antwoord op uw vraag: ook hier weer weet ik dat de informatie integer blijft en ik zeker weet dat ik de juiste informatie gebruik.

Mijn excuses dat ik misschien hier niet eerder duidelijk over was, maar zo zit het dus in elkaar.

[CurlyMo]

umask schreef op vrijdag 17 mei 2019 @ 08:56:
[...]
Antwoord op uw vraag: hierdoor creëer ik geen redundantie en weet ik zeker dat de informatie klopt.

Je creëert wel redundantie want je slaat cName twee keer op. Eén keer in Products en één keer in Credits. Dat is simpelweg wat er met redundantie wordt bedoeld in de context van data-opslag. Dezelfde gegevens op meerdere plekken opslaan (m.u.v. meta-data zoals sleutels).

Overzicht van credits
Ook hier heb ik gebruikgemaakt van een trigger. Zodra de cName hier wordt aangepast, zorg ik er met een trigger voor dat de cName in products wordt aangepast.

Maar waarom zou je dat doen? Je haalt nu toch al de juiste cName op bij het cID wat je uit Products haalt, waardoor je bij elk product via het cID de juiste cName uit Credits te halen valt.

Je vind wat je doet waarschijnlijk oprecht logisch, maar wij snappen de logica daarachter oprecht niet

[DataGhost]

Wat je doet is zo'n beetje de definitie van redundantie, dus hoe je erbij komt dat je dat niet hebt vraag ik me af. Je hebt immers bij elk product in products een pName die te allen tijde gelijk hoort te zijn (claim je) aan de cName van een rij in credits met dezelfde cID.

products

pID	cID	cName	pName
1	2	Waa	Stofzuiger
2	1	Meukee	Waterpistool

credits

cID	cName
1	Meukee
2	Waa

Je ziet dat "Waa" en "Meukee" in beide tabellen voorkomen. Als pName altijd hetzelfde is aan cName heeft het dus geen enkel nut om deze te dupliceren, redundant te hebben er zijn natuurlijk uitzonderingen in bepaalde enorm high-performance situaties maar die heb je hier niet
Als je nou de hele kolom pName dropt en dan mijn eerder genoemde query gebruikt (kleine wijziging), dus:

SQL:

SELECT pID, products.cID, credits.cName as cName, pName FROM products INNER JOIN credits ON credits.cID=products.cID

krijg je als resultaat exact wat nu je products-tabel is, terwijl je geen triggers meer nodig hebt on insert en update, omdat het altijd bij het queryen goed wordt afgehandeld. Stel je eens voor wat een ontzettende puinhoop het gaat worden als je straks 20+ tabellen met 50+ van dat soort velden hebt. Triggers zijn zelden een oplossing die je nodig hebt.

En als je dan écht recalcitrant bent doet een VIEW precies voor je wat je wilt bereiken zonder dat je zelf met triggers aan de slag hoeft

Edit: dit is toch precies wat je wilt?

mysql> CREATE TABLE products (pID int, cID int, pName varchar(50) NULL);
mysql> CREATE TABLE credits (cID int, cName varchar(20) NULL);
mysql> INSERT INTO credits VALUES (1, 'Meukee'), (2, 'Waa');
mysql> INSERT INTO products VALUES (1, 2, 'Stofzuiger'), (2, 1, 'Waterpistool');
mysql> SELECT pID, products.cID, credits.cName as cName, pName FROM products INNER JOIN credits ON credits.cID=products.cID;
+------+------+--------+--------------+
| pID  | cID  | cName  | pName        |
+------+------+--------+--------------+
|    2 |    1 | Meukee | Waterpistool |
|    1 |    2 | Waa    | Stofzuiger   |
+------+------+--------+--------------+
2 rows in set (0.00 sec)

Met goed gebruik van Foreign Keys kan je het een en ander nog verder vastleggen en kan je ook niet meer zomaar verkeerde data invoeren (bijvoorbeeld een cID die niet bestaat).

[ Voor 21% gewijzigd door DataGhost op 17-05-2019 09:25 ]

[umask]

CurlyMo schreef op vrijdag 17 mei 2019 @ 09:12:
[...]

Je creëert wel redundantie want je slaat cName twee keer op. Eén keer in Products en één keer in Credits. Dat is simpelweg wat er met redundantie wordt bedoeld in de context van data-opslag. Dezelfde gegevens op meerdere plekken opslaan (m.u.v. meta-data zoals sleutels).

umask: Hmm, dus toch wel. Nu ik erover nog eens nadenk, heeft u toch wel gelijk. Dezelfde gegevens staan op meerdere plekken en heb je dus redundantie, dank u wel voor de uitleg.

Overzicht van credits
Ook hier heb ik gebruikgemaakt van een trigger. Zodra de cName hier wordt aangepast, zorg ik er met een trigger voor dat de cName in products wordt aangepast.

Maar waarom zou je dat doen? Je haalt nu toch al de juiste cName op bij het cID wat je uit Products haalt, waardoor je bij elk product via het cID de juiste cName uit Credits te halen valt.

Je vind wat je doet waarschijnlijk oprecht logisch, maar wij snappen de logica daarachter oprecht niet

umask: Ik wil namelijk dat de gebruiker de cName ook in tabel 'products' kan zien, daarom heb ik die kolom in products. De reden dat ik weer een trigger gebruik, is dat als ik iets in credits aanpas en het ook meteen wordt aangepast in products?

[umask]

DataGhost schreef op vrijdag 17 mei 2019 @ 09:15:
Je ziet dat "Waa" en "Meukee" in beide tabellen voorkomen. Als pName altijd hetzelfde is aan cName heeft het dus geen enkel nut om deze te dupliceren, redundant te hebben er zijn natuurlijk uitzonderingen in bepaalde enorm high-performance situaties maar die heb je hier niet
Als je nou de hele kolom pName dropt en dan mijn eerder genoemde query gebruikt (kleine wijziging), dus:

SQL:

SELECT pID, cID, credits.cName as cName, pName FROM products INNER JOIN credits ON credits.cID=products.cID

krijg je als resultaat exact wat nu je products-tabel is, terwijl je geen triggers meer nodig hebt on insert en update, omdat het altijd bij het queryen goed wordt afgehandeld. Stel je eens voor wat een ontzettende puinhoop het gaat worden als je straks 20+ tabellen met 50+ van dat soort velden hebt. Triggers zijn zelden een oplossing die je nodig hebt.

En als je dan écht recalcitrant bent doet een VIEW precies voor je wat je wilt bereiken zonder dat je zelf met triggers aan de slag hoeft

Oké, bedankt voor de nuttige uitleg. Dan zal ik die triggers verwijderen en het proberen met C# code en SQL Queries op te lossen.

[MrMonkE]

Bas170 schreef op woensdag 15 mei 2019 @ 16:00:
@umask begin maar eens met meerdere reacties in 1 post te beantwoorden, i.p.v. per reactie een antwoord te plaatsen

Hint: gebruik de wijzig knop boven je reactie

Jammer dat er geen merge knop is.

[umask]

DataGhost schreef op vrijdag 17 mei 2019 @ 09:15:
Edit: dit is toch precies wat je wilt?

mysql> CREATE TABLE products (pID int, cID int, pName varchar(50) NULL);
mysql> CREATE TABLE credits (cID int, cName varchar(20) NULL);
mysql> INSERT INTO credits VALUES (1, 'Meukee'), (2, 'Waa');
mysql> INSERT INTO products VALUES (1, 2, 'Stofzuiger'), (2, 1, 'Waterpistool');
mysql> SELECT pID, products.cID, credits.cName as cName, pName FROM products INNER JOIN credits ON credits.cID=products.cID;
+------+------+--------+--------------+
| pID  | cID  | cName  | pName        |
+------+------+--------+--------------+
|    2 |    1 | Meukee | Waterpistool |
|    1 |    2 | Waa    | Stofzuiger   |
+------+------+--------+--------------+
2 rows in set (0.00 sec)

Met goed gebruik van Foreign Keys kan je het een en ander nog verder vastleggen en kan je ook niet meer zomaar verkeerde data invoeren (bijvoorbeeld een cID die niet bestaat).

Ja, dit is precies wat ik wil. Dank u wel. Ik waardeer dit zeer. In mijn applicatie zal ik dit gebruiken, want ik denk dat dit ook wel beter is.

[CurlyMo]

umask schreef op vrijdag 17 mei 2019 @ 09:32:
[...]


Ja, dit is precies wat ik wil. Dank u wel. Ik waardeer dit zeer. In mijn applicatie zal ik dit gebruiken, want ik denk dat dit ook wel beter is.

Realiseer je ook dat dit dé manier is om het te doen. Dus je splitst je data uit over meerdere logische tabellen en vervolgens voeg je die data weer samen in je selectie.

Zou je nu in het voorbeeld van @DataGhost nu het aanmaken van een FK kunnen verwerken?

Overigens wel weer jammer dat @DataGhost weer letterlijk het antwoord geeft, terwijl ik net lekker bezig was met @umask zelf tot die oplossing en realisatie te komen. "Give a man a fish...". Net zoals @Robbiii aangeeft hier RobIII in "[PHP] mail() verstuurd niet alle variabelen en tekst" waar je zelf lekker pittig in participeert

[ Voor 25% gewijzigd door CurlyMo op 17-05-2019 11:03 ]

[DataGhost]

CurlyMo schreef op vrijdag 17 mei 2019 @ 10:50:
[...]

Realiseer je ook dat dit dé manier is om het te doen. Dus je splitst je data uit over meerdere logische tabellen en vervolgens voeg je die data weer samen in je selectie.

Zou je nu in het voorbeeld van @DataGhost nu het aanmaken van een FK kunnen verwerken?

Overigens wel weer jammer dat @DataGhost weer letterlijk het antwoord geeft, terwijl ik net lekker bezig was met @umask zelf tot die oplossing en realisatie te komen. "Give a man a fish...". Net zoals @Robbiii aangeeft hier RobIII in "[PHP] mail() verstuurd niet alle variabelen en tekst" waar je zelf lekker pittig in participeert

Sorry, ik liet me even gaan Ik ging er in mijn eerste post (wellicht ten onrechte) van uit dat hij alleen een "duwtje in de goede richting" nodig had aangezien in de topicstart al kennis over JOINs bleek. Ik had niet door dat de onderliggende kennis mbt datamodellen volledig ontbrak, mijn fout. Dus daar kwam in principe de hele oplossing "voorgekauwd" uit. Ik heb in mijn laatste posts IMO niet veel nieuws gezegd, alleen de reeds in het topic aanwezige informatie in een andere vorm gepresenteerd en nog iets verder uitgewerkt om het punt duidelijk te maken. Het stukje over FK's heb ik expres opengelaten en mag je mee verder gaan

[umask]

CurlyMo schreef op vrijdag 17 mei 2019 @ 10:50:
[...]

Realiseer je ook dat dit dé manier is om het te doen. Dus je splitst je data uit over meerdere logische tabellen en vervolgens voeg je die data weer samen in je selectie.

Zou je nu in het voorbeeld van @DataGhost nu het aanmaken van een FK kunnen verwerken?

Ja, ik realiseer het nu en daarom ben ik heel erg blij dat mensen net zoals u mij hiermee hebben geholpen. Daarvoor ben ik jullie erg dankbaar. Het verwerken van de FK lukt nu ook, dank u wel.

Ter bevestiging voor mezelf, geef het aub aan als ik hier naast zit: in tabel A heb je de primary key en de FK vind je weer terug in die andere tabel (de waardes van PK en FK komen overigens wel overeen met elkaar in beide tabellen), vervolgens kunnen deze gegevens samengevoegd worden door inderdaad een selectie van te maken net zoals het voorbeeld dat eerder is gedeeld door @DataGhost .

[ Voor 25% gewijzigd door umask op 21-05-2019 23:01 ]

[CurlyMo]

umask schreef op dinsdag 21 mei 2019 @ 22:57:
[...]
Ter bevestiging voor mezelf, geef het aub aan als ik hier naast zit: in tabel A heb je de primary key en de FK vind je weer terug in die andere tabel (de waardes van PK en FK komen overigens wel overeen met elkaar in beide tabellen)

Kan je ter bevestiging de code voor het aanmaken van de FK delen?

[umask]

CurlyMo schreef op woensdag 22 mei 2019 @ 07:57:
[...]

Kan je ter bevestiging de code voor het aanmaken van de FK delen?

Hierbij de code die ik zou gebruiken bij het aanmaken van de FK:

MySQL:

ALTER TABLE products ADD CONSTRAINT FK_cID FOREIGN KEY products(cID) REFERENCES credits(cID) ON DELETE RESTRICT ON UPDATE RESTRICT;

[CurlyMo]

@umask Waarom restrict?

[umask]

CurlyMo schreef op donderdag 23 mei 2019 @ 09:21:
@umask Waarom restrict?

Ik heb het idee dat als ik de kolom cID in products wil verwijderen, MySQL dan zegt: "Dat mag niet, want hij bestaat nog in de tabel credits." Waarom ik het bij UPDATE ook zo heb gedaan weet ik eigenlijk niet. Ik dacht: als ik dat bij ON DELETE doe, dat de actie op dezelfde manier ook bij ON UPDATE toegepast moet worden.

PS 'NO ACTION' is toch ongeveer hetzelfde als RESTRICT?

[ Voor 13% gewijzigd door umask op 23-05-2019 09:32 ]

[DataGhost]

umask schreef op donderdag 23 mei 2019 @ 09:24:
[...]


Ik heb het idee dat als ik de kolom cID in products wil verwijderen, MySQL dan zegt: "Dat mag niet, want hij bestaat nog in de tabel credits." Waarom ik het bij UPDATE ook zo heb gedaan weet ik eigenlijk niet. Ik dacht: als ik dat bij ON DELETE doe, dat de actie op dezelfde manier ook bij ON UPDATE toegepast moet worden.

PS 'NO ACTION' is toch ongeveer hetzelfde als RESTRICT?

Goeie gedachtegang, maar je uitleg klopt niet helemaal of je bedoelt het anders. Je kan namelijk prima je products-tabel leeggooien.

[CurlyMo]

@umask begin eerst eens bij wat je zou willen bij een delete en een update nog los van de techniek.

Beantwoord de volgende vragen dus eens:
- Als ik een specifieke rij in credits wil verwijderen dan moet hij .... doen met alle rijen die ermee verbonden zijn in products.
- Als ik een specifieke rij in credits wil bijwerken dan moet hij .... doen met alle rijen die ermee verbonden zijn in products.

[umask]

CurlyMo schreef op donderdag 23 mei 2019 @ 10:48:
@umask begin eerst eens bij wat je zou willen bij een delete en een update nog los van de techniek.

Beantwoord de volgende vragen dus eens:
- Als ik een specifieke rij in credits wil verwijderen dan moet hij .... doen met alle rijen die ermee verbonden zijn in products.
- Als ik een specifieke rij in credits wil bijwerken dan moet hij .... doen met alle rijen die ermee verbonden zijn in products.

Ok, ik zal het even proberen te beantwoorden:
1) Als ik een specifieke rij in credits wil verwijderen dan moet hij niks doen met alle rijen die ermee verbonden zijn in products, want ik wil nog dat de oude gegevens te lezen zijn in die andere tabel.

2) Als ik een specifieke rij in credits wil bijwerken dan moet hij alle rijen aanpassen die ermee verbonden zijn in products, want ik wil dat de andere waardes aangepast worden in products, zolang de rij in credits bestaat.

[umask]

DataGhost schreef op donderdag 23 mei 2019 @ 10:19:
[...]

Goeie gedachtegang, maar je uitleg klopt niet helemaal of je bedoelt het anders. Je kan namelijk prima je products-tabel leeggooien.

Oh ok.

[Gomez12]

Om het trouwens ook eens vanaf een andere kant te benaderen, wat TS origineel wilde kan wel en is ook gewoon een (deels) geaccepteerde praktijk.

Alleen praat je dan veelal meer over deels historische data. Stel je hebt een tabel producten en je hebt een tabel facturen, dan wil je dat je facturen altijd een omschrijving hebben. Alleen niet per se de huidige product-omschrijving. Omdat die kan wijzigen door de tijd heen, je wilt feitelijk de omschrijving hebben ten tijde van aanmaak factuur.
En op dat moment veroorzaak je dan ook bewust redundantie, puur om te voorkomen dat als een product wat gisteren als omschrijving een potlood was en vandaag als omschrijving een auto bij het opnieuw uitdraaien van een factuur van gisteren een auto oplevert voor 1 euro...

Het is voor de TS niet direct relevant, alleen het is een andere kant dan enkel de juiste weg.

[ Voor 5% gewijzigd door Gomez12 op 23-05-2019 13:35 ]

[DataGhost]

Ja, maar wat TS origineel wilde was een trigger zodat een update in tabel A dezelfde wijziging in tabel B als gevolg zou hebben, om bepaalde data te allen tijde gesynchroniseerd te houden terwijl deze ook gewoon uit de relatie af te leiden is. Je zou het in jouw voorbeeld kunnen zien als alle NAW-gegevens van de klant herhalen bij elke factuurregel, in plaats van een factuurregel linken aan een factuur en een factuur aan een adres. En dan bij een adreswijziging (voordat de order verwerkt is, om het logisch te houden) alle factuurregels moeten gaan updaten met het nieuwe adres. Waar jij het over hebt gaat over data die juist behouden moet blijven ondanks updates aan het origineel, dat is iets heel anders. Doorgaans bewaar je dat ook in andere tabellen dan je live-data.

@umask: in de huidige situatie kan je juist geen credits weggooien zolang er nog products aan die credit gelinkt zijn, maar het klinkt nu alsof dat niet is wat je probeert te bereiken. Dat was wel mijn aanname als ik kijk naar je beschrijving in de TS. Wat dacht je dat er in je originele opzet zou gebeuren met de products.cName als je een product hebt met cID=3 en dan die rij uit credits weggooit? Wat is de reden dat ze uberhaupt gekoppeld zijn/waren, als je die relatie kennelijk niet altijd nodig hebt?

[CurlyMo]

umask schreef op donderdag 23 mei 2019 @ 12:17:
[...]
1) Als ik een specifieke rij in credits wil verwijderen dan moet hij niks doen met alle rijen die ermee verbonden zijn in products, want ik wil nog dat de oude gegevens te lezen zijn in die andere tabel.

Dus zodra je een rij uit credits verwijderd dan is er een rij in product dat geen relatie meer kan leggen met credits? Oftewel, je staat toe dat er producten in products staan zonder relatie naar credits? Is dat wat je wil?

2) Als ik een specifieke rij in credits wil bijwerken dan moet hij alle rijen aanpassen die ermee verbonden zijn in products, want ik wil dat de andere waardes aangepast worden in products, zolang de rij in credits bestaat.

Deze is helder. Als nu eens in de documentatie kijkt, welke actie sluit dan het beste aan bij je update? Degene die je gekozen hebt past namelijk niet.

Gomez12 schreef op donderdag 23 mei 2019 @ 13:34:
Om het trouwens ook eens vanaf een andere kant te benaderen, wat TS origineel wilde kan wel en is ook gewoon een (deels) geaccepteerde praktijk.

Alleen praat je dan veelal meer over deels historische data..

Ik snap wat je zegt, maar ik ben het niet met je eens. Historische data sla je historisch op D.w.z dat je ten alle tijden de historie moet kunnen reproduceren. Dat betekent dus geen updates, maar een insert-only model met twee timestamps: geldigheid en kennisgeving. Zo bestaat er geen redundantie, want elk gegeven moet een unieke waarde hebben i.c.m. met die geldigheid en kennisgeving. Als de facturen deze meta datums ook bevatten, dan weet je ten alle tijden wat de productkenmerken waren op het moment dat de factuur werd gemaakt. Een belangrijk deel van mijn werk is historische databases ontwerpen, dus ik ga graag (in een ander) topic dat gesprek met je aan

[umask]

DataGhost schreef op donderdag 23 mei 2019 @ 13:59:

@umask: in de huidige situatie kan je juist geen credits weggooien zolang er nog products aan die credit gelinkt zijn, maar het klinkt nu alsof dat niet is wat je probeert te bereiken.

Jawel, dit wil ik wel bereiken. Sorry, ik had een verkeerd antwoord gegeven.

Dat was wel mijn aanname als ik kijk naar je beschrijving in de TS. Wat dacht je dat er in je originele opzet zou gebeuren met de products.cName als je een product hebt met cID=3 en dan die rij uit credits weggooit? Wat is de reden dat ze uberhaupt gekoppeld zijn/waren, als je die relatie kennelijk niet altijd nodig hebt?

Ik heb het even uitgetest, maar dat gaat niet. Zolang er een hetzelfde cID in zowel products en credits zit waartussen een relatie is gezet, kan deze niet verwijderd worden. In mijn geval heb ik de relatie wel nodig, anders zijn de gegevens niet meer integer.

[umask]

CurlyMo schreef op donderdag 23 mei 2019 @ 15:12:
Dus zodra je een rij uit credits verwijderd dan is er een rij in product dat geen relatie meer kan leggen met credits? Oftewel, je staat toe dat er producten in products staan zonder relatie naar credits? Is dat wat je wil?

Wacht even, ik zie dat ik gewoon een verkeerd antwoord heb gegeven. Wat ik wil is dat dit afgedwongen wordt, zolang er een relatie is, dus dat het niet mag worden verwijderd als een hetzelfde cID voorkomt in beide gevallen. Dit heb ik nu dan ook en dit is ook waarnaar ik streef.

Deze is helder. Als nu eens in de documentatie kijkt, welke actie sluit dan het beste aan bij je update? Degene die je gekozen hebt past namelijk niet.

Ik denk dan dat mijn MySQL-code als volgt eruit moet komen te zien:

MySQL:

ALTER TABLE products ADD CONSTRAINT FK_cID FOREIGN KEY products(cID) REFERENCES credits(cID) ON DELETE RESTRICT ON UPDATE CASCADE;

Toelichting:
ON DELETE = Wanneer ik een rij probeer te verwijderen, terwijl er een relatie is, moet het "geblokkeerd" worden.
ON UPDATE = Wanneer een rij wordt bijgewerkt, moet het ook in alle andere tabellen worden aangepast, zolang er een relatie is.

[ Voor 10% gewijzigd door umask op 24-05-2019 10:06 ]

[umask]

Wat ik heel erg raar vind, is dat wanneer ik de bovenstaande MySQL code uitvoer en de cName van een record in credits verander, het niet wordt aangepast in products?

En wanneer ik het andersom wil doen (dus alter table = credits), dan krijg ik een foutmelding.

MySQL:

ALTER TABLE `credits` ADD  CONSTRAINT `test` FOREIGN KEY (`cName`) REFERENCES `products`(`cName`) ON DELETE RESTRICT ON UPDATE CASCADE;

MySQL Error
MySQL meldt: Documentatie

#1452 - Cannot add or update a child row: a foreign key constraint fails (`[...databasenaam...]`.`#sql-30d4_c81`, CONSTRAINT `test` FOREIGN KEY (`cName`) REFERENCES `products` (`cName`) ON UPDATE CASCADE)

Ook bij alter table products werkt het niet?

MySQL:

ALTER TABLE `products` ADD  CONSTRAINT `test` FOREIGN KEY (`cName`) REFERENCES `credits`(`cName`) ON DELETE RESTRICT ON UPDATE CASCADE;

MySQL Error
#1215 - Kan foreign key beperking niet toevoegen

Huidige situatie van database structuur:

Database structuur: products

Naam	Type	Collatie	Leeg	Standaardwaarde
pID (Primary Key) (Index)	int(11)	N/A	Nee	Geen
cID (Primary Key) (Index)	int(11)	N/A	Nee	Geen
pName	varchar(50)	latin1_swedish_ci	Ja	NULL

Database structuur: credits

Naam	Type	Collatie	Leeg	Standaardwaarde
cID (Primary Key) (Index)	int(11)	N/A	Nee	Geen
cName (Primary Key) (Index)	varchar(20)	latin1_swedish_ci	Nee	Geen

Toelichting primary key's:
Alle PK's zijn uniek, daarom dat ik ze ook een primary key heb gegeven.

PS. Ik heb een relevant kleine database en de inhoud van de tabellen valt ook wel mee. Is het alsnog nodig om indexes te gebruiken op de tabellen?

[ Voor 118% gewijzigd door umask op 24-05-2019 10:28 ]

[DataGhost]

Okee, dus onze originele aannames kloppen wel. Fijn, dan gaan we daarmee verder

Er bestaat helemaal geen cName in products dus ziet MySQL ook geen cName om aan te linken. De cName is ook geen key maar gewoon een property van credits. Je hebt een "primary key" om te zeggen "dit ding beschrijft/identificeert alle andere velden in deze rij". Je BSN is bijvoorbeeld de primary key (cID) van je naam, adres, geboortedatum (cName). Je gebruikt in products dus alleen je cID en de bijbehorende data (cName) haal je alleen maar op als dat nodig is in je presentatielaag met de join die je eerder gebruikt hebt. Die komt dus helemaal nooit in products te staan, maar de relatie is er wel via cID. Als je de cName verandert, verandert de cID niet, dus hoeft er in products niks te gebeuren. De FK ON UPDATE begint alleen maar te werken wanneer je credits.cID wijzigt, mocht dat ooit nodig zijn.

Misschien leg ik nu teveel uit, maar ik krijg het gevoel dat je met de verkeerde gedachtegang kijkt naar je database, en ik weet hoe ontzettend je daarin vast kan komen te zitten. Probeer er nu nog eens naar te kijken met fris inzicht.

Wat betreft de primary keys: een primary key is inderdaad uniek (wordt door de database enforced), maar je bereikt nu toch niet wat je wilt bereiken. Probeer maar eens:

code:

INSERT INTO credits (cID, cName) values (1, "Waa"), (2, "Waa")

Je kan maar 1 primary key per tabel hebben, en dat is nu ook het geval (huh? denk maar eens na ). Je kan wel laten forceren dat bepaalde kolommen uniek moeten zijn maar dat doe je niet op deze manier. Je zit wel in de buurt

En wat betreft de indexen: ze zijn nu "niet nodig" omdat je tabellen klein zijn inderdaad, maar als ze groter worden ga je het merken. Goede (!) indexen gebruiken doet eigenlijk performancetechnisch nooit pijn, ook niet op kleine tabellen, maar het zorgt ervoor dat de boel nog goed blijft presteren als er meer data in komt te zitten.

[ Voor 40% gewijzigd door DataGhost op 24-05-2019 12:08 ]

[CurlyMo]

umask schreef op vrijdag 24 mei 2019 @ 10:02:
[...]

Wacht even, ik zie dat ik gewoon een verkeerd antwoord heb gegeven. Wat ik wil is dat dit afgedwongen wordt, zolang er een relatie is, dus dat het niet mag worden verwijderd als een hetzelfde cID voorkomt in beide gevallen. Dit heb ik nu dan ook en dit is ook waarnaar ik streef.

Waar je rekening mee moet houden is dat een relatie altijd maar één kant op is. Dus als er een product is met een relatie naar credits, dan mag je dit product wel verwijderen. Je mag alleen de rij met de cID niet verwijderen zolang er vanuit products een relatie is.

Het is me alleen nog onduidelijk of je écht niet wil dat gegevens verwijderd kunnen worden of dat je niet wil dat er kapotte producten zijn. Oftewel, dat er geen producten mogen zijn zonder relatie naar credits.

Is het wel goed als je een cID in credits verwijderd, alle producten die met die credit in products ook worden verwijderd? Zo voorkom je ook dat je losse producten hebt zonder relatie naar credits.

Ik denk dan dat mijn MySQL-code als volgt eruit moet komen te zien:

Inderdaad beter, ik vraag me alleen nog af of je de on delete voldoende begrijpt.

umask schreef op vrijdag 24 mei 2019 @ 10:11:
Wat ik heel erg raar vind, is dat wanneer ik de bovenstaande MySQL code uitvoer en de cName van een record in credits verander, het niet wordt aangepast in products?

Klopt, omdat we al hadden afgesproken dat je van ons geen cName in products mag hebben Alles wat je probeert omdat toch weer te doen mag niet van ons, dus hoeven we ook niet na te denken over alle foutmeldingen die je krijgt om het toch te doen.

Foreign keys aanmaken op velden die niet aan beide kanten van de relatie bestaan kan dus niet.

PS. Ik heb een relevant kleine database en de inhoud van de tabellen valt ook wel mee. Is het alsnog nodig om indexes te gebruiken op de tabellen?

Dat is een heel ander onderwerp waar jij je nog even niet mee bezig moet houden

[umask]

DataGhost schreef op vrijdag 24 mei 2019 @ 11:56:
Okee, dus onze originele aannames kloppen wel. Fijn, dan gaan we daarmee verder

Er bestaat helemaal geen cName in products dus ziet MySQL ook geen cName om aan te linken. De cName is ook geen key maar gewoon een property van credits. Je hebt een "primary key" om te zeggen "dit ding beschrijft/identificeert alle andere velden in deze rij". Je BSN is bijvoorbeeld de primary key (cID) van je naam, adres, geboortedatum (cName). Je gebruikt in products dus alleen je cID en de bijbehorende data (cName) haal je alleen maar op als dat nodig is in je presentatielaag met de join die je eerder gebruikt hebt. Die komt dus helemaal nooit in products te staan, maar de relatie is er wel via cID. Als je de cName verandert, verandert de cID niet, dus hoeft er in products niks te gebeuren. De FK ON UPDATE begint alleen maar te werken wanneer je credits.cID wijzigt, mocht dat ooit nodig zijn.

Misschien leg ik nu teveel uit, maar ik krijg het gevoel dat je met de verkeerde gedachtegang kijkt naar je database, en ik weet hoe ontzettend je daarin vast kan komen te zitten. Probeer er nu nog eens naar te kijken met fris inzicht.

Wat betreft de primary keys: een primary key is inderdaad uniek (wordt door de database enforced), maar je bereikt nu toch niet wat je wilt bereiken. Probeer maar eens:

code:

INSERT INTO credits (cID, cName) values (1, "Waa"), (2, "Waa")

Je kan maar 1 primary key per tabel hebben, en dat is nu ook het geval (huh? denk maar eens na ). Je kan wel laten forceren dat bepaalde kolommen uniek moeten zijn maar dat doe je niet op deze manier. Je zit wel in de buurt

En wat betreft de indexen: ze zijn nu "niet nodig" omdat je tabellen klein zijn inderdaad, maar als ze groter worden ga je het merken. Goede (!) indexen gebruiken doet eigenlijk performancetechnisch nooit pijn, ook niet op kleine tabellen, maar het zorgt ervoor dat de boel nog goed blijft presteren als er meer data in komt te zitten.

Dank u wel voor de uitleg .

[umask]

CurlyMo schreef op vrijdag 24 mei 2019 @ 14:10:


Het is me alleen nog onduidelijk of je écht niet wil dat gegevens verwijderd kunnen worden of dat je niet wil dat er kapotte producten zijn. Oftewel, dat er geen producten mogen zijn zonder relatie naar credits.

Inderdaad, er mogen geen producten zijn zonder een relatie naar credits en dat gebeurt nu ook in mijn database.

Is het wel goed als je een cID in credits verwijderd, alle producten die met die credit in products ook worden verwijderd? Zo voorkom je ook dat je losse producten hebt zonder relatie naar credits.

Nee, dat wil ik weer niet. Als een credits verwijdert wordt, mag het product blijven bestaan, alleen dan krijgt de cName de waarde NULL.

Inderdaad beter, ik vraag me alleen nog af of je de on delete voldoende begrijpt.

Ik denk het wel. ON DELETE: als een record in de tabel wordt verwijdert, doe dan dit ... of dat ... in mijn geval RESTRICT. Het hoeft bij een delete geen acties te ondernemen.

Klopt, omdat we al hadden afgesproken dat je van ons geen cName in products mag hebben Alles wat je probeert omdat toch weer te doen mag niet van ons, dus hoeven we ook niet na te denken over alle foutmeldingen die je krijgt om het toch te doen.

Foreign keys aanmaken op velden die niet aan beide kanten van de relatie bestaan kan dus niet.

Oké, goed om te weten.

Dat is een heel ander onderwerp waar jij je nog even niet mee bezig moet houden

Dat is goed, bedankt voor uw uitleg.

[CurlyMo]

umask schreef op dinsdag 28 mei 2019 @ 08:56:
[...]

Inderdaad, er mogen geen producten zijn zonder een relatie naar credits en dat gebeurt nu ook in mijn database.

Is dat nog steeds afgedwongen met die triggers? Want die mogen van ons niet meer

Nee, dat wil ik weer niet. Als een credits verwijdert wordt, mag het product blijven bestaan, alleen dan krijgt de cName de waarde NULL.

Ik zie hier een tegenstrijdigheid in en ik denk dat dat komt doordat je een denkfout maakt. Om bij je laatste zin te beginnen. Wat je volgens mij wil kunnen is de waarde van een credit aanpassen of verwijderen zonder dat dit invloed heeft op products. Volgens mij heb jij het idee dat zodra je de relatie verbreekt, door cID in products op NULL te zetten je niet opnieuw een relatie kunt leggen met een nieuw record in credits, door weer het cID te vullen. Dat heeft functioneel namelijk hetzelfde effect dan je cName op NULL te zetten. In beide gevallen is er namelijk geen bekende cName waarde meer op te halen voor een specifiek product. Of dat nu komt omdat de cID leeg is en er dus geen relatie meer is, of omdat cName leeg is maakt daarin functioneel niet uit.

Oftewel, het is gangbaar om niet een relatie helemaal leeg te maken en als een soort placeholder te gebruiken, maar in die gevallen gewoon de relatie te verbreken door in dit geval de cID in products leeg te maken.

Ik denk het wel. ON DELETE: als een record in de tabel wordt verwijdert, doe dan dit ... of dat ... in mijn geval RESTRICT. Het hoeft bij een delete geen acties te ondernemen.

Hoe zie jij de actie 'cName op NULL zetten' dan. Dat lijkt me functioneel toch hetzelfde als de waarde verwijderen? In beide gevallen is cName niet meer bekend.

[Matis]

umask schreef op donderdag 23 mei 2019 @ 12:17:
Ok, ik zal het even proberen te beantwoorden:
1) Als ik een specifieke rij in credits wil verwijderen dan moet hij niks doen met alle rijen die ermee verbonden zijn in products, want ik wil nog dat de oude gegevens te lezen zijn in die andere tabel.

2) Als ik een specifieke rij in credits wil bijwerken dan moet hij alle rijen aanpassen die ermee verbonden zijn in products, want ik wil dat de andere waardes aangepast worden in products, zolang de rij in credits bestaat.

Dan kun je ook met "soft deletes" werken. Een vlag (of timestamp) die bijhoudt of/en wanneer de rij verwijderd is.

[DataGhost]

Matis schreef op dinsdag 28 mei 2019 @ 12:09:
[...]

Dan kun je ook met "soft deletes" werken. Een vlag (of timestamp) die bijhoudt of/en wanneer de rij verwijderd is.

TS heeft aangegeven dat zijn bewoording in die post verkeerd was en dat dat niet is wat 'ie wil

[umask]

CurlyMo schreef op dinsdag 28 mei 2019 @ 09:39:

Is dat nog steeds afgedwongen met die triggers? Want die mogen van ons niet meer

Nee, alle triggers heb ik verwijderd. Ik maak nu puur gebruik van C# code en MySQL.

Ik zie hier een tegenstrijdigheid in en ik denk dat dat komt doordat je een denkfout maakt. Om bij je laatste zin te beginnen. Wat je volgens mij wil kunnen is de waarde van een credit aanpassen of verwijderen zonder dat dit invloed heeft op products. Volgens mij heb jij het idee dat zodra je de relatie verbreekt, door cID in products op NULL te zetten je niet opnieuw een relatie kunt leggen met een nieuw record in credits, door weer het cID te vullen. Dat heeft functioneel namelijk hetzelfde effect dan je cName op NULL te zetten. In beide gevallen is er namelijk geen bekende cName waarde meer op te halen voor een specifiek product. Of dat nu komt omdat de cID leeg is en er dus geen relatie meer is, of omdat cName leeg is maakt daarin functioneel niet uit.

Oftewel, het is gangbaar om niet een relatie helemaal leeg te maken en als een soort placeholder te gebruiken, maar in die gevallen gewoon de relatie te verbreken door in dit geval de cID in products leeg te maken.

Hoe zie jij de actie 'cName op NULL zetten' dan. Dat lijkt me functioneel toch hetzelfde als de waarde verwijderen? In beide gevallen is cName niet meer bekend.

Sorry, ik had ook hier een verkeerd antwoord op gegeven. Ik dacht nog dat de cName in products bestond, maar die bestaat niet meer in products en alleen in credits om redundantie af te dwingen.

[CurlyMo]

@umask Om redundantie te voorkomen dus.

Maar als je een verkeerd antwoord hebt gegeven, is het dan een idee dat je nu het juiste antwoord geeft?

[umask]

CurlyMo schreef op woensdag 5 juni 2019 @ 08:49:
@umask Om redundantie te voorkomen dus.

Maar als je een verkeerd antwoord hebt gegeven, is het dan een idee dat je nu het juiste antwoord geeft?

Uw vraag was:

Hoe zie jij de actie 'cName op NULL zetten' dan. Dat lijkt me functioneel toch hetzelfde als de waarde verwijderen? In beide gevallen is cName niet meer bekend.

Ik had het namelijk over de products tabel, maar in dit geval heeft u het nog steeds over de credits tabel.. dus we zitten niet op één lijn en had ik daarom een verkeerd antwoord gegeven.. dus weet ik niet of het antwoord op deze vraag nog relevant is? Of wat voor een antwoord verwacht u, ik snap even niet wat voor een antwoord ik moet geven nu?

[CurlyMo]

umask schreef op woensdag 5 juni 2019 @ 21:39:
[...]

Ik reageerde met mijn verhaal op dit stukje:

umask schreef op dinsdag 28 mei 2019 @ 08:56:
[...]
Nee, dat wil ik weer niet. Als een credits verwijdert wordt, mag het product blijven bestaan, alleen dan krijgt de cName de waarde NULL.

Ik stel die gedachtegang ter discussie en heb geprobeerd toe te lichten waarom. Ik ben benieuwd of je dat hebt begrepen.

[ Voor 4% gewijzigd door CurlyMo op 05-06-2019 21:48 ]

[umask]

CurlyMo schreef op woensdag 5 juni 2019 @ 21:47:
Ik stel die gedachtegang ter discussie en heb geprobeerd toe te lichten waarom. Ik ben benieuwd of je dat hebt begrepen.

Ja, dit klopte niet. Foutje sorry... cName krijgt niet de waarde NULL.. dat is niet het geval

Het had eigenlijk moeten zijn:
Dan blijft de cID in products gewoon staan, alleen bestaat die dan niet in credits.. mocht ditzelfde nummer weer aangemaakt worden, dan krijgt het product de nieuwe naam.. dit is eigenlijk niet goed. Ik ben nu bezig dat als een record in credits wordt verwijdert, de cID in products op NULL moet worden gezet i.p.v. cName (dit had ik gezegd met als gedachte dat cName nog in products als kolom bestaat, maar dat is niet het geval, daarom had ik een foute antwoord gegeven).. zodat de gebruiker opnieuw kan aangeven wat de nieuwe cID is, want stel dat een nieuwe naam is gekoppeld aan het oude nummer, dan weten we zeker dat de gebruiker de juiste cName bij het product kan gebruiken bij het opnieuw aangeven zeg maar..

[ Voor 53% gewijzigd door umask op 06-06-2019 14:44 ]

[CurlyMo]

@umask Dan past de on delete restrict niet bij je doel

[umask]

CurlyMo schreef op donderdag 6 juni 2019 @ 17:28:
@umask Dan past de on delete restrict niet bij je doel

Oh oké.

MySQL:

ALTER TABLE products ADD CONSTRAINT FK_cID FOREIGN KEY products(cID) REFERENCES credits(cID) ON DELETE RESTRICT ON UPDATE CASCADE;

Wat zou de bovenstaande MySQL-code dan moeten zijn eigenlijk? Of was die zo goed?

[CurlyMo]

umask schreef op dinsdag 11 juni 2019 @ 21:19:
[...]
Wat zou de bovenstaande MySQL-code dan moeten zijn eigenlijk? Of was die zo goed?

Je zegt het zelf eigenlijk al:

umask schreef op donderdag 6 juni 2019 @ 14:37:
[...]
Ik ben nu bezig dat als een record in credits wordt verwijdert, de cID in products op NULL moet worden gezet

Dus je opties zijn:
- CASCADE
- SET NULL
- RESTRICT
- NO ACTION
- SET DEFAULT
https://dev.mysql.com/doc...e-table-foreign-keys.html

Het lijkt mij na jouw functionele beschrijving vrij logisch welke je moet hebben.

[umask]

CurlyMo schreef op dinsdag 11 juni 2019 @ 21:26:
[...]


Je zegt het zelf eigenlijk al:

[...]

Dus je opties zijn:
- CASCADE
- SET NULL
- RESTRICT
- NO ACTION
- SET DEFAULT
https://dev.mysql.com/doc...e-table-foreign-keys.html

Het lijkt mij na jouw functionele beschrijving vrij logisch welke je moet hebben.

Inderdaad, dank u wel. Ik waardeer het heel erg dat jullie mij hierbij hebben geholpen

[CurlyMo]

umask schreef op donderdag 13 juni 2019 @ 08:34:
[...]


Inderdaad, dank u wel. Ik waardeer het heel erg dat jullie mij hierbij hebben geholpen

Graag gedaan. Voor zekerheid is het wel goed als je nog even zegt welke actie je nu gekozen hebt

[ Voor 16% gewijzigd door CurlyMo op 13-06-2019 08:41 ]

[umask]

CurlyMo schreef op donderdag 13 juni 2019 @ 08:37:
[...]
Graag gedaan.

Nadat ik de MySQL-code uitvoer, dan wilt het maar niet lukken?

MySQL:

ALTER TABLE products ADD CONSTRAINT FK_cID FOREIGN KEY products(cID) REFERENCES credits(cID) ON DELETE SET NULL ON UPDATE CASCADE;

Error code:

#1215 - Kan foreign key beperking niet toevoegen

[CurlyMo]

umask schreef op donderdag 13 juni 2019 @ 08:41:
[...]

Nadat ik de MySQL-code uitvoer, dan wilt het maar niet lukken?

MySQL:

ALTER TABLE products ADD CONSTRAINT FK_cID FOREIGN KEY products(cID) REFERENCES credits(cID) ON DELETE SET NULL ON UPDATE CASCADE;

Error code:

#1215 - Kan foreign key beperking niet toevoegen

Kan het zijn dat je cID in products op NOT NULL hebt gezet?

Ik neem overigens aan dat je deze dingen eerst test voordat je ons bedankt voor onze hulp. Pas bij het testen weet je of het precies doet wat je zoekt. Niks let je om met dummy data om al die acties eens te testen en te zien wat ze in de praktijk betekenen.

[ Voor 21% gewijzigd door CurlyMo op 13-06-2019 08:48 ]

[umask]

CurlyMo schreef op donderdag 13 juni 2019 @ 08:45:
[...]

Kan het zijn dat je cID in products op NOT NULL hebt gezet?

Ik neem overigens aan dat je deze dingen eerst test voordat je ons bedankt voor onze hulp. Pas bij het testen weet je of het precies doet wat je zoekt. Niks let je om met dummy data om al die acties eens te testen en te zien wat ze in de praktijk betekenen.

Ja, dat was het probleem. de cID in products stond op NOT NULL oftewel Leeg? => Nee..
Hartstikke bedankt

Ja ik test het natuurlijk. Mijn excuses als ik even verder had gezocht, zou ik het antwoord zelf gevonden hebben. Ik probeer hier de volgende keer op te letten.

[ Voor 12% gewijzigd door umask op 13-06-2019 09:02 ]

[umask]

Ik zou graag iedereen willen bedanken die mij op het juiste spoor hebben gezet.
Jullie zijn echt heel goed bezig en ik vind het geweldig hoe jullie anderen op weg helpen.

Iedereen alvast een fijne vakantie toegewenst en heel erg bedankt!

[CurlyMo]

umask schreef op donderdag 13 juni 2019 @ 08:56:
[...]


Ja, dat was het probleem. de cID in products stond op NOT NULL oftewel Leeg? => Nee..
Hartstikke bedankt

Ik weet niet of het aan je beschrijving ligt en je dus hetzelfde bedoeld, maar je probleem was dus dat je de database tegenstrijdige informatie gaf.

Aan de ene kant gaf je aan dat cID op NULL gezet moet worden bij een foreign key verwijdering, aan de andere kant gaf je aan dat cID absoluut niet op NULL gezet mag worden. Dan denkt de database

[umask]

CurlyMo schreef op donderdag 13 juni 2019 @ 09:22:
[...]

Ik weet niet of het aan je beschrijving ligt en je dus hetzelfde bedoeld, maar je probleem was dus dat je de database tegenstrijdige informatie gaf.

Aan de ene kant gaf je aan dat cID op NULL gezet moet worden bij een foreign key verwijdering, aan de andere kant gaf je aan dat cID absoluut niet op NULL gezet mag worden. Dan denkt de database

Haha, inderdaad. Gelukkig werkt het nu goed en daar ben ik wel blij om.