piątek, 17 lutego 2012

Ograniczenia w bazie danych

Ograniczenia w bazie danych są najlepszym sposobem na zachowanie integralności danych. SQL Server oferuje kilka różnych typów ograniczeń i koszt ich użycia nie jest taki sam. Ograniczenia sprawdzane są w określonej kolejności. W poniższych testach zademonstruje działanie kilku z nich.
Przykładowe zapytania bądą wykonywane na bazie danych, do której skrypt można pobrać z tej lokalizacji.
Poniższy skrypt doda wyzwalacz, który będzie potrzebny do wykonania jednego z testów. Wszystkie testy będą wykonane na tabeli Subdivision.
   1: CREATE TRIGGER TRIG_SUBDIVISION_UPDATE ON Subdivision
   2: AFTER UPDATE
   3: AS
   4:  
   5: IF EXISTS(SELECT * FROM inserted WHERE SubdivisionId like 'XX-%')
   6:     RAISERROR('Invalid SubdivisionId', 1,16)
   7: GO
Poniżej dla przejrzystości deklaracja tabeli Subdivision. Znajduję się ona także w skrypcie, o którym wspomniałem wcześniej.
   1: IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'Subdivision')
   2: CREATE TABLE Subdivision(
   3:     SubdivisionID varchar(6) NOT NULL,
   4:     SubdivisionName nvarchar(50) NOT NULL,
   5:     CountryID  AS CAST(LEFT(SubdivisionID,(2)) AS CHAR(2)) PERSISTED,
   6:     Constraint FK_Subdivisions_Country FOREIGN KEY(CountryID) REFERENCES Country(CountryID),
   7:     Constraint PK_Subdivision PRIMARY KEY(SubdivisionID),
   8:     Constraint SubdivisionLength CHECK(LEN(SubdivisionID) >= 4)
   9: )
  10: GO
Istotnym elementów testów jest tabela Hotel
   1: if not exists(select * from sys.tables where name = 'Hotel')
   2: create table Hotel (
   3:     HotelID int identity not null,
   4:     Name nvarchar(100) not null,
   5:     Address1 nvarchar(100) not null,
   6:     Address2 nvarchar(100) null,
   7:     City nvarchar(255) not null,
   8:     SubdivisionID varchar(6) not null,
   9:     Postcode varchar(10) not null,
  10:     constraint PK_Hotel primary key(HotelID),
  11:     constraint FK_Hotel_Subdivision foreign key(SubdivisionID) references Subdivision (SubdivisionID)
  12: )
  13: go

 

Kolejności sprawdzania ograniczeń

Ograniczenia w bazie sprawdzane są w następującej kolejności
  • Wartości domyślne
  • Wartość NULL
  • Ograniczenie CHECK
  • Klucze obce w tabelach zależnych
  • Klucze tabeli aktualizowanej
  • Klucz podstawowy / wartość unikalna
  • Wyzwalacze

Sprawdzanie wartości NULL

W testach pominę pierwszy typ ograniczeń. Osobiście rzadko korzystam z tej funkcjonalności i staram się przenosić ustawianie wartości domyślnych do aplikacji bądź procedur składowych.

Sprawdzanie wartości NULL a raczej NOT NULL jest jednym z moich ulubionych. Ja lubie dane, a jeżeli tych danych nie ma w połowie tabeli to kolumna może powinna zostać przenesiona do innej tabeli.

Kolumna SubdivisionId będąca kluczem podstawowym nie może przyjmować wartości NULL.

Wykonując poniższą kwerendę w wynikach pojawi się błąd.
   1: BEGIN TRAN
   2: UPDATE Subdivision SET SubdivisionId = NULL WHERE SubdivisionName = 'York'
   3: ROLLBACK TRAN

Msg 515, Level 16, State 2, Line 2

Cannot insert the value NULL into column 'SubdivisionID', table 'SQLTest.dbo.Subdivision'; column does not allow nulls. UPDATE fails.


Warto zwrócić uwagę tutaj na fakt, że sprawdzane jest ograniczenie sprawdzające czy nowa wartością nie jest NULL a nie klucz podstawowy.

 

Organiczenie CHECK

Jest to prawdopodobnie jedno z rzadziej używanych ograniczeń w bazie danych, jednakże jest ono bardzo przydatne w kontekście optymalizacji bazy danych.

W przypadku kodów ISO dla jednostek administracyjnych, pierwsze dwie litery to jest oznaczenie państwa po którym następuje myślnik i kod właściwy jednostki administracyjnej. W przykładzie tym w tabeli Subdivision jest ograniczenie na minimalną długość tekstu, która musi wynosić 4 znaki.
   1: BEGIN TRAN
   2: UPDATE Subdivision SET SubdivisionId = 'XX-' WHERE SubdivisionName = 'York'
   3: ROLLBACK TRAN
Powyższe zapytanie zwróci następujący błąd:

Msg 547, Level 16, State 0, Line 2

The UPDATE statement conflicted with the CHECK constraint "SubdivisionLength". The conflict occurred in database "SQLTest", table "dbo.Subdivision", column 'SubdivisionID'.


Ograniczenie to, podobnie jak wartość domyślna i NOT NULL jest ograniczeniem własnym tabeli. Nie występują tutaj żadne odwołania do innych obiektów. Warto zwrócić tutaj uwagę na kolejność sprawdzania ograniczeń. Pomimo tego, że w tabeli Hotel istnieje rekord dla GB-YOR, który jest aktualizowany przez powyższe zapytanie, klucz obcy nie jest weryfikowany.

 

Sprawdzanie kluczy obcych w tabelach zależnych

W nastepnej kolejności sprawdzane są klucze obce
   1: BEGIN TRAN
   2: UPDATE Subdivision SET SubdivisionId = 'XX-ZZ' WHERE SubdivisionName = 'York'
   3: ROLLBACK TRAN

Powyższe zapytanie, gdyby zakończylo się sukcesem spowodowałoby utratę integralności danych. Z racji tego, że w tabeli Hotel znajduję się klucz obcy zapytanie to zwróci następujący błąd

Msg 547, Level 16, State 0, Line 2

The UPDATE statement conflicted with the REFERENCE constraint "FK_Hotel_Subdivision". The conflict occurred in database "SQLTest", table "dbo.Hotel", column 'SubdivisionID'.

 

Sprawdzanie kluczy obcych w tabeli aktualizowanej

Aby przeprowadzic kolejny test najpierw będzie trzeba zaktualizować tabelę Hotel. W tym celu klucz obcy do tabeli Subdivision będzie musiał zostać tymczasowo wyłączony.
   1: BEGIN TRAN
   2: ALTER TABLE Hotel NOCHECK CONSTRAINT FK_Hotel_Subdivision
   3: UPDATE Hotel SET Subdivisionid = 'XX-ZZ' WHERE HotelId=1
   4: ALTER TABLE Hotel CHECK CONSTRAINT FK_Hotel_Subdivision
   5:  
   6: UPDATE Subdivision SET SubdivisionId = 'XX-ZZ' WHERE SubdivisionName = 'York'
   7: ROLLBACK TRAN
Powyższe zapytanie aktualizujące tabelę Subdivision jest takie samo jak w poprzednim teście, jednakże tym razem zwrócony zostanie następujący błąd:

Msg 547, Level 16, State 0, Line 6

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Subdivisions_Country". The conflict occurred in database "SQLTest", table "dbo.Country", column 'CountryID'.

 

Sprawdzanie kluczy podstawowych, wartości unikalnych

Przetestowanie sprawdzanie kluczy podstawowych w tabeli Subdivision, przy jednoczesnym zachowaniu sekwencji testów jest niemożliwe.

 

Wyzwalacze

Ostatnim etapem weryfikacji danych i sprawdzania integralności wbudowanej w SQL Server są wyzwalacze.

W calu wykonania tego testu najpierw trzeba wykonać poniże zapytanie, które spraw, że nie pojawi się błąd związany z kluczami obcymi.
   1: INSERT INTO Country SELECT 'XX', 'Dummy', 'XXX', 0
   2: GO
Teraz ponownie wykonując następujące zapytanie zostanie uruchomiony wyzwalacz
   1: BEGIN TRAN
   2: ALTER TABLE Hotel NOCHECK CONSTRAINT FK_Hotel_Subdivision
   3: UPDATE Hotel SET Subdivisionid = 'XX-ZZ' WHERE HotelId=1
   4: ALTER TABLE Hotel CHECK CONSTRAINT FK_Hotel_Subdivision
   5:  
   6: UPDATE Subdivision SET SubdivisionId = 'XX-ZZ' WHERE SubdivisionName = 'York'
   7: ROLLBACK TRAN
Sprawdza on czy nowa wartość zaczyna się od XX i jeżeli tak jest zwrócony jest błąd.

Invalid SubdivisionId
Msg 50000, Level 1, State 16

Powyższe przykłady pokazały kolejność sprawdzania ograniczeń w bazie danych.

Powyższe rozważania zakończę planem wykonania zapytania aktualizującego dane w tabeli Subdivision. Proste zapytanie a operacji jest nie tak mało. Na planie można między innymi zauważyć kolejność sprawdzania kluczy obcych.

OgraniczeniaPlanWykonania

Dalsze rozwaania na temat powyższego planu pozostawie sobie na kolejny post.

Prześlij komentarz