Při práci s tabulkami, které mají cizí klíče zajišťující integritu dat, často řešíme problém, jak tyto klíče a jiná omezení dočasně vypnout, abychom mohli například provést změnu dat v tabulce, rychlý import velkého množství dat či manipulaci s jinou tabulkou, na kterou se původní tabulka odkazuje. Cizí klíče a jiná omezení můžeme pochopitelně zakázat ručně například odstraněním v Management Studiu a opětovným vytvořením nebo přípravou skriptu pro jednotlivé klíče. Existuje však i jednodušší metoda, kdy lze pomocí několika příkazů naráz zakázat i povolit všechny cizí klíče a omezení tabulky a následně provést kontrolu integrity dat.
Pomocí následujícího skriptu nejprve vytvoříme testovací databázi a v ní tři tabulky: Tabulku Orders (Objednávky) a k ní rodičovské tabulky Customer(Zákazník) a Store (Obchod):
CREATE DATABASE TestDB GO CREATE TABLE Customers ( Customer_ID INT NOT NULL PRIMARY KEY, CustomerName NVARCHAR(100) ) GO CREATE TABLE Stores ( Store_ID INT NOT NULL PRIMARY KEY, StoreName NVARCHAR(100) ) GO CREATE TABLE Orders ( Order_ID INT NOT NULL PRIMARY KEY, Customer_ID INT NOT NULL, Store_ID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderAmount INT NOT NULL )
Nyní vytvoříme mezi tabulkou Orders a tabulkami Customers a Stores cizí klíče, které zajistí, že nebude možné z tabulek Customers a Stores odstranit záznamy, pokud k nim existují nějaké objednávky:
ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY ( Customer_ID ) REFERENCES dbo.Customers ( Customer_ID ) GO ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Stores FOREIGN KEY ( Store_ID ) REFERENCES dbo.Stores ( Store_ID ) GO
Výsledná struktura vypadá v Management Studiu takto:
Do tabulek vložíme testovací data:
INSERT INTO Customers ( Customer_ID, CustomerName ) SELECT 1, 'Customer1' UNION ALL SELECT 2, 'Customer2' UNION ALL SELECT 3, 'Customer3' GO INSERT INTO Stores ( Store_ID, StoreName ) SELECT 1, 'Store1' UNION ALL SELECT 2, 'Store2' GO INSERT INTO Orders ( Order_ID, Customer_ID, Store_ID, OrderDate, OrderAmount ) SELECT 1, 1, 1, '2014-05-01', 500 UNION ALL SELECT 2, 2, 2, '2014-05-02', 1000 UNION ALL SELECT 3, 3, 2, '2014-05-03', 1500 GO
Výsledek vypadá takto:
SELECT * FROM Customers SELECT * FROM Stores SELECT * FROM Orders GO
Ověříme funkčnost cizích klíčů tím, že se pokusíme vymazat Zákazníka nebo Obchod aniž bychom nejprve odstranili závislé záznamy z tabulky Objednávek:
DELETE FROM Customers WHERE Customer_ID = 1 GO DELETE FROM Stores WHERE Store_ID = 1 GO
Výsledkem je chyba:
Server nás upozorní, že odstranění není možné, protože cizí klíč FK_Orders_Store vynucuje, že tabulka Orders může obsahovat ve sloupci Store_ID pouze platná ID z tabulky Stores.
Vytvořené cizí klíče můžeme zkontrolovat pomocí dotazu do metadat – systémový pohled sys.foreign_keys:
SELECT name FK_Name, OBJECT_NAME(parent_object_id) ParentObject, OBJECT_NAME(referenced_object_id) ReferencedObject, is_disabled, is_not_trusted FROM sys.foreign_keys fk GO
- ParentObject je objekt, na kterém je cizí klíč umístěn
- ReferencedObject je objekt, který obsahuje hodnoty (ID), jejichž integritu cizí cizí klíč zajišťuje
- is_disabled – říká, zda je cizí klíč povolen nebo zakázán
- is_not_trusted – říká, zda je cizí klíč důvěryhodný.
U is_not_trusted se na chvíli zastavme. Pokud cizí klíč nad tabulkou zakážeme a opět povolíme, systém neví, zda po uvedenou dobu došlo či nedošlo ke změně dat, která mohla vést k tomu, že do tabulky se dostaly záznamy, které by tam nebylo možné vložit tehdy, pokud by cizí klíč byl stále aktivní. Podobný scénář si ukážeme dále. Důvěryhodnosti cizích klíčů je třeba věnovat pozornost, protože důvěryhodné cizí klíče dokáže velmi obratně využití optimalizátor, protože se například nemusí dotazovat do jiné tabulky, když pomocí důvěryhodného klíče ví, že se v ní hledaná hodnota nemůže vyskytovat.
Nyní přistoupíme ke změně nastavení cizích klíčů a pomocí NOCHECK CONTRAINT ALL všechny cizí klíče nad tabulkou Orders zakážeme:
ALTER TABLE Orders NOCHECK CONSTRAINT ALL GO
Dotazem do metadat se podíváme, zda bylo nastavení cizích klíčů skutečně změněno:
Vidíme, že se změnilo jak nastavení is_disable z 0 na 1, tedy cizí klíče byly zakázány, ale zároveň se změnilo i nastavení jejich důvěryhodnosti, protože nyní nemám SQL Server žádnou kontrolu nad integritou dat a klíče jsou jako nedůvěryhodné vyřazeny z použití optimalizátoru, což v důsledku může znamenat i to, že budou přegenerovány existující exekuční plány, které počítali s existencí důvěryhodných klíčů.
Nyní se pokusme znovu smazat data z tabulek Customers a Stores:
DELETE FROM Customers WHERE Customer_ID = 1 GO DELETE FROM Stores WHERE Store_ID = 1 GO (1 row(s) affected) (1 row(s) affected)
Vidíme, že oba záznamy byly úspěšně smazány a nevrací se nám chyba upozorňující na porušení omezení jako dříve. Pokud si prohlédneme data, jasně vidíme, že v tabulce Orders zůstala IDčka právě smazaný Zákazníků a Obchodů:
Nyní opět povolíme cizí klíče:
ALTER TABLE Orders CHECK CONSTRAINT ALL GO
Ověříme jejich funkčnost:
DELETE FROM Customers WHERE Customer_ID = 2 GO DELETE FROM Stores WHERE Store_ID = 2 GO
Vidíme, že cizí klíče opět kontrolují integritu dat a brání vzniku nevalidních referencí. Ovšem pokud se podíváme do metadat, tak kromě toho, že máme ve skutečnosti v tabulkách nevalidní reference, tak naše cizí klíče jsou stále nedůvěryhodné:
SELECT name FK_Name, OBJECT_NAME(parent_object_id) ParentObject, OBJECT_NAME(referenced_object_id) ReferencedObject, is_disabled, is_not_trusted FROM sys.foreign_keys fk GO
Jak k této situaci došlo? Když jsme výše povolovali cizí klíče pomocí ALTER TABLE… CHECK CONTRAINTS ALL, neuvedli jsme WITH CHECK, která znamená, že SQL Server při povolení cizích klíčů provede i fyzickou kontrolu dat. Nyní spustíme tento příkaz znovu se správných nastavením:
ALTER TABLE Orders WITH CHECK CHECK CONSTRAINT ALL GO
Chybové hlášení nás upozorní, že cizí klíč FK_Orders_Customers nelze povolit, protože došlo ke konfliktu s tabulkou Customers, sloupcem Customer_ID, kde se nepodařilo najít všechny hodnoty, na které se odkazuje sloupec Customer_ID.
Jedinou možností, jak opět povolit cizí klíče a dosáhnout jejich důvěryhodnosti, je opravit data tak, aby byla zachována pravidla integrity vynucovaná cizími klíči. Můžeme smazat nevalidní záznamy z tabulky Orders, případně je převést na existující záznamy z tabulek Customers a Stores, nebo v těchto tabulkách opět vytvořit chybějící záznamy.
ALTER TABLE dbo.TestTable NOCHECK CONSTRAINT ALL
GO
-- TODO
ALTER TABLE dbo.TestTable CHECK CONSTRAINT ALL
GO
DBCC CHECKCONSTRAINTS('dbo.TestTable')
GO