[SQL] Twee foreign-keys: trigger?

[Night-Reveller]

Ik wil in Microsoft SQL Server 2000 een foreign key maken naar 2 kolommen, maar dat is niet toegestaan. Het probleem is wat uitgebreider, maar gegeneraliseerd komt het hier op neer; twee tabellen:

dbo.Rekening:
Rekeningnr (int, PK)
…

dbo.Instellingen
DefaultRekeningInkoop (int)
DefaultRekeningVerkoop (int)
…

Ik kan een Forgein Key maken van dbo.Rekening.Rekeningnr naar dbo.Instellingen.DefaultRekeningInkoop. Het fijne is dan dat als het rekeningnr wordt geupdate, dat dan dit wordt meegenomen in de instellingen. (Uiteraard On delete no action, anders ben ik straks al mijn instellingen kwijt )

Hetzelfde wil ik ook voor DefaultRekeningVerkoop, door nog een Foreign Key te definiëren van Rekeningnr naar DefaultRekeningVerkoop maar dit mag niet, want MSSQL geeft dan aan dat er dan een loop in de boom van cascaded updates zit (error: Introducing FOREIGN KEY constraint 'FK_Instellingen_Rekening1' on table 'Instellingen' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.). Jammer maar helaas, het is niet mogelijk

Volgens mij kan dit alleen opgelost worden door een trigger op dbo.Rekening te plaatsen. In een “For update” trigger op dbo.Rekening voer ik dan zelf de updates door (zie onderaan). Hiervoor moet ik de Foreign-Keys verwijderen, omdat anders de foreign-key de update ook wil doorvoeren (error: Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database).

Nu ik het probleem verder heb lopen specificeren om een mooie post te maken, heb ik het al uitgevoerd op de bovenstaande manier, omdat ik met een beetje testen het gewenste resultaat kreeg . Maar toch krijg ik een beetje jeuk:

Adequate oplossing?
Als ik beide DefaultX-kolommen door een trigger laat updaten, ziet niemand dat die velden in dbo.Instellingen afhankelijk zijn van dbo.Rekening. Ik moet dan de documentatie goed maken om te voorkomen dat mensen rare dingen gaan doen met de DefaultRekeningen.
Ook kan ik ervoor kiezen om voor DefaultRekeningInkoop een trigger te plaatsen en voor DefaultRekeningVerkoop de Foreign-key te laten staan, maar dan ben ik inconsitent bezig en zal ik het ook goed moeten documenteren. Hoe zal ik het aanpakken? Dit moet toch een vrij standaard probleem zijn?

Inconsitentie?
De database kan nu, vanwege het gebrek aan Foreign keys, inconsitente gegevens bevatten door in DefaultRekeningVerkoop een willekeurig nummer te plaatsen. Moet ik nou ook op dbo.Instellingen een trigger plaatsen die de inhoud vergelijkt met dbo.Rekening, of is er een andere weg mogelijk –misschien toch met Foreign keys-?

p.s. sinds deze week ben ik voor het eerst met triggers bezig geweest. Ik heb nu geen idee of ik ze op de juiste wijze toepas.

BTW: de trigger:

SQL:

CREATE TRIGGER [Update_Credit] ON [dbo].[Rekening] 
FOR UPDATE
AS

    Declare @OudCredit char(1),
        @OudRekeningnr int
    Select @OudCredit = Credit, @OudRekeningnr = Rekeningnr
    From Deleted

    Declare @NieuwCredit char(1),
        @NieuwRekeningnr int
    Select @NieuwCredit = Credit, @NieuwRekeningnr = Rekeningnr
    From Inserted

-- Updaten van De rekeningnrs. (Alle referenties (in dbo.Instellingen) naar een 
-- rekeningnr moeten hersteld worden)
if Update(Rekeningnr)
begin
    if @OudRekeningnr <> @NieuwRekeningnr
    begin
        -- dbo.Instellingen (DefaultVerkoopRekeningnr) eventueel updaten
        Update dbo.Instellingen
        Set DefaultVerkoopRekeningnr = @NieuwRekeningnr
        where DefaultVerkoopRekeningnr = @OudRekeningnr

        -- dbo.Instellingen (DefaultInkoopRekeningnr) eventueel updaten
        Update dbo.Instellingen
        Set DefaultInkoopRekeningnr = @NieuwRekeningnr
        where DefaultInkoopRekeningnr = @OudRekeningnr
        
    end

end

end

[ Voor 5% gewijzigd door drm op 08-07-2003 16:14 . Reden: leesbare tekst ]

[drm]

offtopic:
Heb even je post aangepast zodat de tekst zelf iig goed leesbaar is

[ACM]

hoe kan je nou een foreign key van 1 veld naar 2 andere hebben? Hoe wilde je dat mappen??

Als je dus die verkoop-default wijzigt veranderd je rekeningnr mee en als je die inkoop wijzigt wil je die rekening mee laten veranderen

Is zoiets dan niet wat netter als je perse naar een inkoop- en verkooprekening wilt kunnen verwijzen?
dbo.Rekening:
Rekeningnr (int, PK)
foreign key rekeningnr(instellingen.defaultrekening)
…

dbo.Instellingen
DefaultRekening (int)
RekeningType ...

[GraasGast]

[off-topic]
maak anders van die commentaar-regel 2 regels, dan is de layout niet zo fucked:

-- Updaten van De rekeningnrs. (Alle referenties (in dbo.Instellingen)
-- naar een rekeningnr moeten hersteld worden)
[/off-topic]

[Night-Reveller]

drm schreef op 08 July 2003 @ 15:49:

offtopic:
Heb even je post aangepast zodat de tekst zelf iig goed leesbaar is

Dank

GraasGast schreef op 08 July 2003 @ 15:57:
[off-topic]
maak anders van die commentaar-regel 2 regels, dan is de layout niet zo fucked:

-- Updaten van De rekeningnrs. (Alle referenties (in dbo.Instellingen)
-- naar een rekeningnr moeten hersteld worden)
[/off-topic]

Zo beter?

[ Voor 2% gewijzigd door Night-Reveller op 08-07-2003 16:01 . Reden: typo ]

[Night-Reveller]

ACM schreef op 08 juli 2003 @ 15:56:
hoe kan je nou een foreign key van 1 veld naar 2 andere hebben? Hoe wilde je dat mappen??

Als je dus die verkoop-default wijzigt veranderd je rekeningnr mee en als je die inkoop wijzigt wil je die rekening mee laten veranderen

Is zoiets dan niet wat netter als je perse naar een inkoop- en verkooprekening wilt kunnen verwijzen?
dbo.Rekening:
Rekeningnr (int, PK)
foreign key rekeningnr(instellingen.defaultrekening)
…

dbo.Instellingen
DefaultRekening (int)
RekeningType ...

Ik bedoel eigenlijk precies andersom. dbo.Rekening is de Primary Key tabel en dbo.Instelling de Foreign key tabel. Als ik rekeningnr 6010 (Debiteuren) een ander nummer geef, dan moet DefaultRekeningVerkoop ook mee veranderen. Hezelfde voor b.v. 7010 Crediteuren voor DefaultRekeningInkoop.
Verder is het gewoon mogelijk om 2 aparte Foreign keys te maken (eentje van Rekening naar verkoop en een van Rekening naar Inkoop), alleen zijn dan cascaded updates en deletes niet mogelijk.

Maareh, ik hoop dat het nu duidelijker is, want dit komt wel een paar keer voor in de tabellen. Misschien is deze wat tastbaarder;

dbo.Vlucht
VluchtID (int, PK)
Maatschappijcode(string)
Vluchtcode(string)
Vertrektijd(datetime)
Van(string)
Naar(string)
Aankomsttijd(datetime)

dbo.Vliegveld
Vliegveld(string, PK)

Elke vlucht gaat van een vliegveld naar een ander vliegveld, dus heb ik 2 foreign keys gedefinieerd die ervoor zorgen dat de vlucht van een bekende luchthaven naar een andere gaat. Maar updates zijn hier dus ook niet mogelijk (stel als iemand "shiphol" heeft ingevoerd en dit wil wijzigen)

Ik realiseer me nu ook, dat als ik (in dit voorbeeld dan) gebruik had gemaakt van auto-genummerde IDs, dat het hele probleem dan niet had bestaan (niemand die aan de IDs hoeft te zitten).

[ Voor 31% gewijzigd door Night-Reveller op 08-07-2003 16:25 ]

[Annie]

Night-Reveller schreef op 08 July 2003 @ 15:33:
p.s. sinds deze week ben ik voor het eerst met triggers bezig geweest. Ik heb nu geen idee of ik ze op de juiste wijze toepas.

Tip (even los van het probleem):
Probeer bij triggers rekening te houden met het feit dat er meerdere rows in deleted of inserted kunnen zitten.

[Night-Reveller]

Annie schreef op 08 July 2003 @ 18:08:
[...]

Tip (even los van het probleem):
Probeer bij triggers rekening te houden met het feit dat er meerdere rows in deleted of inserted kunnen zitten.

Dank je voor de tip Maar er kunnen toch niet meerdere rows tegelijk via de applicatie worden verwijderd. Mocht het dan toch gebeuren, ben ik redelijk zuur

[ACM]

Ow je bedoelde ze zo

Maar volgens SQL-server mag dit dus niet:

SQL:

create table rekening
(
  rekeningnr integer primary key,
  ...
)

create table instellingen
(
    defaultrekeningverkoop integer references rekening(rekeningnr),
    defaultrekeninginkoop integer references rekening(rekeningnr),
    ...
)

Rare beperking van SQL-server dan..

[Annie]

ACM schreef op 08 juli 2003 @ 21:24:
Maar volgens SQL-server mag dit dus niet:
[ code ]

Jawel, dat is geen enkel probleem. Het probleem is dat mssql niet toestaat dat beide constraints een cascade krijgen omdat deze dan problemen kunnen geven.

De data integriteit is dus wel gewaarborgd. Om ervoor te zorgen dat je ook je data in de Instellingen table wijzigt kan je kiezen om deze bewerkingen altijd via een stored procedure te laten verlopen (evt. update permissions revoken) of middels een trigger.

[ACM]

Annie schreef op 08 juli 2003 @ 23:01:
Jawel, dat is geen enkel probleem. Het probleem is dat mssql niet toestaat dat beide constraints een cascade krijgen omdat deze dan problemen kunnen geven.

Maar waarom dan? Wat voor problemen kan dat opleveren? (en het is niet zo dat ik denk dat er geen problemen op kunnen treden, maar gewoon niet snap welke er dan op zouden kunnen treden )

De data integriteit is dus wel gewaarborgd. Om ervoor te zorgen dat je ook je data in de Instellingen table wijzigt kan je kiezen om deze bewerkingen altijd via een stored procedure te laten verlopen (evt. update permissions revoken) of middels een trigger.

En is het daar dan niet zo dat je in vervelende situaties kan komen ala:
Je wilt de rekening wijzigen, maar omdat er references naar bestaan mag dat niet, maar je kan ook de instellingen nog niet wijzigen, want de rekening bestaat nog niet. Dan zou je dus een insert-in-rekening moeten doen, dan een update van de instellingen en dan de delete van de dubbele/oude rekening?

[Annie]

ACM schreef op 08 July 2003 @ 23:28:
[...]

Maar waarom dan? Wat voor problemen kan dat opleveren? (en het is niet zo dat ik denk dat er geen problemen op kunnen treden, maar gewoon niet snap welke er dan op zouden kunnen treden )

Uhh, niet van die moeilijke vragen 's ochtends
Dit zal wel te maken hebben met de implementatie van de cascaded updates binnen SQL Server. Alhoewel ik in eerste instantie ook niet echt zie waar het probleem ligt. Als je twee tables met beide 1 reference hebt dan mag het ineens wel.
Iemand die het weet mag het roepen Als m'n grijze massa over een paar uur op kruissnelheid is probeer ik het nog wel een keer te begrijpen.

ACM schreef op 08 July 2003 @ 23:28:
[...]

En is het daar dan niet zo dat je in vervelende situaties kan komen ala:
Je wilt de rekening wijzigen, maar omdat er references naar bestaan mag dat niet, maar je kan ook de instellingen nog niet wijzigen, want de rekening bestaat nog niet. Dan zou je dus een insert-in-rekening moeten doen, dan een update van de instellingen en dan de delete van de dubbele/oude rekening?

Si. Maar in feite doe je dan gewoon zelf wat het systeem normaalgesproken voor je zou doen met een cascade update (misschien via een iets andere route).
Overigens kan je hiervoor gewoon update blijven gebruiken icm een 'instead of' trigger.

[ Voor 5% gewijzigd door Annie op 09-07-2003 07:07 ]

[Night-Reveller]

Annie schreef op 08 juli 2003 @ 23:01:
[...]

De data integriteit is dus wel gewaarborgd. Om ervoor te zorgen dat je ook je data in de Instellingen table wijzigt kan je kiezen om deze bewerkingen altijd via een stored procedure te laten verlopen (evt. update permissions revoken) of middels een trigger.

Aha! Ik ben dus op de goede weg . Kan een trigger dan ook bestaan icm een foreign key? Ik kreeg een error omdat de trigger het record al had geupdate waarna de foreign key zelf de update nog eens dunnetjes over wilde doen, wat natuurlijk niet meer kon. Hoe moet ik dit dan doen?

ACM: Cascading Foreign keys mogen niet in een loopje zitten (kolom x.a wijst naar y.b, y.b wijst weer naar x.a etc). Daarom bouwt MSSQL een tree op per cascading foreign key en elke tabel mag daar maar 1 keer in voorkomen. Helaas is hiermee ook voor mij de weg afgesneden voor een prima oplossing .

[ Voor 3% gewijzigd door Night-Reveller op 09-07-2003 12:11 . Reden: technische correctie :) ]

[ACM]

Night-Reveller schreef op 09 juli 2003 @ 12:10:
ACM: Cascading Foreign keys mogen niet in een loopje zitten (kolom x.a wijst naar y.b, y.b wijst weer naar x.a etc). Daarom bouwt MSSQL een tree op per cascading foreign key en elke tabel mag daar maar 1 keer in voorkomen. Helaas is hiermee ook voor mij de weg afgesneden voor een prima oplossing .

Lekker handig...

Jij wilt toch x.a die naar y.b wijst en x.b die naar y.b wijst Ik snap niet zo goed waarom MSSQL daar zo moeilijk om doet, want je hebt helemaal geen circulaire definitie of ik begrijp gewoon nog steeds niet wat je precies bedoelt of je doet het fout

[Night-Reveller]

ACM schreef op 09 juli 2003 @ 12:20:
[...]

Lekker handig...

Jij wilt toch x.a die naar y.b wijst en x.b die naar y.b wijst Ik snap niet zo goed waarom MSSQL daar zo moeilijk om doet, want je hebt helemaal geen circulaire definitie of ik begrijp gewoon nog steeds niet wat je precies bedoelt of je doet het fout

Hehehe, we bedoelen hetzelfde, maar zeggen het anders . Ik heb idd geen loop in m'n foreign keys zitten. Maar MS heeft een verkeerde oplossing bedacht om loopjes met foreign keys te detecteren.

Verder lul ik soms teveel, dus nu zo kort als ik zou durven (ik ben wetenschapper in spe , dus dat is eng) mijn kernpunt;
*Ik wil een foreign-key maken van Rekening (primairy) naar DefaultRekeningVerkoop EN -Inkoop.

De oplossing ligt, zoals annie aangaf, in het maken van een trigger op dbo.Rekening.

Dan mijn vervolg vragen:
* Kan die trigger icm een foreign key (voor de consitentie)?
* Moet er een een extra trigger op dbo.Instellingen (voor de consitentie)?

[Annie]

* Er is geen extra trigger nodig op Instellingen, de consistentie waarborg je met de 2 FK constraints.
* Met een 'instead of' trigger kan je in plaats van de update (de naam zegt het al) de situatie namaken die ACM schetst. Namelijk een insert in Rekening, update op Instellingen en een delete van de oude waarde in Rekening.

offtopic:
whoops, 5 minuutjes te laat voor een meeting, gotta run

[Night-Reveller]

Das fijn om te lezen . Je zegt in feite dat de 2 Foreign keys kunnen blijven en ik moet een instead-of trigger maken op Rekening (klopt dat?). Okidokie, can do that. Sterker nog; already tried that. Maar dat is met een for-update geweest, wat niet werkte.

[Annie]

Ja dat klopt helemaal. Maar als ik me niet vergis moet een dat ook werken met een update.

Even uit m'n hoofd (maar als ik jou was zou ik meer vertrouwen op de BoL ):

SQL:

CREATE TRIGGER DoeDitInPlaatsVanEenUpdateOpRekening ON Rekening
INSTEAD OF UPDATE
AS
  -- blabla

[EfBe]

Meerdere cascading updates op 1 row werkt natuurlijk niet omdat bij de 1e update de row gelockt wordt en bij de 2e update er dus niets kan worden geupdate. (hij doet geen multiupdate in 1 row).

Een foreign key constraint loopt altijd van de fk naar de pk. Zodoende kom je niet in de war wanneer je zegt dat je 2 fks' op een field wil definieren, want dat kan nl. niet (1 fk field en 2 pk fields).

Het is verder een doodzonde om PK fields te wijzigen. Een field is niet voor niets een PK field en het wijzigen van een PK field houdt in dat een entity (dat is de verzameling van de attributes die uniek geidentificeerd worden door de PK) ophoudt te bestaan en opnieuw wordt toegevoegd, en wel met een nieuwe PK. Wijzigen van een PK field is dus not done, je moet de row verwijderen en opnieuw toevoegen met de nieuwe PK waarde. Iedere andere oplossing is, sorry, geknoei in de ruimte en leidt tot broddelwerk en daaraan gerelateerde fouten. Niet doen dus.

[Night-Reveller]

Waar staat dat het wijzigen van de waarde van een PK niet mag? Een PK is alleen een sleutel om een veld uniek aan te kunnen wijzen. dbo.Rekening bevat de rekeningnrs die gebruikt worden voor een boekhouding (b.v. debiteuren, crediteuren, liquide middelen, etc). Al deze rekeningnrs zijn uniek en als de boekhouder besluit om ze te hernummeren, dan mag dat, als hij ze maar uniek laat.

(bovendien wordt intern bij een update sowieso eerst het record verwijderd en dan toegevoegd, vanwege de tabellen inserted en deleted).

Anders gezegd: Als je de waarde van een PK niet zou mogen wijzigen, dan zou een cascaded update uberhaupt ook niet mogen! Wat zou dan het nut zijn van een cascaded update?

[Night-Reveller]

Annie schreef op 09 July 2003 @ 15:06:
Ja dat klopt helemaal. Maar als ik me niet vergis moet een dat ook werken met een update.

Even uit m'n hoofd (maar als ik jou was zou ik meer vertrouwen op de BoL ):

SQL:

CREATE TRIGGER DoeDitInPlaatsVanEenUpdateOpRekening ON Rekening
INSTEAD OF UPDATE
AS
  -- blabla

Correctemundo!

[ACM]

EfBe zegt ook niet dat het niet mag, maar wel dat het niet handig is. Een PK zou in principe nooit gewijzigd hoeven te worden, tenzij er zich een uitzonderlijke situatie voordoet.

Als jij dus van plan bent je PK (vaak) te gaan wijzigen, dan is het eigenlijk geen PK meer of zou je het iig niet als PK moeten aanmerken.

Althans, dat is de standaard gedachte over de PK, afaik.

[Night-Reveller]

Oh OK. Dan is er geen probleem. Rekeningnrs zullen bijna nooit gewijzigd moeten worden; een boekhouder gaat niet regelmatig rekeningen zitten hernummeren, hooguit een keer vlak nadat hij nieuw is aangenomen

Triggers die vaak worden aangeroepen dienen uiterst efficient geschreven te worden, omdat anders de performance sterk negatief beinvloed wordt. Maar omdat dit hoogstzelden gebeurt hoef ik me hier niet zoveel zorgen over te maken (In werkelijkheid moet bij een update op rekening er veel meer gebeuren).

[EfBe]

Je kunt stellen dat omdat het mogelijk is dat rekeningnummer kan worden gewijzigd, je rekeningnummer niet als PK kan gebruiken, immers het is niet een uniek identificerend gegeven.

Wellicht theoretisch geneuzel, maar het is IMHO niet meer dan normaal dat een software developer deze dingen weet en ermee werkt en dus geen software bouwt die gebaseerd is op aannames als "het mag best want hier werkt het vast goed en ik denk dat het wel meevalt".

Net zoals het feit dat SqlServer je vrolijk 2 of meer Foreign Key relations laat definieren op 1 FK field. Dit is semantisch onjuist, sterker, gewoon fout, maar het kan wel.

[Night-Reveller]

Keb net met reptile209 wat gepraat, o.a. ex-penningmeester met ervaring in Unit4 (boekhoudprog). Hierin is het mogelijk om na het afsluiten van een boekjaar het rekening nr te 'deleten'. Je kan het rekeningnr dan niet meer selecteren in de boekhouding (komt niet meer voor in het lijstje), terwijl, als je de gegevens van vorig jaar bekijkt het nr er (uiteraard) nog tussen staat. Conclusie: het rekeningnr wordt niet echt gedelete, maar uit het lijstje verwijderd.

Geinig is dat je in het nieuwe jaar dan vrolijk een nieuw rekeningnr kan aanmaken met hetzelfde nr als dat je vorig jaar hebt gebruikt (maar eventueel iets heel anders betekent!!!). Conclusie: unit4 heeft niet rekeningnr als PK, maar een ID als PK. Is dit dan correct?

Maar terug op FK's: Waarom zou het semantisch onjuist zijn? Als ik rekeningnr update dient hij in een andere tabel twee kolommen te controleren op aanwezigheid van dat rekeningnr en dienovereenkomstig ook moet wijzigen. Ik ben het niet met je eens, nofi, over de bouw van software. Met de eindgebruiker in mind is het ook volstrekt logisch dat een rekeningnr uniek is (dat er een ID oid. achterhangt zal hem een wordt wezen) en het imho prima als PK gebruikt mag worden.

[drm]

Geinig is dat je in het nieuwe jaar dan vrolijk een nieuw rekeningnr kan aanmaken met hetzelfde nr als dat je vorig jaar hebt gebruikt (maar eventueel iets heel anders betekent!!!). Conclusie: unit4 heeft niet rekeningnr als PK, maar een ID als PK. Is dit dan correct?

Is dat geinig? Dat is imo klinkklare onzin. Waarom zou je in vredesnaam iets wat gewoon uniek is 2x aan moeten kunnen maken Dan ga je volledig voorbij aan de functie en de meest belangrijke eigenschap van een primaire sleutel: het identificeren van een record en daarmee uniek zijn.

---

edit:

Never mind, ik lees weer als een kruk

---

[q]Maar terug op FK's: Waarom zou het semantisch onjuist zijn? Als ik rekeningnr update dient hij in een andere tabel twee kolommen te controleren op aanwezigheid van dat rekeningnr en dienovereenkomstig ook moet wijzigen.[/]Leg mij eens uit hoe en waarom een rekeningnummer 2x een relatie beschrijft naar een tabel Dan zijn er 2 mogelijkheden:
1. Je hebt 2x een tabel met primary key rekeningnummer: fout.
2. Je hebt 2x een rekeningnummer-veld in een tabel die geen koppeltabel is*, hoe kunnen die velden dan PK zijn Een FK verwijst tenslotte altijd naar een primary key of er is iets grondig mis...

_{*) Bij een koppeltabel tussen rekeningnummer kan om precies de reden als 1 alleen maar een relatie van 1 tabel naar dezelfde tabel beschrijven. Ik zou niet weten waarom je dat zou willen, maar goed}

Of ik snap niet waar de discussie over gaat, dat kan ook nog

[ Voor 3% gewijzigd door drm op 10-07-2003 13:29 ]

[ACM]

drm schreef op 10 July 2003 @ 13:24:
Is dat geinig? Dat is imo klinkklare onzin. Waarom zou je in vredesnaam iets wat gewoon uniek is 2x aan moeten kunnen maken Dan ga je volledig voorbij aan de functie en de meest belangrijke eigenschap van een primaire sleutel: het identificeren van een record en daarmee uniek zijn.

Lol, zij gebruiken het dus blijkbaar (correct) niet als primary key, maar als een of ander veld dat eventueel gecombineerd met een jaartal uniek is oid.

1. Je hebt 2x een tabel met primary key rekeningnummer: fout.

Een foreign key is toch ook een relatie? En dan is er echt niet perse sprake van een 2-tal primary keys met rekeningnr als waarde.
Het is, normaliserend gezien, wel zo verstandig het rekeningnummer maar op 1 plaats te hebben en verder met unieke, niet wijzigende, keys te werken.

2. Je hebt 2x een rekeningnummer-veld in een tabel die geen koppeltabel is*, hoe kunnen die velden dan PK zijn Een FK verwijst tenslotte altijd naar een primary key of er is iets grondig mis...

Euhm je mist dus nog de stap dat er een primary key is bij A en een FK bij B is...
En dan is het dus de vraag of een rekeningnr een primary key mag zijn, voor een bank wellicht wel. Voor jouw applicatie waarschijnlijk ook wel, maar is het wellicht niet de handigste keuze.

Of ik snap niet waar de discussie over gaat, dat kan ook nog

Zullen we het daar maar op houden dan?

[Night-Reveller]

drm schreef op 10 July 2003 @ 13:24:
[...]
Is dat geinig? Dat is imo klinkklare onzin. Waarom zou je in vredesnaam iets wat gewoon uniek is 2x aan moeten kunnen maken Dan ga je volledig voorbij aan de functie en de meest belangrijke eigenschap van een primaire sleutel: het identificeren van een record en daarmee uniek zijn.

psies. Unit4 is niet echt goed geprogrammeerd met de eindgebruiker in het hoofd; een rekeningnr in Unit4 is niet uniek!

[...]
Leg mij eens uit hoe en waarom een rekeningnummer 2x een relatie beschrijft naar een tabel Dan zijn er 2 mogelijkheden:
1. Je hebt 2x een tabel met primary key rekeningnummer: fout.
2. Je hebt 2x een rekeningnummer-veld in een tabel die geen koppeltabel is*, hoe kunnen die velden dan PK zijn Een FK verwijst tenslotte altijd naar een primary key of er is iets grondig mis...

_{*) Bij een koppeltabel tussen rekeningnummer kan om precies de reden als 1 alleen maar een relatie van 1 tabel naar dezelfde tabel beschrijven. Ik zou niet weten waarom je dat zou willen, maar goed}

Of ik snap niet waar de discussie over gaat, dat kan ook nog

Hehehe: Zelf vind ik het ook niet altijd even makkelijk om alles duidelijk uit te leggen. aan drm, ACM, Annie, EfBe en iedereen die ik vergeten ben voor het geduld en meedoen in de discussie.

Omdat een plaatje meer zegt dan 1000 woorden. De foreign key naar van ( ) DefaultInkoopRekening:


De foreign key van DefaultVerkoopRekening:


En het geheel ziet er dan zo uit in het diagram (de overige kolommen zijn er niet om af te leiden, daar gaan we het dus niet over hebben):


De bedoeling is dat ik DefaultInkoopRekening en DefaultVerkoopRekening automatisch wil laten updaten, zodra Rekeningnr dat doet. Volgens Annie met een trigger, maar ben ik er dan?

[ Voor 26% gewijzigd door Night-Reveller op 10-07-2003 17:17 . Reden: plaatjes verbeterd ]

[ACM]

Night-Reveller schreef op 10 July 2003 @ 13:34:
psies. Unit4 is niet echt goed geprogrammeerd met de eindgebruiker in het hoofd; een rekeningnr in Unit4 is niet uniek!

Met het verhaal dat je zelf hierboven geeft vind ik het juist wel correct

Hoe kan je anders een rekening uit je boekhouding verwijderen en een jaar lang niet gebruiken, zonder dat je het risico loopt dat je oude gegevens kwijtraakt en dat je nooit meer iets met die rekening kan doen?

[JaQ]

Night-Reveller schreef op 10 July 2003 @ 13:07:
Keb net met reptile209 wat gepraat, o.a. ex-penningmeester met ervaring in Unit4 (boekhoudprog). Hierin is het mogelijk om na het afsluiten van een boekjaar het rekening nr te 'deleten'. Je kan het rekeningnr dan niet meer selecteren in de boekhouding (komt niet meer voor in het lijstje), terwijl, als je de gegevens van vorig jaar bekijkt het nr er (uiteraard) nog tussen staat. Conclusie: het rekeningnr wordt niet echt gedelete, maar uit het lijstje verwijder.

Dat noemen ze nou een "deleted_flag". Delete wil je uberhaupt niet zomaar want je loopt risico op "wezen" en als je het verkeerde delete kan je goed in de shit komen. Zeker met die fijne cascades die hier voorgesteld zijn. (Stel je voor, hoogste niveau = rekeningnr. Ik verwijderd die.. wat gebeurd er met alles wat aan die rekening hangt... precies.. daarom dus een vlaggetje)

Maar goed, dat alles offtopic.

De rest van het topic snap ik niet zo goed, maar misschien dat ik de uitleg gewoon niet goed interpreteer of zo. (ik meende toch wel e.e.a. van databases af te weten, maar goed)

[Night-Reveller]

ACM schreef op 10 July 2003 @ 13:36:
[...]

Met het verhaal dat je zelf hierboven geeft vind ik het juist wel correct

Hoe kan je anders een rekening uit je boekhouding verwijderen en een jaar lang niet gebruiken, zonder dat je het risico loopt dat je oude gegevens kwijtraakt en dat je nooit meer iets met die rekening kan doen?

Klopt: niet verwijderen dus.

De boekhouder zou dit uiteraard wel willen en daarom kan je, zoals DrFrankenstoner zegt, een flag zetten. (Check! zie dbo.Rekening.Actief). Zo kan je in het onderhoudsscherm voor de rekening (waarin de naam etc gewijzigd kan worden) de rekening de-/activeren en het gevolg voor de boekhoudmodule (waarom de regeltjes worden getypt) is dat de rekening niet/wel in een lijstje van boekbare rekeningnrs komt.

[EfBe]

Night-Reveller schreef op 10 July 2003 @ 13:07:
Keb net met reptile209 wat gepraat, o.a. ex-penningmeester met ervaring in Unit4 (boekhoudprog). Hierin is het mogelijk om na het afsluiten van een boekjaar het rekening nr te 'deleten'. Je kan het rekeningnr dan niet meer selecteren in de boekhouding (komt niet meer voor in het lijstje), terwijl, als je de gegevens van vorig jaar bekijkt het nr er (uiteraard) nog tussen staat. Conclusie: het rekeningnr wordt niet echt gedelete, maar uit het lijstje verwijderd.
Geinig is dat je in het nieuwe jaar dan vrolijk een nieuw rekeningnr kan aanmaken met hetzelfde nr als dat je vorig jaar hebt gebruikt (maar eventueel iets heel anders betekent!!!). Conclusie: unit4 heeft niet rekeningnr als PK, maar een ID als PK. Is dit dan correct?

Je gebruikt dan iig een uniek identificerend gegeven, dus het is zeker correct.

Maar terug op FK's: Waarom zou het semantisch onjuist zijn? Als ik rekeningnr update dient hij in een andere tabel twee kolommen te controleren op aanwezigheid van dat rekeningnr en dienovereenkomstig ook moet wijzigen.

Je hebt mn vorige post in deze thread niet gelezen. Jij definieert een FK constraint vanaf de PK. Dat is niet zo, hij loopt vanaf de FK, immers de FK velden wijzen naar PK velden -> de FK veldwaarden moeten als PK voorkomen in de PK velden behorende bij de FK.

Het is dus fout om vanaf een FK field (dus bv CustomerID in 'Orders') 2 of meer FK constraints te definieren, want je hebt dan 2 of meer PK fields die dezelfde waarde hebben, maar in verschillende tabellen maar WEL semantisch hetzelfde betekenen, immers anders had je niet vanaf 1 FK field 2 FK constraints.

Ik ben het niet met je eens, nofi, over de bouw van software. Met de eindgebruiker in mind is het ook volstrekt logisch dat een rekeningnr uniek is (dat er een ID oid. achterhangt zal hem een wordt wezen) en het imho prima als PK gebruikt mag worden.

Wat de eindgebruiker ziet/denkt is totaal irrelevant voor je datamodel. Het datamodel is bedoeld om de data gebruikt in de applicatie correct op te slaan op een consistente, non-redundante wijze. Hoe, is voor de gebruiker niet interessant, daar is het stuk code wat tussen de data en zn muispointer zit voor bedoeld, hetgeen jij moet maken.

[Night-Reveller]

EfBe schreef op 10 July 2003 @ 15:07:
[...]
Je hebt mn vorige post in deze thread niet gelezen. Jij definieert een FK constraint vanaf de PK. Dat is niet zo, hij loopt vanaf de FK, immers de FK velden wijzen naar PK velden -> de FK veldwaarden moeten als PK voorkomen in de PK velden behorende bij de FK.

Ik heb plaatjes bijgevoegd zodat het e.e.a. duidelijk is.

Het is dus fout om vanaf een FK field (dus bv CustomerID in 'Orders') 2 of meer FK constraints te definieren, want je hebt dan 2 of meer PK fields die dezelfde waarde hebben, maar in verschillende tabellen maar WEL semantisch hetzelfde betekenen, immers anders had je niet vanaf 1 FK field 2 FK constraints.

offtopic:
Ik heb het voor de gein even getest met Northwind. Een FK field als CustomerID in 'Orders', kan idd naar PK's uit twee verschillende tabellen wijzen. (Maar dan moeten de waarden in het FK field natuurlijk wel in beide tabellen staan, anders wordt een van de twee FK constraints overtreden). Wist ik niet eens, wel leuk.

Terug naar Annie:

Annie schreef op 09 juli 2003 @ 13:06:
* Er is geen extra trigger nodig op Instellingen, de consistentie waarborg je met de 2 FK constraints.
* Met een 'instead of' trigger kan je in plaats van de update (de naam zegt het al) de situatie namaken die ACM schetst. Namelijk een insert in Rekening, update op Instellingen en een delete van de oude waarde in Rekening.

Ik heb eindelijk tijd gehad om de trigger 'instead of' te maken, maar dat geeft niet het gewenste resultaat. Deze Trigger:

code:

CREATE TRIGGER [Update_Credit] ON [dbo].[Rekening] 
INSTEAD OF UPDATE
AS
    Declare @OudCredit char(1),
        @OudRekeningnr int
    Select @OudCredit = Credit, @OudRekeningnr = Rekeningnr
    From Deleted

    Declare @NieuwCredit char(1),
        @NieuwRekeningnr int
    Select @NieuwCredit = Credit, @NieuwRekeningnr = Rekeningnr
    From Inserted

if Update(Rekeningnr)
begin
    if @OudRekeningnr <> @NieuwRekeningnr
    begin
        Update dbo.Instellingen
        Set DefaultVerkoopRekeningnr = @NieuwRekeningnr
        where DefaultVerkoopRekeningnr = @OudRekeningnr
        Raiserror('foutje', 16,1)

        Update dbo.Instellingen
        Set DefaultInkoopRekeningnr = @NieuwRekeningnr
        where DefaultInkoopRekeningnr = @OudRekeningnr
    end
end

geeft icm foreign keys (zoals ze in de plaatjes staan (snel even aangepast om het geheel nog duidelijker te maken dan het al is )), geeft de error: "Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database" (een database error dat het update statement conflicteerd met de foreign key FK_Instellingen_RekeningenVerkoop). De "Raiserror('foutje', 16,1)" in de trigger hebben jullie vast al wel gezien, maar daar komt ie dus niet.

M.a.w. ondanks dat het een 'instead of' trigger is kan ik tijdens de update actie niet dbo.Instellingen updaten, zodat die weer zou kloppen met de nieuwe PK waarde in Rekening. Is ook wel logisch: ook tijdens het uitvoeren van een trigger moeten Foreign key constraints gehandhaafd blijven.

Weet een van jullie toevallig of locks dan nog wat zouden kunnen uitmaken om tijdelijk een FK constraint te omzeilen? Nee zeker? Als dat idd niet zo is, ben ik bang dat het niet met Foreign keys gaat en zou ik cascading updates en de foreign keys zelf geheel moeten simuleren mbv triggers, zonder Foreign keys dus .

Constraints (als in 'Rekening > 0') wordt gecontroleerd na het uitvoeren van een 'instead of update' trigger, maar voor een 'after update'. Een allerlaatste mogelijkheid voor mij is dat hopelijk zoiets ook met Foreign Keys wordt gedaan. Iemand een idee? Nee zeker?

[ Voor 4% gewijzigd door Night-Reveller op 10-07-2003 18:06 . Reden: typo ]

[Annie]

Ik heb het niet uitvoerig getest, maar onderstaande komt denk ik al een aardig eind in de buurt.

SQL:

/* Cascading update van Rekingnr in dbo.Instellingen */
CREATE TRIGGER Update_Credit ON dbo.Rekening 
INSTEAD OF UPDATE
AS
  SET XACT_ABORT ON
  BEGIN TRAN
  
  DECLARE @NieuwRekeningnr INT, @OudRekeningnr INT
  
  SELECT @NieuwRekeningnr = Rekeningnr FROM inserted
  SELECT @OudRekeningnr = Rekeningnr FROM deleted
  
  IF UPDATE(Rekeningnr) AND @OudRekeningnr <> @NieuwRekeningnr BEGIN
    /* insert nieuwe waarde van Rekeningnr in dbo.Rekening */
    INSERT INTO dbo.Rekening
    SELECT Rekeningnr, Credit, Omschrijving, enz
    FROM inserted
    
    /* update dbo.Instellingen met nieuwe waarde van Rekeningnr */
    UPDATE dbo.Instellingen
    SET DefaultVerkoopRekeningnr = @NieuwRekeningnr
    WHERE DefaultVerkoopRekeningnr = @OudRekeningnr
    
    UPDATE dbo.Instellingen
    SET DefaultInkoopRekeningnr = @NieuwRekeningnr
    WHERE DefaultInkoopRekeningnr = @OudRekeningnr
    
    /* delete oude waarde van Rekeningnr uit dbo.Rekening */
    DELETE FROM dbo.Rekening
    WHERE Rekeningnr = @OudRekeningnr
  END
  ELSE BEGIN
    UPDATE dbo.Rekening
    SET Credit = i.Credit, Omschrijving = i.Omschrijving, enz
    FROM inserted i
    JOIN dbo.Rekening r
      ON r.Rekeningnr = i.Rekeningnr
  END
  
  COMMIT TRAN
GO

[Night-Reveller]

Hé, dat zou wel eens heel goed kunnen werken! Zal ik strax uittesten, maareh alvast complimenten .

[Night-Reveller]

* even een kickje om Annie te bedanken!

Go Annie, Go Annie!!!

Oplossing
Het idee om eerst een nieuw record te inserten, de boel updaten naar het nieuwe record, en weer het eerste record weer te verwijderen was idd. de oplossing.
Omdat een record met de nieuwe waarde en de oude waarde tijdens de trigger bestaan, wordt er continu aan Foreign Key constraints voldaan.

Duizend maal dank .