[SQL / MSSQL 2005] referentiele integriteit controleren

[Verwijderd]

Ik heb de volgende stored procedure:

code:

create procedure DeleteStuksType
    @solutionID smallint,
    @stuksTypeID int
as
begin
    delete from tblStuksType where solutionID = @solutionID and stukstypeID = @stuksTypeID
end

deze 'stuks typen' worden door enkele (en in de toekomst meer) tabellen gebruikt.

Het verwijderen van een stukstype kan dus alleen als deze niet gebruikt wordt door een andere tabel. Dit is netjes opgelost d.m.v. foreign keys e.d.

Als deze procedure wordt aangeroepen, krijg je netjes een (.net) SqlException met een melding over reference contstraints enzo.

Mijn vraag is: hoe detecteer ik van te voren of er een dergelijke situatie optreedt áls ik die records zou verwijderen.

ik kan natuurlijk kijken in de tabellen die deze stukstypen gebruiken of er records bestaan met die specifieke id's die verwijderd gaan worden. Maar zoals ik al zei, er zijn meerdere tabellen en in de toekomst komen er nog bij; wat zou betekenen dat je dan elke keer de procedure / code moet aanpassen als dat gebeurd.

ik wil dit graag al opvangen in mijn bussines object, aangezien ik daar een 'rule' moet toevoegen dat een stukstype niet verwijderd mag worden als er een reference constrain overtreden wordt.

het opvangen van de SqlException is ook een optie, echter hoe weet je nu het type error. Aangezien bij kritieke fouten de systeembeheerder op de hoogte gesteld zou moeten worden. En bij minder kritieke de gebruiker een 'normale' melding krijgt.

snap e vous ?

[Verwijderd]

Hmm, ik zat nog een beetje te zoeken en vind in de SqlException class de property 'Number' wat overeenkomt met 'master.dbo.sysmessages'.
als ik het nummer van de 'reference constrain' fout opzoek en die nou gebruik ??

is dat een 'nette' oplossing ?

[sig69]

Een SqlException heeft properties zat waar je nuttige informatie uit kan halen?

[GarBaGe]

De vraag is natuurlijk wat je wil. Als je wil dat de records in de andere tabellen ook automatisch verwijderd worden, dan moet je even kijken naar "cascade delete".

[Verwijderd]

@sig69
daar kwam ik op tijd achter :-)

@GarBaGe
Dat wil ik dus juist niet (kan je uit mijn verhaal opmaken)

--

Nu ik er nog verder over nadenk, is het opvangen van de exceptie natuurlijk een goede mogelijkheid, echter voor mijn situatie niet helemaal ideaal.

Wat ik eigenlijk wil, is dat je vantevoren al weet of de record al dan niet verwijderd kan worden. Zodat er geen round-trip naar SqlServer nodig is om erachter te komen dat er een fout optreed.

Zoals ik in mijn startpost al zei, kan ik natuurlijk alle tabellen langs om te kijken of het stuksTypeID ergens gebruikt wordt; maar dit gaat de schaalbaarheid een beetje tegen, aangezien ik dan elke keer als er een tabel / kolom bijkomt die stukstypes gebruikt deze check moet aanpassen...

andere ideeen?

(bestaat er in T-SQL niet iets dat je kan opvragen of een waarde (key) gebruikt wordt: check_constraints() oid.)

[Niemand_Anders]

Je werkt verkeert om! Jouw code hoort zo geschreven te worden dat deze de integriteit van je database niet aantast. Constraints zijn juist belangrijk als buiten een applicatie om database aanpassingen worden gedaan (bijv. via de enterprise manager).

Jouw code hoort ervoor te zorgen dat eerst alle records (tblStuksType) wordt verwijderd voordat deze het record zelf uit tblSolution verwijderd. Omdat je in tblStuksType een column SolutionID hebt opgenomen, kan een record uit tblStuksType nooit aan twee solutions gekoppeld zijn. Kan dat wel dan is je data model onjuist.

Om ervoor te zorgen dat het mogelijk is om een solution te verwijderen, kun je een stored procedure maken welke eerst record van solution x uit tblStuksType verwijderd en daarna pas een delete op tblSolution uitvoert.

Je zou ook een query als

code:

IF (select StukTypeID from tblStuksType group by StukTypeID having count(StukTypeID) = 1)
    delete from tblStuksType where solutionID=@SolutionID and stukstypeID=@StuksTypeID

op die manier voorkom je dat er uberhaupt een exception optreed. Een exception is letterlijk een uitzondering. En uitzonderingen mogen normaal niet voorkomen, want anders is het geen uitzondering meer.

[Verwijderd]

Ik begrijp wel hoe constraints en databases in elkaar zitten Niemand_Anders,

maar mijn vraag of doel is eigenlijk: het detecteren of de te verwijderen record wel verwijderd mág worden.

dit kan op (naar mijn wete) 2 manieren:
- alle tabellen langs gaan die een reference constrain hebben met tblStuksType en kijken of de te verwijderen stuksTypeID bestaat in die tabel. Zo ja: dan mag deze niet verwijderd worden.
- domweg verwijderen en de SqlExpeption op laten treden en afvangen.

1 doe ik liever niet, omdat ik dus meerdere tabellen heb die stuksTypen gebruiken en er in de toekomst vast nog wel meer bij komen
2 doe ik liever ook niet, omdat ik vantevoren al wil weten of een record verwijderd mag worden ja/nee. en niet achteraf (zoals Niemand_Anders al aangeeft hoe je het zou móeten doen).

is er dus nog een 3e manier om te kijken of een bepaalde key in gebruik is door een andere tabel, door bijv. SqlServer naar de constraints op die key te laten kijken. In plaats van zelf alle tabellen op te sommen en te queryen.

[RobIII]

Niemand_Anders schreef op vrijdag 13 juli 2007 @ 16:12:
Je werkt verkeert om! Jouw code hoort zo geschreven te worden dat deze de integriteit van je database niet aantast. Constraints zijn juist belangrijk als buiten een applicatie om database aanpassingen worden gedaan (bijv. via de enterprise manager).

Oneens; constraints hebben als primair doel het ondersteunen van de software om te voorkomen dat er fouten worden gemaakt. Niet omdat users heel misschien perongeluk via de enterprise manager enge dingen zouden gaan doen. Zowieso horen users geen enterprise manager te gebruiken en als ze dat wel doen zouden ze 'opgevoed genoeg' moeten zijn om te weten wat ze doen; en dan zijn die constraints wel handig om te voorkomen dat je iets over het hoofd ziet. Maar again: daar zijn ze echt niet (primair) voor bedoeld.

Niemand_Anders schreef op vrijdag 13 juli 2007 @ 16:12:
Jouw code hoort ervoor te zorgen dat eerst alle records (tblStuksType) wordt verwijderd voordat deze het record zelf uit tblSolution verwijderd.

Dat wil 'ie juist niet; hij wil weten of het verwijderd kan worden (en er dus geen verwijzingen meer naar zjin).

Uiteraard dient je software exceptions en het 'tegen constraints aanlopen' (zoveel mogelijk) te voorkomen, maar die constraints zijn er juist voor de plekken waar je het 'vergeet' in je software op te nemen (kort door de bocht: bugs).

[ Voor 11% gewijzigd door RobIII op 13-07-2007 16:36 ]

[Verwijderd]

RobIII, ook nog een tip hoe ik dit toch het beste kan aanpakken ?

ik zit er toch over te denken om een functie te schrijven die controleert of die bepaalde record voorkomt in de verschillende tabellen:

dbo.fnStuksTypeInGebruik :

code:

DECLARE @inGebruik as bit
SELECT @inGebruik = (COUNT(1) > 0) FROM tblProduct WHERE stuksTypeID = @stuksTypeID
SELECT @inGebruik = (@inGebruik OR COUNT(1) > 0) FROM tblArtikel WHERE stuksTypeID = @stuksTypeID
SELECT @inGebruik = (@inGebruik OR COUNT(1) > 0) FROM tblBlaat WHERE stuksTypeID = @stuksTypeID

code:

SELECT 
 *, 
 dbo.fnStuksTypeInGebruik(stuksTypeID)
FROM tblStuksType
WHERE solutionID = @solutionID

[ Voor 35% gewijzigd door Verwijderd op 13-07-2007 16:44 ]

[RobIII]

Als je het (zoals je aangeeft) vantevoren wil weten, zonder exceptions te gebruiken, dan zit er (volgens mij) weinig anders op dan alle referenties na te lopen.

[EfBe]

Je kunt checken wat je wilt, maar zonder een exclusive lock op de complete table is het resultaat van die check zinloos. Immers tussen de check en jouw delete kan een andere thread een row inserten die ervoor zorgt dat de dependency afgaat.

[SPee]

Zou je niet vanuit je tabel a.d.h.v. de relaties kunnen controleren hoevaak hij voorkomt?

Maar je houdt nu al rekening mee dat nieuwe tabellen er mogelijk bijkomen en daardoor wil je dit doen. Máár, houden nieuwe tabellen niet in dat je je programmatuur moet wijzigen en uiteindelijk toch moet aanpassen (wat je nu wilt voorkomen)

Het is goed om aan de toekomst te denken, maar je moet niet te ver vooruit denken. Dan ga je geheid verkeerde keuzes nemen, dingen maken die je later toch nooit hebt gebruikt en het onnodig complex maken.
Mijn collega zit nu ook te schelden op de applicatie die hij nu beheert, waar ze in het begin ook 'rekening houden met de toekomst'.

[MrBucket]

Verwijderd schreef op vrijdag 13 juli 2007 @ 16:21:
maar mijn vraag of doel is eigenlijk: het detecteren of de te verwijderen record wel verwijderd mág worden.

dit kan op (naar mijn wete) 2 manieren:
- alle tabellen langs gaan die een reference constrain hebben met tblStuksType en kijken of de te verwijderen stuksTypeID bestaat in die tabel. Zo ja: dan mag deze niet verwijderd worden.
- domweg verwijderen en de SqlExpeption op laten treden en afvangen.

1 doe ik liever niet, omdat ik dus meerdere tabellen heb die stuksTypen gebruiken en er in de toekomst vast nog wel meer bij komen
2 doe ik liever ook niet, omdat ik vantevoren al wil weten of een record verwijderd mag worden ja/nee. en niet achteraf (zoals Niemand_Anders al aangeeft hoe je het zou móeten doen).

Wat is de achterliggende reden dat je van tevoren wil weten of een record verwijderd kan worden? Ga je hem daarna alsnog verwijderen?

Als je'm meteen daarna alsnog verwijdert, dan kun je beter gewoon de delete uitvoeren en de evt. SqlException bekijken waarom het niet mocht. Vergeet niet dat van tevoren checken of een record in gebruik is ook een roundtrip kost, en bovendien kan het record ten tijde van de check nog niet in gebruik zijn, maar ten tijde van de delete net wel (dankzij een andere user). De check-delete is nl. niet atomair.

Ben je helemaal niet van plan om het record te deleten maar wil je echt alleen weten of deze nog in gebruik is, dan kun je ofwel alle referenties handmatig gaan checken om te zien of er nog een tabel is die ernaar verwijst (de nette manier), of binnen een SqlTransaction de delete uitvoeren, kijken of dit lukt, en daarna altijd de transactie weer terugrollen (misschien wat ranziger).

Wat betreft het achterhalen van de reden van een SqlException: wat je kunt doen is een Regular Expression schrijven die matcht op de tekst van bijv. een foreign key violation ("row is referenced by foreign key constraint xxx" of zoiets) en die de naam van de foreign key captured. Matcht de regex niet, dan is het een serieuze error, matcht 'ie wel (en is de gecapturde naam die van een bekende / verwachte foreign key constraint), dan betekent het dat het record nog in gebruik is.

[ Voor 12% gewijzigd door MrBucket op 13-07-2007 18:55 ]

[tss68nl]

Je kan toch gewoon de systeemtabellen raadplegen? Maak ff een query om voor de huidige tabel op te halen welke andere tabellen afhankelijk zijn van de records in de huidige. Je kan dan vervolgens de procedure een functie in een variabele laten schrijven waarmee je kan controleren of een bepaalde primary key value in de hoofdtabel verwijderd mag worden.

Is 1 keer schrijven, en toepasbaar op iedere willekeurig(e) database/model.