Zapnutí, vypnutí a přehled constraintů v SQL Serveru

03.03.2016, Vladimír Klaus, navštíveno 4126x

Pokud občas potřebujete pracovat s databází přímo (například smazat nějaké záznamy), tak vám v tom mohou zabránit constrainty, neboli omezení. Klasickým příkladem je odstranění pacientů, kteří mají záznamy v kalendáři lékaře. Aby šel smazat pacient, musí se nejprve smazat na něj navázané záznamy. A právě to (i jiné věci) hlídají constrainty, aby zajistili konzistenci dat v databázi.

Nejprve se podíváme, jak si omezení vypsat. Skript jsem někde našel, proto ho vkládám bez dalšího komentáře.

SELECT (CASE 
        WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0
          THEN 'ENABLED'
          ELSE 'DISABLED'
        END) AS STATUS,
        OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
        OBJECT_NAME(FKEYID) AS TABLE_NAME,
        COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
        OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
        COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
   FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, KEYNO

Výsledkem je pěkná tabulka

Zapnutí, vypnutí a přehled constraintů v SQL Serveru, 1

Jak tedy vypnout constrainty? Buď to můžete udělat pro všechny tabulky najednou nebo postupně jen pro některé (což bych doporučoval).

-- vypne všechny constrainty
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- vypne constrainty jen pro některé tabulky
ALTER TABLE SHIPMENTAddresses NOCHECK CONSTRAINT all
ALTER TABLE SHIPMENTAmounts NOCHECK CONSTRAINT all
ALTER TABLE SHIPMENTDeliveryInstructions NOCHECK CONSTRAINT all

Nyní už můžete odstranit záznamy, které potřebujete. Ale pozor, musíte to udělat jak z té hlavní tabulky (např. pacienti), tak i za všech dalších, kde se na pacienty odkazuje (kalendář, diagnózy). Proč? No protože když odstraníte jen ty hlavní a pokusíte se zase constrainty zapnout, tak to nepůjde - omezení zjistí, že jsou data nekonzistentní a výsledek je tedy třeba takováto hláška.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ShipmentRelatedServiceOrders_ShipmentShipments". The conflict occurred in database "IKEA2016", table "dbo.SHIPMENTShipments", column 'ID'.

Záznamy se dají chytře odstraňovat také pomocí kaskádového mazání, ale to bude předmětem až zcela jiného článku.

Když máte tedy vše potřebné odstraněné, můžete constrainty zapnout podobným způsobem, jako se vypínají.

-- zapne všechny constrainty
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

-- zapne constrainty jen pro některé tabulky
ALTER TABLE SHIPMENTAddresses WITH CHECK CHECK CONSTRAINT all
ALTER TABLE SHIPMENTAmounts WITH CHECK CHECK CONSTRAINT all
ALTER TABLE SHIPMENTDeliveryInstructions WITH CHECK CHECK CONSTRAINT all

No a to je celé. Možná snad jen znovu spusťte ten kontrolní skript, abyste se přesvědčili, že to zabralo a databáze je OK.

Poznámka: Constrainty z pochopitelných důvodů databázi zpomalují. Pokud jste si jisti, že nemůžete žádným způsobem databázi uvést do nekonzistentního stavu, můžete nechat omezení vypnutá.

Nahoru