Rozdział 14.
Zapewnienie integralności danych

Poprzedni rozdział omawiał indeksy i wszystkie opcje związane z indeksami dostępne w SQL Serverze. Indeksy mają znaczący wpływ na wydajność, ale zbyt wiele indeksów może faktycznie pogorszyć wydajność. Wybór odpowiedniej struktury indeksowania, włączając w to wybranie jakiego indeksu użyć jako indeksu zgrupowanego, jest bardzo istotny dla zwiększenia wydajności bazy danych. Tworzenie indeksów na widokach może znacząco poprawić raportowanie zapytań które często łączą wiele tablic.

Niniejszy rozdziała skupia się na integralności danych. Zostaną omówione dwa mechanizmy wymuszenia integralności danych: proceduralny i deklaratywny. Integralność deklaratywna w SQL Serverze połączona z własnością IDENTITY jest często nazywana w dokumentacji Microsoft declarative referential integrity (DRI). Jednak nazwa ta nie oddaje wszystkiego co jest związane z tą własnością.

Jak zapewnić integralność danych

Sprawdzając integralność danych, użytkownik stara się zapewnić poprawność danych w bazie z punktu widzenia zwykłej poprawności (bez błędów) oraz z punktu widzenia biznesowego. Jak zostało wcześniej wspomniane można wymusić integralność danych w sposób proceduralny lub używając integralności deklaratywnej.

Integralność proceduralna oznacza, że można wykorzystać struktury programistyczne i oddzielne obiekty do zapewnienia integralności danych. Często wykonuje się to z poziomu aplikacji. Jednak, oznacza to, ze jeżeli wystąpią jakieś modyfikacje danych poza tą aplikacją, reguły integralność nie będą wymuszane. Można zapewnić integralność przy pomocy obiektów takich jak procedury wyzwalane i procedury składowane, które zostaną omówione w rozdziale 15. Pozostała część dzisiejszej lekcji obejmuje wartości domyślne i reguły mechanizmów wymuszania, jak również omawia bardziej szczegółowo typy danych zdefiniowane przez użytkownika.

Inne podejście to integralność deklaratywna, inaczej mówiąc mechanizmy zapewniające integralność są deklarowane jako część definicji obiektów (tablic) w bazie danych. Stają się one integralną częścią tych obiektów. Większość czasu zostanie poświęcona na testowanie tych obiektów, ponieważ dostarczają one istotnego zbioru funkcji, który w innym przypadku wymagałby znaczącej ilości programowania aby go określić.

Typy integralności

Wiele składników jest używanych do wymuszenia integralności w SQL Serverze 2000. Niektóre z tych składników są bardziej popularne niż inne. Może używać tych trzech typów integralności: dziedziny, relacyjnego i encji.

Integralność dziedziny

Typy danych pomagają określić, jakie wartości są prawidłowe dla poszczególnych kolumn. Jest to integralność dziedziny (domain integrity). Dziedzina jest po prostu zbiorem poprawnych wartości dla danej kolumny. Możliwość wprowadzania wartości NULL jest inną opcją określającą jakie wartości są prawidłowe w dziedzinie — w tym przypadku, nawet nieznana wartość (null) jest poprawna. Można dodać kolejne ograniczenia do dziedziny kolumn korzystając z typu danych zdefiniowanych przez użytkownika, reguł i wartości domyślnych w przypadku używania „tradycyjnych” obiektów integralności SQL Servera. Z drugiej strony, można używać więzów American National Standards Institute (ANSI) — domyślnych i więzów sprawdzających, aby wymusić integralność dziedziny. Warto zauważyć, że typy danych i wartości null są używane zawsze, pozostałe składniki są opcjonalne.

Integralność referencyjna

Integralność referencyjna odnosi się do utrzymywania relacji pomiędzy wierszami danych w wielu tablicach. Integralność referencyjna jest wymuszana z DRI, jak zostało wcześniej powiedziane. Można również wymusić integralność używając wyzwalaczy i kontrolując programowo tę funkcjonalność, ale wymaga to trochę pracy.

SQL Server 2000 posiada dwa typy akcji kaskadowych dla kluczy obcych: kaskadowe usuwanie (nowe w SQL Serverze 2000) oraz działanie „no action”. Drugi typ oznacza, że jeżeli przykładowo próbuje się usunąć z bazy dane klienta, a ustawiona jest integralność referencyjna „no action”, nie można usunąć klienta jeżeli posiada jakiekolwiek faktury. Jeżeli jest ustawiona integralność referencyjna z usuwaniem kaskadowym, usuwając klienta z bazy danych usuwane są również wszystkie jego faktury. Aby spełnić integralność usuwania kaskadowego we wcześniejszych wersjach SQL Servera, należało ją własnoręcznie zaprogramować (przy pomocy procedur wyzwalanych, procedur składowych lub standardowych poleceń Transact-SQL). Ta znacząca możliwość SQL Servera 2000 powinna oszczędzić znaczną ilość programowania projektantom rozbudowującym bazę danych.

Integralność encji

Ostatnim typem integralności jaki będzie rozważany jest integralność encji. Oznacza on, że można jednoznacznie zidentyfikować każdy wiersz w tablicy. Można to zrobić używając indeksu unikalnego (jak powiedziano w poprzednim rozdziale) lub integralności deklaratywnej (więzów unikalnych lub klucza podstawowego), o których będzie mowa w tym rozdziale.

Tradycyjne metody integralności

Tradycyjne (czyli zgodne z wcześniejszymi wersjami) metody zapewnienia integralności zawierają zdefiniowane przez użytkownika typy danych, wartości domyślne i reguły. Teraz zostanie pokazane jak te elementy bazy danych mogą wymusić integralność bazy.

Pomimo tego, że można używać typów zdefiniowanych przez użytkownika, wartości domyślnych i reguł jako mechanizmów integralności, odradza się używanie ich na rzecz korzystania z więzów integralności ANSI, gdzie tylko jest to możliwe. Więzy integralności ANSI są generalnie bardziej elastyczne. Kolejną zaletą więzów ANSI jest to, że SQL Server Query Optimizer, składnik, pomagający wybrać fizyczny plan wykonywania zapytań, rozumie więzy ANSI i może z nich korzystać w podejmowaniu lepszych decyzji na temat planów dostępu.

Typy danych zdefiniowane przez użytkownika

Typy danych zdefiniowane przez użytkownika pomagają w zapewnieniu integralności dziedziny. Normalnie, gdy tworzona jest tablica, każda kolumna jest definiowana z typem danych dostarczanych przez system jak również z informacją, czy kolumna zezwala na wartości null. Można również określić własne typy danych w SQL Serverze. Należy pamiętać, że typy danych zdefiniowanych przez użytkownika należy zdefiniować, korzystając z typów danych dostarczonych przez system. SQL Server nie pozwala, na przykład na tworzenie struktur jako nowych typów danych.

Można używać tych zdefiniowanych przez użytkownika typów gdy potrzeba przetłumaczyć logiczne typy danych z modelu danych na fizyczny typ danych w SQL Serverze. Przykładowo, jeżeli zostało zmodelowane użycie kodu pocztowego w modelu danych na kilku tablicach, typ danych zdefiniowany przez użytkownika może być odpowiedni. Jeżeli podjęło się decyzję o definiowaniu modelu jako char(10), można utworzyć typ danych zwany postal_code_datatype. Typem danych jest char(10). Aby utworzyć ten typ danych, należy uruchomić następującą procedurę składową:

Exec sp_addtype postal_code_datatype, 'char(10)'

Systemowa procedura składowa sp_addtype używa następującej składni:

sp_addtype typename, phystype[, nulltype][, owner]

Znaczenie składni:

typename jest nazwą typu zdefiniowanego przez użytkownika. Typ ten jest używany zamiast systemowego typu danych w poleceniach CREATE TABLE.

phystype jest systemowym typem danych, który ma być używany gdy następuje odwołanie do typu danych zdefiniowanego przez użytkownika. W niektórych przypadkach cudzysłów jest opcjonalny, ale bezpieczniej jest używać cudzysłowia.

nulltype jest parametrem opcjonalnym. Określenie go (w cudzysłowiu) informuje o dopuszczaniu (lub nie) wartości null w kolumnie, jeżeli nie zostało to określone w poleceniu CREATE TABLE. Jeżeli ten parametr nie zostanie określony, typ danych domyślnie przyjmuje wartości domyślne systemowe w zakresie dopuszczania wartości nieokreślonych —null (w SQL Serverze 2000 jest to wartość NULL, dopóki nie zostanie zmieniona).

Dla bezpieczeństwa, nie należy opierać się na tej funkcji. Należy zawsze sprecyzować możliwość wystąpienia wartości nieokreślonych (nullability) w kolumnach w poleceniach CREATE TABLE.

owner jest również wartością opcjonalną. Określenie jej (w cudzysłowiu) determinuje właściciela danych typu definiowanego przez użytkownika. Domyślnie, użytkownik tworzący typ danych jest jego właścicielem. Typy są przechowywane w tablicy systemowej systypes w każdej bazie danych.

Przykładowo, aby utworzyć typ danych state_code_type o wartościach char(2), który domyślnie nie zezwala na wartości null, należy uruchomić kod:

Exec sp_addtype state_code_type, 'char(2)', 'not null'

Aby wykorzystać ten typ należy uruchomić

create Table mytable2

(col1 state_code_type)

col1 nie zezwala na wartości null, ponieważ ten typ danych został tak określony przez użytkownika. Kolumna ta będzie się zachowywała w ten sposób bez względu na to, czy baza danych domyślnie zezwala na wartości nieokreślone w kolumnach (NULL).

Gdy przestanie się korzystać z typu zdefiniowanego przez użytkownika (ponieważ nie był nigdy używany lub zostały usunięte wszystkie tablice korzystające z tego typu), można usunąć ten typ korzystając z systemowej procedury składowej sp_droptype:

sp_droptype typename

W tej składni, typename jest nazwą typu danych zdefiniowanego przez użytkownika, przeznaczonego do usunięcia.

Jeżeli jednak typ zdefiniowany przez użytkownika jest nadal gdzieś używany, otrzyma się błąd przy próbie usunięcia tego typu. Najpierw przed usunięciem typu danych trzeba usunąć wszystkie tablice, używające tego typu.

Jak można się było spodziewać, SQL Server Enterprise Manager dostarcza interfejsu do obsługi typów danych zdefiniowanych przez użytkownika. Aby z niego skorzystać, należy rozwinąć bazę danych, w której będą wykonywane operacje (należy pamiętać, że typy danych użytkownika są specyficzne dla bazy danych), następnie podświetlić User Defined Data Types i przejrzeć typy danych utworzone przez użytkownika w tej bazie danych. Pojawiają się one w prawym panelu konsoli SQL Server Enterprise Managera. Aby utworzyć nowy typ danych zdefiniowany przez użytkownika, należy kliknąć prawym klawiszem myszy ikonę User Defined Data Types w lewym panelu i wybrać New User Defined Data Type. Można również wybrać New User Defined Data Type z menu Akcja gdy folder User Defined Data Type jest podświetlony lub kliknąć przycisk New na pasku narzędzi Enterprise Managera (ikona podobna do żółtej gwiazdy). Bez względu na sposób, ukaże się okno właściwości User Defined Data Type Properties (zobacz rysunek 14.1).

Rysunek 14.1. Okno definiowania typów danych.

Aby dodać nowy typ danych zdefiniowany przez użytkownika, należy wpisać nazwę tego typu, wybrać systemowy typ danych, wpisać długość jeżeli jest potrzebna (opcja ta jest niedostępna, gdy nie jest potrzebna), a następnie wybrać Allow NULLs jeżeli ten typ ma domyślnie zezwalać na wartości NULL. Więcej informacji na temat wartości domyślnych i reguł zostanie podanych w dalszej części rozdziału. Należy kliknąć OK aby dodać zdefiniowany przez siebie typ danych.

Aby usunąć typ danych zdefiniowany przez użytkownika należy kliknąć prawym klawiszem myszy odpowiedni typ (lub typy danych) w prawym panelu SQL Server Enterprise Managera i wybrać z menu kontekstowego opcję Delete. W oknie Drop Objects kliknąć Drop All aby usunąć wszystkie typy zdefiniowane przez użytkownika. Nie można usunąć typu danych użytkownika, który jest używany. Aby sprawdzić, czy typ danych jest używany należy podświetlić go i kliknąć Show Dependencies, aby zobaczyć listę wszystkich obiektów, które korzystają z tego typu danych użytkownika. Następnie można zobaczyć listę wszystkich tablic i kolumn, z którymi jest używany ten typ danych.

Jeżeli chce się obejrzeć listę zależności bez usuwania typu danych zdefiniowanego przez użytkownika (zobacz rysunek 14.2), należy kliknąć dwukrotnie odpowiedni typ danych (lub kliknąć prawym klawiszem i wybrać Properties), a następnie kliknąć Where Used. Opcjonalnie, można kliknąć prawym klawiszem typ danych zdefiniowany przez użytkownika i wybrać Wszystkie zadania, Display Dependencies.

Rysunek 14.2. Przegląd listy zależności bez usuwania typu danych.

Wartości domyślne

Wartości domyślne określają jaka wartość ma być dodana do kolumny jeżeli nie została ona określona bezpośrednio dla tej kolumny. Dwoma typami wartości domyślnych w SQL Serverze są domyślne wartości więzów ANSI i osobne obiekty zwane wartościami domyślnymi (defaults). Należy posiadać uprawnienia do uruchomienia polecenia CREATE DEFAULT lub należeć do ustalonej roli bazy danych db_owner lub db_ddladmin.

Zaleca się używanie domyślnych więzów ANSI, a nie wartości domyślnych utworzonych poleceniami CREATE DEFAULT. Więzy ANSI dostarczają tego samego podstawowego zbioru funkcji, zapewniając spójność z innymi więzami ANSI.

Jak można się było domyślić z wcześniejszych sekcji, wartości domyślne tworzy się używając polecenia CREATE DEFAULT:

CREATE DEFAULT default_name

AS constant_expression

Składnia

default_name musi być unikalną nazwą w całej bazie danych (ponieważ wartości domyślne są osobnymi obiektami) i musi spełniać reguły identyfikatorów SQL Servera.

constant_expression jest stałą odpowiednią dla typu danych kolumn, które mają być używane z tym typem danych. Przykładowo, jeżeli typ danych korzysta z typu char(2), stała powinna być postaci x lub xx, gdzie x jest poprawnym znakiem. Jeżeli korzysta z typu integer, stała powinna być również liczbą całkowitą (integer). Jeżeli są to dane binarne, stała musi się zaczynać zestawem znaków 0x (jak np. 0x13 dla binarnej wartości 13). Typy danych money musza się zaczynać znakiem dolara ($). Dane typu Unicode rozpoczynają się literą N. Można również używać funkcji systemowych takich jak getdate(), dopóki zwracają one odpowiednie dane do danego typu.

Wartości domyślne są rejestrowane w systemowej tablicy sysobjects w każdej bazie danych. Tekst polecenia CREATE DEFAULT jest przechowywany w tablicy syscomments. Podobnie jak w przypadku innych obiektów w SQL Serverze 2000, definicja obiektu jest przechowywana w formacie Unicode.

Jak zostało powiedziane w rozdziale 11., można stosować wartości domyślne podczas dodawania danych przy pomocy słowa kluczowego DEFAULTS lub nie definiując danej kolumny w liście kolumn.

Decydując się na korzystanie z wartości domyślnych należy mieć świadomość kilku kwestii:

Wartości domyślne muszą być zgodne z wszelkimi regułami lub więzami sprawdzającymi (check) ANSI, które są przypisane do tych samych kolumn lub typów danych zdefiniowanych przez użytkownika. Jeżeli do kolumny przypisana jest reguła (np. w przypadku kolumny z numerem telefonu), która formatuje kolumnę jako (xxx)xxx-xxxx, domyślną wartością nie może być Unknown. Musi być to liczba postaci [phone]. Reguły zostaną krótko omówione.

Wartość domyślna nie może być niezgodna z typem danych kolumny lub typem danych zdefiniowanym przez użytkownika przypisanym do tej kolumny. Nie można przypisać wartości domyślnej Unknown do kolumny char(2). Podobnie, wartość domyślna Unknown nie działa z kolumnami liczbowymi. (W tym przypadku Unknown jest łańcuchem liter, a nie wartością null).

Wynika z tego do tej pory, że wartości domyślne stosuje się do kolumn lub typów danych zdefiniowanych przez użytkownika. Po utworzeniu, wartość domyślna staje się samodzielnym obiektem w bazie danych, który nie jest związany z żadnym szczególnym obiektem. Żeby zacząć faktycznie korzystać z wartości domyślnych należy związać je z kolumną lub typem danych zdefiniowanym przez użytkownika. W tym celu należy użyć systemowej procedury składowej sp_bindefault:

sp_bindefault defname, objname[, futureonly]

Składnia:

defname jest nazwą wartości domyślnej, która została utworzona.

objname jest nazwą obiektu, do którego ma być przypisana ta wartość domyślna i musi być ujęta w cudzysłów. Można przypisać wartość do kolumny lub typu danych zdefiniowanego przez użytkownika. SQL Server rozpoznaje, że jest to przypisanie do kolumny, jeżeli tekst w cudzysłowiu jest w formacie tablename.columnname. Jeżeli w tekście objętym w cudzysłów nie pojawi się kropka (.), można przyjąć, że chodzi o typ danych zdefiniowany przez użytkownika. Nie można przypisać wartości domyślnej do typu danych dostarczanych przez system.

futureonly dotyczy jedynie typów zdefiniowanych przez użytkownika. Za każdym razem, gdy w przyszłości zostanie użyty typ danych zdefiniowany przez użytkownika, będzie z nim związana wartość domyślna. Zaleca się nie używanie tej opcji, bo może być w niektórych przypadkach myląca. Niekiedy wartości domyślne zostały zastosowane a niekiedy nie, w zależności od tego kiedy była uruchomiona procedura systemowa sp_bindefault. Parametr musi być ujęty w cudzysłów.

Po zakończeniu przypisania, wszelkie typy danych zdefiniowane przez użytkownika lub kolumny, do których stosuje się wartość domyślna, będą korzystały z tej wartości odpowiednio podczas operacji insert.

Aby usunąć przypisanie wartości domyślnej należy uruchomić systemową procedurę składową sp_unbindefault:

sp_unbindefault objname[, futureonly]

Składnia:

objname jest takim samym parametrem jak w przypadku sp_bindefault.

futureonly, jak w przypadku sp_bindefault, stosuje się jedynie do typów danych zdefiniowanych przez użytkownika. Powoduje, że wartość domyślna nie będzie już przypisywana do typu użytkownika ale tam, gdzie została użyta wcześniej pozostanie nadal przypisana. Z tych samych przyczyn co wcześniej, nie zaleca się używania tej opcji. Nie można usunąć wartości domyślnej, dopóki nie zostanie bezpośrednio usunięte powiązanie jej z kolumnami, do których została przypisana wcześniej, przed uruchomieniem procedury sp_unbindefault z opcją futureonly.

Po usunięciu powiązania wartości domyślnej z typu danych lub kolumny, nie jest ona dłużej używana podczas operacji insert. Nie można usunąć wartości domyślnej, dopóki nie zostanie usunięte jej powiązanie z wszelkimi typami danych zdefiniowanych przez użytkownika, i kolumn.

Aby usunąć wartość domyślną , należy użyć polecenia DROP DEFAULT:

DROP DEFAULT default_name[, default_name...]

Polecenie to jasne i zrozumiałe. Pojedynczym poleceniem można usunąć tyle wartości domyślnych, ile w danym momencie potrzeba. Jeżeli użytkownik jest właścicielem tej wartości domyślnej, posiada również prawo do usuwania tej wartości. W innym przypadku, aby usunąć wartość domyślną, użytkownik musi przynależeć do ustalonej roli bazy danych db_owner lub db_ddladmin lub roli serwera sysadmin.

Można skorzystać z przykładowego kodu aby utworzyć tablicę i typ danych użytkownika, a następnie zastosować kilka wartości domyślnych:

Use pubs

Go

Exec sp_addtype my_uddt_type, 'money'

Go

CREATE DEFAULT intdefault as 0

Go

CREATE DEFAULT char5default as 'Hello'

Go

CREATE DEFAULT moneydefault as $10.00

Go

CREATE TABLE mytab

(intcol int not null,

char5col char(5) not null,

uddtcol my_uddt_type not null)

Go

Teraz należy przypisać wartości domyślne do kolumn i typu danych:

Exec sp_bindefault moneydefault, 'my_uddt_type'

Exec sp_bindefault intdefault, 'mytab.intcol'

Exec sp_bindefault char5default, 'mytab.char5col'

Go

W celu sprawdzenia należy wstawić wiersz domyślny a następnie użyć polecenia select:

INSERT mytab DEFAULT VALUES

Go

SELECT * FROM mytab

Go

W wyniku otrzyma się wartości domyślne wprowadzone do tego wiersza:

(1 row(s) affected)

intcol char5col uddtcol

-------- --------- --------

0 Hello 10.0000

(1 row(s) affected)

Można przejrzeć te wyniki przy pomocy SQL Server Enterprise Managera. W Enterprise Manager należy rozwinąć folder Databases a następnie bazę danych pubs. Podświetlić Defaults w lewym panelu, aby zobaczyć wartości domyślne (jeżeli nie są widoczne wartości właśnie utworzone należy kliknąć przycisk Refresh na pasku narzędzi). Należy kliknąć prawym klawiszem myszy ikonę Defaults i wybrać New Default aby otworzyć okno Default Properties (zobacz rysunek 14.3). Można również otworzyć to okno wybierając New Default z menu Akcja.

Rysunek 14.3. Okno domyślnych właściwości.

Należy wpisać nazwę wartości domyślnej, jaka ma być utworzona. W polu tekstowym Value należy wpisać łańcuch znaków, wartość liczbową, wartość walutową lub wartość binarną, która ma być wartością domyślną. Po rozpoczęciu wprowadzania danych w tym oknie, przycisk OK staje się dostępny. Należy kliknąć OK aby dodać wartość domyślną do bazy danych.

Po utworzeniu wartości domyślnych można je przeglądać w SQL Server Enterprise Managerze. W celu przeglądania, modyfikacji lub zmiany powiązania dla wartości domyślnej, należy kliknąć prawym klawiszem wartość domyślną i wybrać Properites (lub kliknąć wartość dwukrotnie). Jeżeli wartość domyślna ma być przypisana do jednej lub więcej kolumn w bazie danych należy kliknąć przycisk Bind Columns, , jak pokazano na rysunku 14.4.

Rysunek 14.4. Przypisanie wartości domyślnej do kolumny przy pomocy Enterprise Managera.

Należy wybrać w polu Tables wybrać tablicę, z której ma pochodzić kolumna do przypisania wartości domyślnej następnie podświetlić wybraną kolumnę lub kolumny i kliknąć przycisk Add aby przesunąć je do pola Bound Columns. Po kliknięciu przycisku Zastosuj lub OK, następuje próba powiązania. Jeżeli powiązanie nie powiedzie się pojawi się komunikat informujący o błędzie. Aby usunąć powiązanie z kolumną, należy wybrać odpowiednią tablicą, podświetlić kolumnę w polu Bound Columns a następnie kliknąć Remove.

Aby zmienić typy danych zdefiniowane przez użytkownika, należy kliknąć przycisk Bind UDTs w celu otwarcia okna Bind Default to User-Definded Data Types (zobacz rysunek 14.5). Okno to zawiera listę wszystkich istniejących typów zdefiniowanych przez użytkownika. Należy zaznaczyć pole wyboru w kolumnie Bind aby powiązać wartość domyślną z typem danych zdefiniowanym przez użytkownika.

Rysunek 14.5. Powiązanie typu danych zdefiniowanego przez użytkownika korzystając z Enterprise Managera.

Aby usunąć wartość domyślną, należy kliknąć ją prawym klawiszem myszy i wybrać Delete. Podobnie jak w przypadku typów danych zdefiniowanych przez użytkownika, można przeglądać zależności jakie posiada dana wartość domyślna. Przed usunięciem obiektu wartości domyślnej należy usunąć wszystkie powiązania jakie posiada z kolumnami i typami danych użytkownika.

Reguły

Reguły wymuszają integralność dziedziny dostarczając bardziej skomplikowanego sprawdzania poprawności wartości. Reguły są stosowane aby zapewnić, że wartości

Pasują do wzorca (podobnie jak w klauzuli like)

Pasują do listy wartości (podobnie jak w klauzuli in)

Znajdują się w zakresie wartości (podobnie jak klauzula between)

Reguły, podobnie jak wartości domyślne, są osobnymi obiektami, które wymagają aby użytkownik posiadał odpowiednie uprawnienia do tworzenia ich. Użytkownik musi przynależeć do roli db_owner lub sysadmin aby utworzyć regułę. Reguły są przechowywane w tych samych tablicach systemowych co wartości domyślne: sysobjects i syscomments. Spełnianie reguł jest sprawdzane przy wykonywaniu operacji wstawiania i modyfikacji danych (bez względu na to, czy kolumna której dotyczy reguła jest powiązana relacjami).

Ponownie zalecana jest implementacja więzów ANSI zamiast korzystania z reguł dla prawie wszystkich instancji z implementacją integralności tego typu. Jednak w szczególnych przypadkach, gdy to samo sprawdzenie integralności ma występować w 100 tablicach, lepiej jest raz utworzyć regułę, a następnie odwołać się do niej 100 razy. Mimo to, nadal zalecane są więzy ANSI, ponieważ SQL Server Query Optimizer może używać więzów podczas optymalizacji zapytań. Oznacza to, że używanie więzów zamiast reguł może przyspieszyć wykonywanie zapytań.

Można utworzyć reguły używając polecenia CREATE RULE:

CREATE RULE rulename

AS condition_expression

Składnia:

rulename jest poprawną i unikalną nazwą w bazie danych, w której jest tworzona reguła.

condition_expression jest formą warunku @variable_name WHERE clause. Klauzula WHERE może być dowolną poprawną klauzulą tego typu włączając w to operatory arytmetyczne, BETWEEN, IN, LIKE, AND, OR, NOT i inne operatory. Jednak reguła nie może odnosić się do wartości zmiennych lub do innych kolumn w bazie danych. Aby to zrobić należy skorzystać z procedur wyzwalanych (trigger) lub więzów sprawdzających.

Przykładowo, aby utworzyć regułę dla kolumny numeru częściowego, gdy kolumna musi się zaczynać od litery p lub t, można wykorzystać następującą regułę:

CREATE RULE myrule AS @myvar like 'p%' OR @myvar like 't%'

Wartość @myvar jest nazwana arbitralnie. Można nazwać ją @fredandethel w razie potrzeby. Większość ludzi tworzy nazwy zmiennych podobne do nazw kolumn lub typów danych, które są z nimi używane.

Reguły podobnie jak wartości domyślne, muszą być związane z typami danych lub kolumnami. Można je powiązać przy użyciu systemowej procedury składowanej sp_bindrule:

sp_bindrule rulename, objname[, futureonly]

Znaczenie składni:

rulename jest regułą, która ma zostać powiązana.

objname jest kombinacją tablename.columnname lub typem danych zdefiniowanym przez użytkownika.

futureonly ma takie samo znaczenie jak w przypadku sp_bindefault.

Gdy reguły zostaną powiązane, są wykorzystywane podobnie jak wartości domyślne. Przy operacji wstawiania danych, dane są sprawdzane z tą regułą czy są poprawne (czy spełniają regułę). Jeżeli nie są, wystąpi komunikat podobny do następującego:

Server: Msg 513, Level 16, State 1, Line 1A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated.
The conflict occurred in database 'pubs', table 'testtab', column 'c1'.
The statement has been terminated.

Jeżeli potrzeba usunąć powiązanie reguły, należy skorzystać z systemowej procedury składowej sp_unbindrule:

sp_unbindrule objname[, futureonly]

W tej składni, objname jest nazwą typu danych zdefiniowanego przez użytkownika lub kombinacją tablename.columnname, a futureonly ma to samo znaczenie jak w przypadku sp_unbindefault.

Aby usunąć regułę, należy uruchomić polecenie DROP RULE:

DROP RULE rulename[, rulename...]

Jeżeli jest używane polecenie DROP RULE wystarczy określić nazwy reguł, przeznaczonych do usunięcia. Podobnie jak w poleceniu DROP DEFAULT, pojedynczym poleceniem można usunąć tyle reguł ile potrzeba. Usuwane reguły nie mogą być związane z żadnymi kolumnami lub typami danych zdefiniowanych przez użytkownika.

Można skorzystać z tej samej tablicy, która była używana wcześniej aby przetestować działanie reguł i ich połączenie z wartościami domyślnymi. Następujący kod tworzy kilka reguł, które są pokrótce wyjaśnione. Skrypt zależy od wcześniej uruchomionego skryptu dotyczącego wartości domyślnych.

CREATE RULE char5rule AS @col LIKE 'h%'

Go

CREATE RULE intrule AS @intval < 100

Go

CREATE RULE moneyrule AS @moneyval BETWEEN $5.00 AND $10.00

Go

Exec sp_bindrule 'char5rule', 'mytab.char5col'

Exec sp_bindrule 'intrule', 'mytab.intcol'

Exec sp_bindrule 'moneyrule', 'my_uddt_type'

Go

A teraz należy dodać odpowiedni wiersz (bazując na tych regułach):

INSERT mytab VALUES (90,'Howdy',$6.00)

Go

Można przetestować czy wartości domyślne są zgodne z regułami:

INSERT mytab DEFAULT VALUES

Go

W każdym przypadku, otrzyma się informację (1 row(s) affected).

Teraz należy dodać nieprawidłowy zbiór wartości (w oparciu o reguły):

INSERT mytab VALUES (101,'Ralph',$20.00)

Go

SQL Server nie pozwoli na wprowadzenie niepoprawnych danych. Teraz można zobaczyć jak reguły i wartości domyślne wspaniale powiększają sposób kontroli domeny poprawnych wartości kolumn.

Oczywiście, może skorzystać z SQL Server Enterprise Managera do wykonania tych operacji. Aby otworzyć okno dialogowe Rule Properties (zobacz rysunek 14.6), należy kliknąć prawym klawiszem myszy ikonę Rules w bazie danych i wybrać New Rule. Jak poprzednio, można otworzyć to okno wybierając New Rule z menu Akcja. Okno to działa dokładnie tak samo jak w przypadku omówionego wcześniej okna Default Properties.

Rysunek 14.6. Okno właściwości reguły.

Zapewnienie poprawności danych przy pomocy więzów ANSI i mechanizmów integralności deklaratywnej

Można używać więzów ANSI i integralności deklaratywnej do zapewnienia poprawności danych. Zawierają one używanie własności IDENTITY i następujących więzów: default, check, primary key, foreign key i unique.

Własność IDENTITY

Własność IDENTITY umożliwia korzystanie z wartości generowanych przez system w tablicach. Jest to podobne do typu danych auto-numeracji w bazach danych Microsoft Access i Microsoft FoxPro. W każdej z tablic dozwolona jest jedna kolumna z własnością IDENTITY.

Na ogół, kolumny identity generują klucze przypisane do systemu. Aby wymusić integralność wpisu, należy jednoznacznie zidentyfikować każdy wiersz w tablicy. Jeżeli żadna zwykła kolumna lub zbiór kolumn tego nie robi, może być potrzebna kolumna z własnością identity.

Własność IDENTITY jest używana jedynie w przypadku kolumny typu całkowitego lub zgodnego z typem całkowitym (integer). Dlatego, można używać następujących typów danych:

tinyint bigint

smallint numeric

integer decimal

Można używać typu numeric i decimal jedynie gdy mają one skalę 0 (jak np. numeric (12,0)). Nie mogą również zezwalać na wartości null. Być może użytkownik będzie chciał używać tych typów danych, które mają większy zakres, ponieważ własność IDENTITY domyślnie nie używa ponownie wartości i nie powraca cyklicznie na początek. Gdy jest „wypełniona” nie dozwolone są wpisywania żadnych danych.

Za każdym razem gdy dokonywane jest wprowadzenie danych do tablicy z włączoną własnością IDENTITY dla kolumny, do tej kolumny dokładana jest automatycznie kolejna dostępna wartość:

IDENTITY [(seed, increment)]

Składnia:

seed jest wartością określającą początkową wartość dla kolumny identity. Jest używana dla pierwszego wiersza umieszczanego w tablicy.

increment jest wartością, która identyfikuje ilość zmian (wyrażoną liczbą całkowitą), które wystąpiły pomiędzy każdą wartością. Wartość ta może być dodatnia lub ujemna.

Jeżeli nie zostanie określona wartość seed lub increment, zostanie domyślnie przyjęta dla każdej z nich wartość 1. Czyli, pierwszy wiersz będzie miał wartość 1, następny 2 itd.

Można używać własności IDENTITY z poleceniem CREATE TABLE lub ALTER TABLE, jak omówiono w rozdziale 9. Przykładowo, następujący kod daje wartość 1 dla col1 gdy dodawany jest pierwszy wiersz, następnie wartość 101, kolejną 102 itd.:

CREATE TABLE mytable5

(col1 int not null IDENTITY(1,100),

col2 char(5) not null)

Ponieważ kolumny identity nie przechodzą cyklicznie do wartości początkowej, mogą się one przepełnić. Jako, że nie można zmienić typów danych w SQL Serverze po utworzeniu tablicy, należy mieć pewność, że wybrano wystarczająco duży typ danych do obsłużenia wszelkich możliwych do wyobrażenia wartości, które mogą się pojawić w tej kolumnie.

Jeżeli własność IDENTITY jest używana bez parametrów opcjonalnych, jest ustawiona na (1,1).

Można się odwołać do takie kolumny używając słowa kluczowego INDENTITYCOL zamiast odpowiedniej nazwy kolumny. SQL Server może zawsze określić, do której kolumny dotyczy odwołanie, ponieważ tylko jedna kolumna w tablicy może mieć ustawioną własność IDENTITY. W poprzednim przykładzie, można było uruchomić SELECT IDENTITYCOL FROM mytable5 a SQL Server zwróciłby jedynie dane dla col1.

Aby uzyskać informacje o kolumnie identity w tablicy, można uruchomić systemową procedurę składowaną sp_help, określając nazwę tablicy jako parametr. Można również używać funkcji systemowych IDENT_SEED lub IDENT_INCR:

IDENT_SEED('tablename')

IDENT_INCR('tablename')

W powyższej składni

IDENT_SEED zwróci parametr seed używany podczas tworzenia lub zmiany tablicy — polecenia CREATE TABLE lub ALTER TABLE.

IDENT_INCR zwróci parametr increment używany z poleceniami CREATE i ALTER TABLE.

Z punktu widzenia programowania, rodzi się istotne pytanie: "Skąd wiadomo jaka wartość była ostatnio dodawana"? Istnieje wartość globalna @@IDENTITY. Za każdym razem, gdy do tablicy, która ma kolumnę identity dodawana jest wartość, zmienna @@IDENTITY jest uaktualniana.

Należy wypróbować następujący kod aby zobaczyć jak działa ta zmiana. Kod korzysta z wcześniej utworzonej tablicy myidenttab.

CREATE TABLE myidenttab

(col1 int not null IDENTITY(1,100),

col2 char(5) not null)

Go

INSERT myidenttab (col2) VALUES ('howdy')

SELECT @@identity

W tym momencie, wartość zmiennej @@IDENTITY powinna wynosić 1.

INSERT myidenttab(col2) VALUES ('Movie')

SELECT @@identity

Teraz @@IDENTITY powinna wynosić 101.

Uruchomienie polecenia truncate table resetuje wartość identity z powrotem do wartości początkowej seed. Jednak zwykłe usuwania — nawet usunięcie wszystkich wierszy z tablicy — nie przynoszą tego rezultatu. Przykładowo:

DELETE myidenttab

Go

INSERT myidenttab (col2) VALUES ('Zebra')

SELECT @@identity

Teraz wartość @@IDENTITY powinna być 201, nawet jeżeli jest to jedyny wiersz w tablicy.

TRUNCATE TABLE myidenttab

Go

INSERT myidenttab (col2) VALUES ('howdy')

SELECT @@identity

Teraz @@IDENTITY powinna mieć znowu wartość 1.

Nową własnością SQL Servera 2000 jest funkcja SCOPE_IDENTITY(), która zwraca @@IDENTITY, ale z pewną kwestią wymagającą wyjaśnienia. @@IDENTITY zwraca ostatnią uaktualnioną wartość IDENTITY. SCOPE_IDENTITY() zwraca ostatnią wartość IDENTITY ale z bieżącym zakresem SCOPE. Przykład jest najlepszym sposobem aby pokazać czym różni się SCOPE_IDENTITY() od @@IDENTITY. Przypuśćmy, że istnieje procedura wyzwalana (procedury te zostaną omówione w kolejnym rozdziale) i że ta procedura wyzwalana uruchamia kod modyfikujący inną tablice z kolumną identity. Pobierając wartość zmiennej @@IDENTITY otrzymuje się wartość IDENTITY z tablicy modyfikowanej przez procedurę wyzwalaną, a nie wartość z oryginalnej tablicy, ponieważ jest to ostatnia wartość identity, która była modyfikowana. Jednak, SCOPE_IDENTITY() zwróci wartość ostatniego wiersza idenitity, który był dodawany bezpośrednio do oryginalnej tablicy. Jeżeli nie jest to jeszcze jasne, powinien to wyjaśnić następujący przykład:

Create table triggertab (col1 int not null identity(1,1), col2 char(5) null)

Create table othertab (cola int not null identity(100,1))

Create trigger mytrigger on triggertab for insert as

Insert othertab DEFAULT VALUES

insert triggertab (col2) values ('howdy')

select @@identity

select scope_identity()

(1 row(s) affected)

----------------------------------------

100

(1 row(s) affected)

----------------------------------------

1

(1 row(s) affected)

Należy zwrócić uwagę, ze wynik z @@IDENTITY jest 100, ponieważ procedura wyzwalana (trigger) z tablicy triggertab zmodyfikowała wartość IDENTITY w tablicy othertab i jest to rzeczywiście ostatnia kolumna identity, która była modyfikowana. Nie jest to zapewne spodziewany wynik, więc SQL Server 2000 posiada funkcję SCOPE_IDENTITY(), która sprawdza ostatnią wartość IDENTITY jaka została umieszczona w tablicy triggertab. We wszystkich wcześniejszych wersjach SQL Servera, wartość umieszczana w tablicy triggertab byłaby utracona.

Wartości identity są trzymane w pamięci jako pula (pool). Nie ma gwarancji, że każda wartość zostanie wykorzystana, ponieważ niektóre transakcje mogą być anulowane, a czasami może wystąpić awaria serwera. Jeżeli chce się zagwarantować, żeby kolumny identity były unikalne, należy założyć unikalny indeks na tę kolumnę. Niektórzy chcą ponownie używać wartości identity. Domyślnie nie można ręcznie umieścić wartości w kolumnach identity. Można używać jednak opcji identity_insert aby ominąć ustawienie domyślne dla pojedynczej tablicy w obrębie jednej sesji:

SET identity_insert [database.[owner.]]tablename ON|OFF

Polecenie to włącza możliwość bezpośredniego dodawania do kolumny identity w tablicy. Aby skorzystać z tej opcji, użytkownik musi być właścicielem tablicy lub przynależeć do roli sysadmin, db_owner lub db_ddladmin.

Aby użyć tej możliwości w tablicy myidenttab, należy uruchomić następujący kod Transact-SQL. Należy uruchomić go w całości z tego samego SQL Server Query Analyzera, ponieważ polecenie SET jest specyficzne dla danej sesji:

SET identity_insert myidenttab ON

Go

INSERT myidenttab (col1, col2) VALUES (2,'jolly')

Go

SET identity_insert myidenttab OFF

Go

SELECT * FROM myidenttab

Przykład ten umieszcza żądany wiersz w tablicy. Należy określić listę kolumn, nawet jeżeli zostanie określona wartość dla każdej kolumny w tablicy.

Pomimo tego, że typ danych UNIQUEIDENTIFER nie jest faktycznie powiązaniem lub opcją DRI, jest to kolejna opcja, którą można rozważać w miejsce identity (szczególnie dla kluczy podstawowych). Jeżeli zostanie zdefiniowana kolumna z typem danych UNIQUEIDENTIFIER — i ustawiona domyślnie funkcja NEWID(), która automatycznie generuje nową, globalnie unikalną wartość za każdym razem gdy dodawany jest wiersz — jest to dobry mechanizm do zapewnienia unikalności wartości klucza. Inaczej niż własność IDENTITY, wartości UNIQUEIDENTIFIER są unikalne w każdej tablicy w każdym systemie. Jednak są one znacznie większe niż typowa kolumna identity używająca typu danych integer, więc należy o tym pamiętać, używając ich zbyt często. Również, odwołanie się do 16-bajtowej liczby binarnej zamiast do prostej liczby całkowitej, jest znacznie trudniejsze.

Więzy ANSI

Więzy ANSI są funkcjonalnie bardzo zbliżone do tradycyjnych obiektów omówionych wcześniej. Nie są one osobnymi obiektami, lecz częścią definicji tablic w bazie danych. Mogą wymuszać integralność dziedziny z pomocą więzów check i default, podobnie jak wartości domyślne i reguły lub integralność referencyjna z kluczami podstawowymi i obcymi. Można również wymusić integralność encji z unikalnymi więzami lub kluczami podstawowymi.

Używanie więzów może być znaczącym ulepszeniem w porównaniu do używania wartości domyślnych i reguł. Nie ma potrzeby utrzymywania osobnego zbioru obiektów wykorzystywanego z tablicami, nie potrzeba również śledzić powiązań. Więzy są przechowywane w tablicach systemowych sysreferences, syscomments i sysobjects i możliwe są również w tablicy sysforeignkeys w każdej z baz danych.

Teraz należy przestudiować składnię więzów jako rozszerzeń poleceń CREATE TABLE i ALTER TABLE:

CREATE TABLE [database.[owner].]table_name

({col_name column_properties [constraint]

[[,] {next_col_name | next_constraint}...])

[ON filegroup]

[TEXTIMAGE_ON filegroup]

Powyższa składnia została omówiona w rozdziale 9., z wyjątkiem opcji constraint (powiązanie). Więzy mają następującą formę:

[CONSTRAINT name] Type_of_Constraint [Constraint_Options]

Pełna składnia opcji constraint jest zamieszczona poniżej (została omówiona szczegółowo w dalszej części rozdziału):

column_constraint ::= [CONSTRAINT constraint_name]

{[ NULL | NOT NULL] | [ { PRIMARY KEY | UNIQUE}

[ CLUSTERED | NONCLUSTERED]

[WITH [FILLFACTOR = fillfactor] ]

[ ON {filegroup | DEFAULT} ]]

]

| [ [FOREIGN KEY] REFERENCES ref_table [(ref_column) ]

[ON UPDATE { CASCADE | NO ACTION}]

[ON DELETE { CASCADE | NO ACTION}]

[NOT FOR REPLICATION]]

| CHECK [NOT FOR REPLICATION] (logical_expression) }

table_constraint ::= [CONSTRAINT constraint_name]

{[ {PRIMARY \ KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED]

{ ( column[,...n] ) }

[ WITH [FILLFACTOR = fillfactor] ]

[ON {filegroup | DEFAULT} ]]

| FOREIGN KEY [(column[,...n])] REFERENCES ref_table [(ref_column[,...n])]

[ON UPDATE {CASCADE | NO ACTION }]

[ON DELETE {CASCADE | NO ACTION}]

[NOT FOR REPLICATION] | CHECK [NOT FOR REPLICATION] (search_conditions)

}

ALTER TABLE table

{ [ALTER COLUMN column_name

{[ new_data_type [ (precision[, scale] ) ]

[COLLATE collation_name] [NULL | NOT NULL ]

| [ {ADD | DROP} ROWGUIDCOL ] }]

| ADD { [ column_definition ]

| column_name AS computed_column_expression [,...n]

| [ WITH CHECK | WITH NOCHECK] ADD [ <table_constraint> ]} [,...n]

| DROP { [CONSTRAINT] constraint_name | COLUMN column }[,...n]

| {CHECK | NOCHECK} CONSTRAINT {ALL | constraint_name[,...n]}

| {ENABLE | DISABLE} TRIGGER {ALL | trigger_name [,...n]} } }

Warto wiedzieć, że jest to uproszczona składnia.

Więzy przybierają dwie formy: poziomu tablicy i poziomu kolumny. Więzy poziomu kolumny są stosowane na poziomie kolumny tworzonej tablicy, a więzy poziomu tablicy są dodawane, tak jakby były dodatkowymi kolumnami. Najlepszym sposobem na rozróżnienie ich jest przykład:

Poziom kolumny:

CREATE TABLE mytablea

(col1 int not null CONSTRAINT DF_a_col1 DEFAULT (0))

Poziom tablicy:

CREATE TABLE mytableb

(col1 int not null)

ALTER TABLE mytableb ADD

CONSTRAINT DF_b_col1 DEFAULT (0) FOR col1

Opcja FOR col1 określa, do której kolumny stosują się wartości domyślne. Kolumna jest wypełniana podczas stosowania więzów poziomu kolumny.

Więzy domyślne (default)

Więzy domyślne są podobne do wartości domyślnych SQL Servera. Jednak, więzy domyślne stosuje się tylko do kolumn — nigdy do typów danych zdefiniowanych przez użytkownika. Nie można zastosować również więzów domyślnych do kolumn zdefiniowanych jako kolumny identity. Nie można również używać więzów domyślnych z kolumnami zdefiniowanymi z typem danych timestamp. Różnicą tutaj jest to, że wartość domyślna jest „częścią” kolumny, czyli inaczej niż w przypadku powiązania z daną kolumną. Więzy te są wymuszane jedynie podczas operacji wpisywania do tablicy — tak jak obiekty domyślne SQL Servera.

Poziom kolumny:

[CONSTRAINT constraint_name] DEFAULT {constraint_expression}

Poziom tablicy:

[CONSTRAINT constraint_name] DEFAULT {constraint_expression} FOR col_name

Część składni CONSTRAINT constraint_name jest opcjonalna. Część ta oznacza, że więzy są dodawane bezpośrednio (z perspektywy dokumentacji jest to zawsze dobry sposób), tak jak nadawanie nazwy więzom. Jeżeli więzy nie zostaną nazwane, nazwa domyślna wygląda mniej więcej tak:

DF_mytab_col1_117F9D94

Dlatego nazywanie poszczególnych więzów jest dobrym pomysłem, ponieważ niektóre operacje wymagają nazwania więzów.

Następnie występuje słowo kluczowe DEFAULT, a następnie albo więzy odpowiednie do typu danych, NULL lub niladic function. Niladic function zawiera następujące opcje:

CURRENT_TIMESTAMP pobiera bieżącą datę i czas. Jest odpowiednikiem polecenia SELECT getdate().

SYSTEM_USER pobiera nazwę zalogowanego aktualnie użytkownika. Jest odpowiednikiem polecenia SELECT suser_sname().

CURRENT_USER, USER i SESSION_USER wszystkie pobierają nazwę aktualnego użytkownika bazy danych. Są odpowiednikiem polecenia SELECT user_name().

Mając na myśli zakres funkcji pomiędzy więzami domyślnymi poziomu tablicy i poziomu kolumny, nie ma różnic innych niż fakt, że więzy domyślne muszą być określane na poziomie kolumny podczas operacji CREATE TABLE, a więzy poziomu tablicy lub kolumny podczas operacji ALTER TABLE.

Poniższe przykłady pokazują więzy domyślne:

CREATE TABLE defaulttab1

( intcol int NOT NULL CONSTRAINT df_intcol DEFAULT 0,

char5col char(5) NOT NULL DEFAULT 'Hello',

anumber numeric(10,0) NOT NULL

)

Go

Jak widać pierwsze powiązanie (constraint) jest nazwane, ale drugie nie i dlatego otrzyma nazwę przypisaną przez system.

ALTER TABLE defaulttab1

ADD moneycol money NULL CONSTRAINT df_moneycol DEFAULT $2.00,

CONSTRAINT df_anumber DEFAULT 100 FOR anumber

Go

Można uruchomić EXEC sp_help defaulttab1 aby sprawdzić czy więzy w kolumnie są poprawne. W wyniku zostanie pokazany raport dotyczący więzów podobny do następującego:

constraint_type constraint_name constraint_keys

----------------------------------------------------------------------

DEFAULT on column char5col DF__defaultta__char5__702996C1 ('Hello')

DEFAULT on column anumber df_anumber (100)

DEFAULT on column intcol df_intcol (0)

DEFAULT on column moneycol df_moneycol (2.0000)

No foreign keys reference this table.

No views with schema_binding reference this table.

Jak widać, powiązanie, któremu nie została nadana nazwa posiada nazwę systemową.

Więzy sprawdzające (check)

Więzy sprawdzające działają bardzo podobnie jak reguły. Dostarczają mechanizmu do wymuszenia integralności dziedziny dla kolumn. Nie ma ograniczeń co do ilości więzów sprawdzających na pojedynczej kolumnie (inaczej niż w przypadku innych więzów). Posiadają one wiele podobnych ograniczeń jak więzy domyślne, takich jak kolumny z typem timestamp lub własność IDENTITY. Więzy te są sprawdzane podczas operacji wpisywania i uaktualniania danych, podobnie jak reguły.

Więzy sprawdzające mogą wykonywać coś czego nie mogą wykonywać reguły. Więzy sprawdzające mogą się odnosić do innych kolumn tej samej tablicy jako część przestrzegania ich warunków. Można się odwoływać do innych kolumn przy pomocy więzów z poziomu tablicy.

Poziom kolumny:

[CONSTRAINT constraint_name]

CHECK [NOT FOR REPLICATION] (expression)

Poziom tablicy:

[CONSTRAINT constraint_name]

CHECK [NOT FOR REPLICATION] (expression)

Znaczenie składni:

CONSTRAINT constraint_name jest opcjonalny, podobnie jak w przypadku więzów domyślnych.

CHECK określa, że jest tworzone powiązanie sprawdzające. Expression może być dowolnym wyrażeniem podobnie jak w przypadku reguł. Jednak, tym razem można się odwołać do innych kolumn w tej samej tablicy.

NOT FOR REPLICATION powstrzymuje stosowanie powiązania sprawdzającego w przypadku gdy proces wewnętrznej replikacji dodaje dane lub uaktualnia tablicę.

Jak zostało wcześniej wspomniane, jedynie więzy poziomu tablicy mogą zawierać odniesienia do wielu kolumn.

Przykłady są prawdopodobnie najlepszym sposobem analizy działania więzów:

CREATE TABLE checktable

(col1 int not null CONSTRAINT ck_col1

CHECK (col1 between 1 and 100),

col2 char(5) null,

zip_code char(5) null,

col4 int not null,

CONSTRAINT ck_col4 CHECK (col4 > col1),

CONSTRAINT ck_zip_code CHECK

(zip_code like '[0-9][0-9][0-9][0-9][0-9]')

)

ALTER TABLE checktable

ADD CONSTRAINT ck_col2 CHECK (col2 like 'H%')

Go

Warto zwrócić uwagę, że są teraz stosowane reguły. Przykładowo,

INSERT checktable VALUES (1,'Howdy','99901',2)

zadziała, ale

INSERT checktable VALUES (2,'Howdy','8834A',3)

nie powiedzie się, zwracając komunikat:

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with COLUMN CHECK constraint 'ck_zip_code'. The conflict occurred in database 'pubs', table 'checktable', column 'zip_code'.
The statement has been terminated.

Więzy klucza podstawowego (primary-key)

Więzy klucza podstawowego łączą integralność referencyjną i integralność encji. Każda kolumna używana dla klucza podstawowego musi być zdefiniowana z atrybutem NOT NULL i może istnieć tylko jedno powiązanie (constraint) klucza podstawowego w danej tablicy. Powiązanie klucza podstawowego może być wywołane przez powiązanie klucza obcego. Niektóre procesy, takie jak replikacja lub aplikacje open database connectivity (ODBC), mogą wymagać zadeklarowanych kluczy podstawowych ANSI.

Więzy klucza podstawowego zawierają tworzenie indeksu unikalnego. Domyślnie tworzony jest unikalny indeks zgrupowany.

Poziom kolumny:

[CONSTRAINT constraint_name] [PRIMARY KEY [CLUSTERED | NONCLUSTERED]

[WITH [FILLFACTOR = fillfactor] ] [ON {filegroup | DEFAULT} ]

Poziom tablicy:

[CONSTRAINT constraint_name] [PRIMARY KEY [CLUSTERED | NONCLUSTERED]

{ (col_name[,...n] ) } [ WITH [FILLFACTOR = fillfactor] ]

[ON {filegroup | DEFAULT} ]

W tej składni, PRIMARY KEY tworzy indeks klucza podstawowego (unikalny). Wszelkie inne opcje indeksu są tutaj dozwolone, włączając w to zmianę indeksu na niezgrupowany, stosowanie współczynników wypełnienia, itd. Domyślnie, opcja ta tworzy indeks zgrupowany.

W tym przypadku nie ma różnicy w funkcjonalności pomiędzy więzami poziomu kolumny i poziomu tablicy.

Można tworzyć klucz podstawowy na pojedynczej kolumnie lub na większej ilości kolumn — do 16 kolumn, dopóki całkowity rozmiar kolumn nie przekracza 900 bajtów.

Przykład ten tworzy unikalny indeks zgrupowany na kolumnie col1 tablicy pktable:

CREATE TABLE pktable

(col1 int not null CONSTRAINT pk_col1 PRIMARY KEY,

col2 char(5) null

)

Kolejny przykład tworzy unikalny indeks niezgrupowany na kolumnie col1 tablicy pktable2:

CREATE TABLE pktable2

(col1 int not null CONSTRAINT pk2_col1

PRIMARY KEY nonclustered (col1),

col2 char(5) null

)

Ostatni przykład tworzy unikalny indeks zgrupowany na (col1, col2) tablicy pktable2:

CREATE TABLE pktable3

(col1 int not null,

col2 char(2) not null,

col3 int null,

CONSTRAINT pk3_col1col2 PRIMARY KEY (col1, col2)

)

We wszystkich instancjach można przeglądać indeksy, ale nie można manipulować nimi bezpośrednio. Przy próbie usunięcia indeksu z ostatniej tablicy, wystąpi błąd podobny do przestawionego poniżej:

Server: Msg 3723, Level 16, State 4, Line 1

An explicit DROP INDEX is not allowed on index 'pktable3.pk3_col1col2'. It is being used for PRIMARY KEY constraint enforcement.

Więzy unikalne

Więzy unikalne umożliwiają tworzenie unikalnych indeksów, tak jak umożliwiają to klucze podstawowe, ale więzy te posiadają większą elastyczność. Na ogół tworzy się więzy unikalne jeżeli jest więcej niż jedna kolumna lub zbiór kolumn, które mogą być poprawnymi kluczami podstawowymi. Używanie tych więzów realizuje dwa cele: dokumentuje potencjalny wybór klucza i zezwala na klucze obce w innych tablica jako odniesienie do więzów unikalnych (dodatkowo zezwalając na związek z więzami klucza podstawowego).

Więzy unikalne mogą być również utworzone na kolumnach, które zezwalają na wartości null. Można mieć również więcej niż jedno unikalne powiązanie w tablicy.

Poziom kolumny:

[CONSTRAINT constraint_name] [UNIQUE [CLUSTERED | NONCLUSTERED]

[WITH [FILLFACTOR = fillfactor] ] [ON {filegroup | DEFAULT} ]

Poziom tablicy:

[CONSTRAINT constraint_name] [PRIMARY KEY [CLUSTERED | NONCLUSTERED]

{ ( column[,...n] ) } [WITH [FILLFACTOR = fillfactor] ]

[ON {filegroup | DEFAULT} ]

Jak poprzednio, nazwa powiązania jest opcjonalna. Również, jak w przypadku więzów klucza podstawowego, na poziomie kolumny nie ma potrzeby wymieniania wszystkich kolumn. Wystarczy wymienić kolumnę, na której jest tworzone powiązanie.

Przykładowo, następujący kod tworzy klucz podstawowy jak również powiązanie unikalne. Obydwa są indeksami unikalnymi tablicy myuniquetable.

CREATE TABLE myuniquetable

(col1 int not null CONSTRAINT pk_myuniquetable PRIMARY KEY,

col2 char(20) NOT NULL CONSTRAINT u_myuniquetable UNIQUE

)

Więzy klucza obcego (foreign-key)

Więzy klucza obcego zapewniają integralność referencyjną pomiędzy tablicami. Tworzony jest klucz obcy na tablicy, który odwołuje się do klucza podstawowego lub powiązania unikalnego innej tablicy. Powiązanie to wykonuje jedną z trzech operacji:

ogranicza modyfikacje danych w tablicy z kluczem podstawowym dopóki tablica z kluczem obcym zawiera powiązane wiersze.

usuwa wszystkie powiązane wiersze w tablicy z kluczami obcymi

uaktualnia wartość klucza podstawowego we wszystkich wierszach relacyjnych w tablicy z kluczami obcymi

Chroni również przed dodawaniem (lub uaktualnianiem) danych w tablicy z powiązaniem klucza obcego, która nie zawierałaby poprawnych danych z tablic zależnych.

Utworzenie klucza obcego nie tworzy indeksu w tablicy; jednak może być indeksem. Dlatego, na ogół należy tworzyć tablice z kluczami obcymi zgodnie z poleceniami CREATE INDEX. Można się odwołać do tablicy w tej samej bazie danych jedynie gdy są tworzone więzy klucza obcego. Należy posiadać odpowiednie uprawnienie (SELECT lub REFERENCES) do tablicy, do której występuje odwołanie, a każda pojedyncza tablica może mieć maksymalnie 253 klucze obce wskazujące na nią. Nie można powiększyć tego limitu.

Poziom kolumny:

[CONSTRAINT constraint_name] [FOREIGN KEY] REFERENCES ref_table

[ (ref_column) ]

[ON UPDATE { CASCADE | NO ACTION}]

[ON DELETE { CASCADE | NO ACTION}]

[NOT FOR REPLICATION]

Poziom tablicy:

[CONSTRAINT constraint_name] FOREIGN KEY [(column [,...n])]

REFERENCES ref_table [(ref_column [,...n])]

[ON UPDATE { CASCADE | NO ACTION}]

[ON DELETE { CASCADE | NO ACTION}]

[NOT FOR REPLICATION]

Jak zwykle, nazwa powiązania (constraint) jest opcjonalna. Jak w przypadku innych więzów referencyjnych, nazwa kolumny nie musi być wywoływana lokalnie jeżeli jest to powiązanie pojedynczej kolumny. Również nie trzeba nazywać kolumny w innej tablicy, jeżeli kolumny mają taką samą nazwę.

Jeżeli nie zostanie określony parametr ON UPDATE lub ON DELETE, obydwa są ustawione na NO ACTION aby zachować zgodność z wcześniejszymi wersjami SQL Servera. Oznacza to, że usunięcie lub zmiany wartości klucza podstawowego nie są dozwolone jeżeli jakiekolwiek wiersze odwołują się do klucza podstawowego w tablicy (tablicach) z kluczem obcym.

Jeżeli zostanie określona opcja CASCADE, wiersze z tablicy (tablic) z kluczem obcym są usuwane lub otrzymują odpowiednio zaktualizowaną wartość kolumny referencyjnej.

W przypadku odniesienia do powiązania unikalnego klucza podstawowego wielu kolumn, należy uważać, aby odwoływać się do nich w tej samej kolejności na liście kolumn w listach FOREIGN KEY i REFERENCES. Obsługiwane są również odwołania własne, czyli można zastosować odwołanie tablicy do niej samej (z inną kolumną).

Następujący kod tworzy tablicę pracowników i tablicę zamówień (która była wypełniana przez pracownika). Aby sprawdzić, czy odpowiedni pracownik wprowadził zamówienie, można zaprogramować tę własność lub zadeklarować ją z kluczami obcymi. Następnie, gdy ktoś próbuje usunąć pracownika, osoba ta nie jest uprawniona do wykonania tego dopóki są zamówienia związane z tym pracownikiem.

CREATE TABLE emp

(emp_id int not null CONSTRAINT pk_emp PRIMARY KEY,

emp_name char(30) not null)

Go

CREATE TABLE orders

(order_id int not null CONSTRAINT pk_order PRIMARY KEY,

emp_id int not null CONSTRAINT fk_order

FOREIGN KEY (emp_id) REFERENCES emp (emp_id)ON DELETE NO ACTION ON UPDATE NO ACTION

)

Go

INSERT emp VALUES (1,'Joe Smith')

INSERT emp VALUES (2,'Ann Jones')

INSERT orders VALUES (1,1)

INSERT orders VALUES (2,2)

Go

Przykład ten działa do tej pory poprawnie. Teraz należy spróbować wpisać zamówienie dla nieistniejącego pracownika:

INSERT orders VALUES (3,3)

Go

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with COLUMN FOREIGN KEY constraint

'fk_order'.

The conflict occurred in database 'pubs',

table 'emp', column 'emp_id'.

The statement has been aborted.

Następnie należy spróbować usunąć pracownika, który posiada zamówienie:

DELETE emp WHERE emp_id = 1

Go

Server: Msg 547, Level 16, State 1, Line1

DELETE statement conflicted with COLUMN REFERENCE constraint

'fk_order'.

The conflict occurred in database 'pubs',

table 'orders', column 'emp_id'

The statement has been aborted.

Przykład odwołania się do siebie samej może mieć następującą postać, oznaczającą, że każdy menedżer musi mieć również poprawne dane jako pracownik:

CREATE TABLE emp_manager

(emp_id int not null CONSTRAINT pk_emp_mgr PRIMARY KEY,

mgr_id int not null CONSTRAINT fk_emp_mgr FOREIGN KEY

REFERENCES emp_manager (emp_id),

emp_name char(30) not null)

Te dwa polecenia insert zakończą się sukcesem, ponieważ odwołują się do poprawnych danych:

INSERT emp_manager VALUES (1,1, 'Ann Jones')

INSERT emp_manager VALUES (2,1, 'Tom Smith')

Warto jednak pamiętać, że jeżeli nastąpi odwołanie do osoby, która jeszcze nie istnieje, jak np.:

INSERT emp_manager VALUES (3,4, 'Bob Newett')

otrzyma się komunikat podobny jak poprzednio — że zostało naruszone powiązanie klucza obcego. Podejście to może być bardzo przydatne w wielu rzeczywistych scenariuszach.

Kod z wydruku 14.1 dodaje tablicę zamówień szczegółowych, która używa nowych możliwości SQL Servera 2000 do kaskadowego usuwania lub wprowadzania danych.

Wydruk 14.1 Używanie więzów kaskadowych

CREATE TABLE order_detail

(order_id int not null CONSTRAINT pk_order_detail PRIMARY KEY (order_id, line_no),

line_no int not null,

part_no int not null,

price money not null,

CONSTRAINT fk_order_detail FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE ON UPDATE CASCADE

)

-- Load some data into the table

INSERT order_detail VALUES (1,1,1,$5.00)

INSERT order_detail VALUES (1,2,2,$15.00)

INSERT order_detail VALUES (1,3,9,$2.95)

INSERT order_detail VALUES (2,1,1,$5.00)

INSERT order_detail VALUES (2,2,8,$8.00)

INSERT order_detail VALUES (2,3,4,$29.00)

-- Now delete order number 2.

DELETE orders where order_id = 2

--select * from order_detail to verify the rows for order #2 were deleted

select * from order_detail

-- Now update order #1 to be order #5 and watch it cascade to order_detail

update orders set order_id = 5 where order_id = 1

--select * from order_detail to verify the rows for order #1 were updated to be order #5

select * from order_detail

Usuwanie więzów

Można usunąć powiązanie przy pomocy polecenia ALTER TABLE. Przykładowo, aby usunąć powiązanie klucza obcego w poprzednim przykładzie należy uruchomić następujące polecenie:

ALTER TABLE emp_manager DROP CONSTRAINT fk_emp_mgr

Jednak, nie można usunąć powiązania klucza podstawowego (lub powiązania unikalnego), które ma nadal odniesienia do klucza obcego. Przykładowo, w tablicy emp_manager, próbując usunąć powiązanie klucza podstawowego, korzystając z poniższego kodu (nie usuwając wcześniej klucza obcego),

ALTER TABLE emp_manager DROP CONSTRAINT pk_emp_mgr

otrzyma się komunikat informujący o błędzie:

Server: Msg 3725, Level 16, State 1, Line2

The constraint 'pl_emp_mgr' is being referenced by table 'emp_manager',

foreign key constrait 'fk_emp_mgr'.

Server: Msg 3727, Level 16, State 1, Line 2

Could not drop constraint. See previous errors.

Zapewnienie poprawności danych przy pomocy SQL Server Enterprise Managera

Wszystkie wykonywane wcześniej czynności można wykonać korzystając z SQL Server Enterprise Managera. Aby to zrobić, należy podświetlić ikonę Tables w bazie danych, kliknąć prawym klawiszem myszy tablicę w prawym panelu i wybrać Design Table. Otworzy się okno Design Table (zobacz rysunek 14.7).

Rysunek 14.7. Projektowanie tablicy.

Aby wprowadzić w tej tablicy klucz podstawowy, należy podświetlić kolumnę lub kolumny, które mają być kluczem podstawowym a następnie kliknąć ikonę żółtego klucza w pasku narzędzi, co spowoduje ustawienie klucza podstawowego. Po ustawieniu tego klucza i zachowaniu zostanie utworzone powiązanie klucza podstawowego. Warto zwrócić uwagę, że ikona klucza została dodana do każdej kolumny w tablicy wyznaczonej do umieszczenia w kluczu podstawowym.

Aby utworzyć unikalne powiązanie dla tablicy, należy kliknąć tablicę i przycisk Index Properties na pasku narzędzi (drugi przycisk od lewej), lub kliknąć prawym klawiszem myszy dowolne miejsce w tablicy i wybrać Properties. Należy kliknąć zakładkę Indexes/Keys i wprowadzić odpowiednie informacje. W poprzednim rozdziale zostało zaprezentowane takie samo okno. Należy pamiętać, aby używać opcji Constraint po wybraniu kolumn i zaznaczeniu pola wyboru Create UNIQUE.

Aby pokazać możliwości tego okna i pomóc w studiowaniu więzów sprawdzających i więzów klucza obcego jak również więzów klucza podstawowego i klucza unikalnego, należy utworzyć tablicę nazwaną ConstraintTab w bazie danych pubs. Po utworzeniu tablicy, należy dodać następujące kolumny w widoku projektowania tablicy:

Nazwa kolumny	Typ danych	Długość	Zezwalanie na wartości Null
pkcol	int
fkcol	int		Check
checkcol	char	10
defaultcol	int
altpkcol	int

Należy podświetlić kolumnę pkcol i kliknąć ikonę żółtego klucza aby dodać powiązanie klucza podstawowego. Również, należy nadać kolumnie pkcol własność identity klikając prawym klawiszem obszar tablicy wybierając Properties a następnie wybierając pkcol jako Table Identity Column (akceptując domyślne wartości seed i increment). Należy podświetlić wiersz defaultcol i ustawić Default Value na wartość 0. Po zakończeniu wprowadzania, tablica powinna wyglądać podobnie jak przedstawiona na rysunku 14.8.

Rysunek 14.8. Okno zaprojektowanej tablicy ConstraintTab.

W tym momencie została utworzona tablica z powiązaniem domyślnym na kolumnie defaultcol i kluczem podstawowym na kolumnie pkcol. Teraz należy utworzyć jeszcze jedną tablice nazwaną reltab (dla tablicy relacyjnej) z pojedynczą kolumną pkcol typu int, not null, i ustawić ją jako klucz podstawowy.

Teraz należy kliknąć prawym klawiszem myszy folder Diagrams i wybrać New Database Diagram. Używając kreatora Create Database Diagram Wizard, należy kliknąć Dalej, a następnie wybrać tablice ConstraintTab i reltab (zobacz rysunek 14.9).

Rysunek 14.9. Okno Kreator tworzenia diagramu bazy danych.

Należy kliknąć Dalej a następnie Finish. SQL Server Enterprise Manager poinformuje, że żądane tablice zostały dodane do diagramu bazy danych. Należy kliknąć OK aby zobaczyć nowy diagram bazy danych (zobacz rysunek 14.10).

Rysunek 14.10. Początkowy diagram bazy danych.

Należy kliknąć kolumnę fkcol w tablicy ConstraintTab i przytrzymać klawisz myszy. Przeciągnąć tę kolumnę nad tablicę reltab i upuścić. Pojawi się okno pokazane na rysunku 14.11, pokazujące zależności klucza podstawowego — klucza obcego, jakie zostały właśnie utworzone. Należy zaakceptować wartości domyślne i kliknąć OK.

Rysunek 14.11. Nowoutworzona relacja.

Warto zauważyć jak zmienił się diagram: relacja jest przedstawiona w formie linii pomiędzy tablicami (zobacz rysunek 14.12). Można kliknąć prawym klawiszem linię aby zmienić własności relacji lub przesunąć tablicę w dowolny sposób, w jaki ma być przedstawiona ta relacja.

Rysunek 14.12. Diagram bazy danych pokazujący nowoutworzoną relację.

Należy zapisać diagram akceptując nazwę domyślną (jeżeli jest to pierwszy tworzony diagram, powinna być nazwa DIAGRAM1). SQL Server Enterprise Manager pokazuje następnie listę tablic, które zostały zmienione i pyta czy je zachować. Należy kliknąć Yes aby zapisać wykonane zmiany integralności referencyjnej.

Należy powrócić do tablicy ConstraintTab i otworzyć widok projektowania (kliknąć prawym klawiszem myszy tablicę i wybrać Design Table). Następnie należy kliknąć prawym klawiszem kolumnę fkcol i wybrać Properties. Na zakładce Relationships, należy przejrzeć relacje jakie zostały utworzone z tablicą reltab ( zobacz rysunek 14.13).

Rysunek 14.13. Okno ustawiania powiązania klucza obcego.

Teraz należy kliknąć zakładkę Check Constraints a następnie kliknąć przycisk New aby utworzyć nowe powiązanie sprawdzające (check). Należy uzupełnić informacje w oknie, jak pokazano na rysunku 14.14 aby dodać powiązanie sprawdzające.

Rysunek 14.14. Okno ustawiania powiązania sprawdzającego.

Na końcu, należy dodać powiązanie unikalne, jak opisano wcześniej przez wybór nowego indeksu na zakładce Indexes/Keys i okna Index Properites oraz uzupełnienie okna jak pokazano na rysunku 14.15. Po ukończeniu, należy zapisać tablicę i zamknąć okno Design Table.

Rysunek 14.15. Okno ustawiania powiązania unikalnego.

Teraz należy uruchomić systemową procedurę składową sp_help dla tej tablicy korzystając z SQL Server Query Anaylzera i przejrzeć powiązania:

EXEC sp_help constrainttab

Warto zauważyć, że w tej tablicy zostało utworzone powiązanie (constraint) każdego rodzaju, jak również została ustawiona własność IDENTITY. Wszelkie funkcje związane z więzami oraz własnością IDENTITY są w pełni przedstawione w SQL Server Enterprise Managerze.

Opóźnianie stosowania więzów

Można opóźnić stosowanie więzów typu sprawdzającego i więzów klucza obcego. Więzy klucza podstawowego, unikalne i domyślne nie mogą być opóźniane, ponieważ więzy klucza podstawowego i więzy unikalne tworzą indeksy, a więzy domyślne nigdy nie są sprawdzane dla wcześniejszych danych. Kiedy tablica zostanie zmieniona i zostanie dodane jedno z tych powiązań i określona opcja NOCHECK, istniejące dane nie są sprawdzane. Jeżeli tablica zostanie zmieniona i zostanie dodane powiązanie sprawdzające (check), bieżące dane są sprawdzane pod kątem poprawności jak w poniższym przykładzie:

CREATE TABLE mytesttable

(col1 char(5) not null

)

Go

INSERT mytesttable VALUES ('Howdy')

INSERT mytesttable VALUES ('Grant')

Teraz należy spróbować utworzyć powiązanie, którego sprawdzenie nie powiedzie się dla niektórych danych:

ALTER TABLE mytesttable

ADD CONSTRAINT ck_mytest CHECK (col1 like 'h%')

Komunikat:

Server: MSg 547, Level 16, State 1

ALTER TABLIC statement conflicted with COLUMN CHECK constraint

'ck_mytest'. _The conflict occurred in database 'pubs', table 'mytesttable', column 'col1'.

Jeżeli są tworzone niektóre powiązania z opcją NOCHECK, w tym przypadku kod zadziała:

ALTER TABLE mytesttable

WITH NOCHECK

ADD CONSTRAINT ck_mytest CHECK (col1 like 'h%')

To samo dotyczy więzów klucza obcego. Należy być ostrożnym w używaniu tej opcji ponieważ może to powodować, że w bazie danych będą niepoprawne dane.

Wyłączanie więzów

Można również czasowo wyłączyć więzy, czyli będzie możliwe dodanie nieprawidłowych danych, które naruszają więzy sprawdzające lub dane, które naruszają więzy klucza obcego. Aby to zrobić, należy uruchomić polecenie ALTER TABLE z opcją WITH NOCHECK CONSTRAINT constraintname lub WITH NOCHECK ALL aby wyłączyć wszelkie więzy sprawdzające i więzy klucza obcego.

Kontynuując poprzedni przykład, następujący kod wyłączy wszystkie więzy sprawdzające:

ALTER TABLE mytesttable

NOCHECK CONSTRAINT ALL

Można wtedy dodać nieprawidłowe dane, które naruszają powiązanie sprawdzające. Można włączyć więzy ponownie uruchamiając polecenie:

ALTER TABLE mytesttable

CHECK CONSTRAINT ALL

2 Część I ♦ Podstawy obsługi systemu WhizBang (Nagłówek strony)

2 P:\Aneta\SQLSER~1\SQLSER~1\r14-1.doc

---