Ćwiczenia > Budowa bazy danych w MS SQL Server 2000 > Nakładanie ograniczeń na kolumny

3.9 Nakładanie ograniczeń na kolumny



W celu wymuszenia poprawności logicznej danych w tabelach możesz użyć ograniczeń (CONSTRAINT). Na przykład wiadomo, że w kolumnie rok_wydania w tabeli Ksiazki bazy danych Biblioteka nie powinny być zapisywane inne znaki, jak tylko cyfry (i to na dodatek określone - tzn. na pozycji pierwszej 1 lub 2, zaś na pozostałych trzech dowolne w przypadku cyfry 1 na pierwszym miejscu oraz zero na drugiej pozycji i dowolne pozycje trzecia i czwarta w przypadku cyfry 2 na pierwszym miejscu). Spróbujemy wymusić taki format danych w tej kolumnie.

Krok 1 - Dodaj ograniczenie na format danych kolumny do istniejącej tabeli

1. Uruchom program Query Analyzer.
2. Zaloguj się używając uwierzytelnienia systemu Windows.
3. W menu głównym programu wybierz File - Open.
4. W oknie Open Query File wybierz plik demo_3_9_1.sql. Nie uruchamiaj skryptu, a jedynie obejrzyj jego kod.
5. Zaznacz i uruchom (klawisz F5) fragmenty kodu oznaczone w komentarzach jako (1) i (2).



-- (1) ustawmy Biblioteke jako baze robocza
USE Biblioteka
GO

-- (2) utworzmy warunek, ze rok wydania
-- ma byc z przedzialu 1900-2004
ALTER TABLE Ksiazki
ADD
	CONSTRAINT CK_rok_wydania 
	CHECK ((rok_wydania LIKE '19[0-9][0-9]')
	OR (rok_wydania LIKE '200[0-4]'))
GO


Powyższy kod ustawia bazę danych Biblioteka jako bazę, z którą będziemy pracować, a następnie modyfikuje tabelę Ksiazki dodając ograniczenie o nazwie CK_rok_wydania. Ograniczenie to będzie wymuszało, by wartości zapisywane w kolumnie rok_wydania były z przedziału 1900-2004.

Ograniczenia można określać oczywiście przy tworzeniu tabel. W tym przypadku wyglądałoby to jak poniżej.


CREATE TABLE Ksiazki (
	ID_ksiazki 	int IDENTITY(1,1)
		CONSTRAINT PK_ksiazki
		PRIMARY KEY CLUSTERED,
	tytul 		varchar(100) NOT NULL,
	rok_wydania 	char(4) NOT NULL,
	ID_wydawnictwa	int NOT NULL,
	CONSTRAINT FK_wydawnictwa
	FOREIGN KEY (ID_wydawnictwa)
	REFERENCES Wydawnictwa(ID_wydawnictwa),
	CONSTRAINT CK_rok_wydania 
	CHECK ((rok_wydania LIKE '19[0-9][0-9]')
	OR (rok_wydania LIKE '200[0-4]')) )
GO


6. Aby można było w ogóle dodawać rekordy do tabeli Ksiazki, musisz wstawić przynajmniej jeden rekord do tabeli Wydawnictwa. Zaznacz i uruchom (F5) fragmenty kodu oznaczone jako (3) oraz (4). Wynikiem powinno być dodanie jednego wydawnictwa w tabeli Wydawnictwa (polecenie INSERT) i pokazanie zawartości tabeli (polecenie SELECT).



-- (3) wstawmy jakies wydawnictwo w tabele Wydawnictwa,
-- zeby mozna bylo wstawiac ksiazki
INSERT INTO Wydawnictwa(wydawnictwo)
VALUES ('Helion')
GO

-- (4) zobaczmy, co wstawilismy
SELECT * FROM Wydawnictwa
GO


7. Skoro dodaliśmy ograniczenie, wypróbujmy je. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (5). Jest to próba wstawienia rekordu zawierającego niepoprawny rok wydania książki. Wynikiem działania powinien być komunikat o błędzie.


-- (5) ta instrukcja sie nie wykona, poniewaz
-- warunek mowi, ze rok nie moze byc wiekszy
-- niz 2004
INSERT INTO Ksiazki(tytul,rok_wydania,ID_wydawnictwa)
VALUES ('SQL','2005',1)
GO


8. Sprawdźmy, czy rzeczywiście nie dodaliśmy rekordu do tabeli Ksiazki. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (6). Kod ten powoduje wyświetlenie zawartości tabeli Ksiazki.


-- (6) wyswietlmy zawartosc tabeli Ksiazki
SELECT * FROM Ksiazki
GO


9. Po sprawdzeniu, że ograniczenie odrzuca błędne dane, sprawdźmy, czy pozwoli na dodanie prawidłowych danych do tabeli. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (7). Jest to próba wstawienia tym razem poprawnych logicznie danych. Tym razem nie powinieneś zobaczyć komunikatu o błędzie.


-- (7) ta instrukcja sie wykona, gdyz rok jest poprawny
INSERT INTO Ksiazki(tytul,rok_wydania,ID_wydawnictwa)
VALUES ('SQL','2000',1)
GO


10. Zobaczmy, czy rzeczywiście udało się wstawić dane do tabeli. Zaznacz i wykonaj (F5) ponownie fragment kodu oznaczony w komentarzu jako (5) - czyli wyświetlenie zawartości tabeli Ksiazki. Tym razem dane zostały wstawione do tabeli. Czyli ograniczenie funkcjonuje poprawnie.

Krok 2 - Jak dodawać ograniczenia przy użyciu programu Enterprise Manager

1. Uruchom program Enterprise Manager.
2. Rozwijaj drzewo po lewej stronie okna programu i wejdź do folderu Databases.
3. W folderze bazy Biblioteka wybierz pozycję Tables.
4. W prawej części okna programu kliknij prawym przyciskiem myszy na tabeli Ksiazki i z menu kontekstowego wybierz opcję Design Table.
5. W górnej części okna Design Table klikni na ikonie zarządzania ograniczeniami .
6. Pojawi się okno Properties z otwartą kartą Check Constraints.
7. Zauważ, że w tabeli Ksiazki juz istnieje ograniczenie CK_rok_wydania. Możesz także obejrzeć kod tego ograniczenia.

Aby dodać nowe ograniczenie, musisz kliknąć w oknie Properties w karcie Check Constraints na przycisku New. Wszystko, co musisz podać, to nazwa ograniczenia oraz jego kod (to, co w kodzie T-SQL występuje po słowie kluczowym CHECK). Dodatkowo możesz wymusić sprawdzenie przez ograniczenie istniejących w tabeli danych (zaznaczasz pole Check existing data on creation), wymuszać ograniczenie dla procesu replikacji (pole Enforce constraint for replication) oraz wymuszać ograniczenie dla danych wstawianych i modyfikowanych po utworzeniu ograniczenia (pole Enforce constraint for INSERTs and UPDATEs).

Aby usunąć istniejące ograniczenie należy wybrać ograniczenie w polu Selected constraint i kliknąć na przycisku Delete.



Rys. 3.9.1 Enterprise Manager - edycja istniejącego ograniczenia w tabeli


8. Nie zmieniaj istniejącego ograniczenia i nie dodawaj nowych. Zamknij okno Properties i okno Design Table.

Czasem nie można osiągnąć wymaganego rezultatu za pomocą ograniczeń. Wtedy na ogół używamy wyzwalaczy, o których dowiesz się w dalszej części podręcznika. Jednak jeśli tylko pożądany efekt można osiągnąć przy użyciu ograniczenia, należy je stosować.


W bazie danych Prace dyplomowe zaplanuj i dodaj ograniczenia w tabelach. Nie dodawaj zbędnych ograniczeń, a tylko takie, które są funkcjonalne.


Przejdź dalej



Ćwiczenia > Budowa bazy danych w MS SQL Server 2000 > Nakładanie ograniczeń na kolumny