Wykład ten poświęcony jest językowi DDL, będącemu podzbiorem poleceń SQL odpowiedzialnych za operacje na obiektach bazy danych – ich tworzenie, modyfikację i usuwanie. Omówione są operacje dotyczące podsatwowych obiektów bazy danych takich jak tabele, widoki, więzy integralności. W tym wykładzie pominięto operacje na indeksach, pozostawiając to zagadnienie do omówienia na przedmiocie Administracja Bazami Danych,
W Wykładzie 8 zostanie omówiona składnia poleceń języka SQL należących do podzbioru DDL, czyli polecenia odpowiedzialne za zarządzanie obiektami bazy danych – ich tworzenie, modyfikację i usuwanie. W tym wykładzie ograniczymy się wyłącznie do obiektów definiowanych w SQL, takich jak tabele, widoki, więzy integralności, indeksy. Obiekty programistyczne (procedury, wyzwalacze itp.) zostaną omówione w dalszych wykładach.
Polecenia utworzenia, modyfikacji i usunięcia obiektów zaczynają się zawsze od tych samych słów kluczowych i mają ustaloną składnię:
Tabele tworzone są poleceniem:
CREATE TABLE nazwa_tabeli (
Nazwa_kolumny typ_danych [więzy_spójności],
...);
Więzy spójności danych (ang. Constraints) to zespół reguł, które gwarantują
logiczną spójność danych wprowadzanych i przechowywanych w bazie. Zadaniem
więzów spójności jest zagwarantowanie tego, aby dane w bazie danych wiernie
odzwierciedlały świat rzeczywisty, dla opisu którego baza danych została
zaprojektowana. Więzy spójności definiowane są na etapie projektowania bazy
danych, tworzone wraz z innymi obiektami, przy tworzeniu bazy. Za ich realizację
(przestrzeganie zdefiniowanych przez nie reguł) odpowiada system bazy danych.
System bazy danych musi zagwarantować, że więzy spójności pozostaną prawdziwe
przy wszystkich operacjach wykonywanych na bazie danych w postaci transakcji,
akcji wyzwalaczy, ładowania danych do bazy danych czy ich importu.
Więzy spójności mogą być sprawdzane zarówno po stronie serwera jak i po stronie aplikacji klienckich. Jednak bardziej naturalnym miejscem sprawdzania jest serwer, który obsługuje wszystkie możliwe aplikacje utworzone dla bazy danych. Lepszym rozwiązaniem jest utworzenie jednego, spójnego mechanizmu, gwarantującego spełnienie więzów spójności, niż wielu, potencjalnie niespójnych.
Podstawowymi metodami określania i realizacji więzów spójności na serwerze są:
Więzami spójności encji nazywamy reguły, pozwalające zdefiniować ograniczenia dla wartości dopuszczalnych w wierszu tabeli:
Więzy klucza głównego definiowane są na jednej, lub kilku kolumnach. Wartości w tych kolumnach jednoznacznie identyfikują wiersz, czyli muszą być unikalne. W kolumnach klucza głównego nie jest dopuszczalna wartość NULL. Na kolumnach tworzących klucz główny automatycznie jest zakładany indeks. Dla jednej tabeli tworzony jest tylko jeden klucz główny.
Definiowanie: PRIMARY KEY
Tak jak więzy klucza głównego, wartości w określonych kolumnach jednoznacznie identyfikują wiersz, czyli muszą być unikalne. W kolumnach klucza jednoznacznego jest dopuszczalna wartość NULL. Na kolumnach tworzących klucz jednoznaczny automatycznie jest zakładany indeks. Dla jednej tabeli można utworzyć więcej niż jeden klucz jednoznaczny.
W przypadku tych więzów w MS SQL Server istnieje pewien problem. W kolumnie (kolumnach) klucza jednoznacznego UNIQUE dopuszczalna jest wartość NULL, ale niedopuszczalne jest jej powtarzanie (!).
Definiowanie: UNIQUE
W kolumnie, dla której zdefiniowane są więzy NOT NULL, nie dopuszcza się wystąpienia wartości NULL. Więzy tego typu nie powinny być deklarowane na kolumnie klucza obcego związków rekurencyjnych.
Definiowanie: NOT NULL
Więzy CHECK pozwalają zdefiniować warunek, który ma być spełniony dla wszystkich wierszy w tabeli.
Definiowanie: CHECK (warunek…)
Więzy DEFAULT stanowią definicję wartości domyślnej dla kolumny. W przypadku pominięcia w instrukcji INSERT wartości dla kolumny, na której zdefiniowano więzy DEFAULT, zostanie w niej umieszczona wartość domyślna.
Definiowanie: DEFAULT wartość domyślna
Więzy spójności referencyjnej pozwalają zdefiniować zależności pomiędzy wartościami zapisanymi w kolumnach wiążących tabele w układzie klucz_główny – klucz_obcy. Wymuszają one następujące zależności:
Definiowanie: FOREIGN KEY REFERENCES nazwa_tabeli_nadrzędnej
Napisz instrukcję tworzącą tabelę EMP.
CREATE TABLE EMP ( Empno INT PRIMARY KEY, Ename VARCHAR(10) NOT NULL, Job VARCHAR(9), Mgr INT REFERENCES EMP, Hiredate DATETIME, Sal NUMERIC(7,2), Comm NUMERIC(7,2), Deptno INT NOT NULL REFERENCES DEPT, CONSTRAINT Chk_Emp_1 CHECK (Comm < Sal) );
Więzy integralności mogą być definiowane w jednej linii z deklaracją nazwy kolumny i jej typu, albo po zadeklarowaniu wszystkich kolumn, poprzedzone słowem CONSTRAINT. System bazy danych automatycznie nadaje więzom integralności nazwy domyślne. W przypadku definiowania więzów „poza linią” możemy nadać więzom własną nazwę. Poniżej przedstawimy trzy przykłady tworzenia tabeli OSOBA {Id_Osoba, Imie, Nazwisko} z różnym rozwiązaniem deklaracji więzów.
Napisz instrukcję tworzącą tabelę OSOBA {IdOsoba, Imie, Nazwisko}. Przedstaw różne sposoby deklaracji więzów klucza głównego.
CREATE TABLE OSOBA ( IdOsoba INT PRIMARY KEY, Imie VARCHAR(20), Nazwisko VARCHAR(50) );
CREATE TABLE OSOBA ( IdOsoba INT, Imie VARCHAR(20), Nazwisko VARCHAR(50) PRIMARY KEY (IdOsoba) );
CREATE TABLE OSOBA ( IdOsoba INT, Imie VARCHAR(20), Nazwisko VARCHAR(50) CONSTRAINT PK_Osoba PRIMARY KEY (IdOsoba) );
Sposób generowania jednoznacznych numerów wierszy, na ogół w celu realizacji „sztucznego” klucza głównego, nie jest unormowany przez standard języka. Rozwiązanie może zostać zrealizowane przez procedurę lub wyzwalacz. Zarówno MS SQL Server, jak i ORACLE oferują rozwiązania tego problemu, jednak różnią się one pomiędzy sobą w sposób zasadniczy.
Rozwiązaniem jest wykorzystanie właściwości IDENTITY kolumny tabeli, z zadeklarowanym typem danych Integer.
Napisz instrukcję tworzącą tabelę OSOBA {IdOsoba, Imie, Nazwisko}. Zapewnij automatyczną generację unikalnej wartości klucza głównego (MS SQL Server).
CREATE TABLE OSOBA ( IdOsoba INT IDENTITY PRIMARY KEY, Imie VARCHAR(20), Nazwisko VARCHAR(50) );
Przy wpisywaniu nowego rekordu do tabeli, nie podajemy wartości klucza głównego (w powyższym przykładzie kolumna IdOsoba). Jest ona generowana automatycznie. Ostatnio wprowadzoną wartość IDENTITY (w danej sesji) można odczytać ze zmiennej systemowej @@IDENTITY.
Dyrektywa IDENTITY może występować w składni uproszczonej (jak w poprzednim przykładzie), lub pełnej:
IDENTITY (Seed, Increment)
Gdzie:
Seed – pierwsza wartość wstawiona do kolumny
Increment – wartość przyrostu kolejnej wartości
Jeżeli Seed i Increment nie zostały zadeklarowane, przyjmą domyślne wartości (1, 1).
W pewnych przypadkach działanie IDENTITY może zostać wyłączone i wówczas dane do kolumny mogą być wprowadzane explicite.
Instrukcją wyłączającą automatyzm IDENTITY jest:
SET Identity_Insert nazwa_tabeli ON;
Ponowne włączenie działania IDENTITY uzyskujemy poleceniem
SET Identity_Insert nazwa_tabeli OFF;
W ORACLE służy do tego celu specjalna klasa obiektów - Sekwencja (Sequence).
Utworzenie nowej instancji:
CREATE SEQUENCE nazwa_sekwencji (seed, increment)
Wpisując nowy rekord do tabeli, jako wartość klucza głównego podajemy kolejną wartość wygenerowaną w sekwencji:
nazwa_sekwencji.NextVal
Odczytanie ostatnio wygenerowanej wartość w sekwencji (w ramach bieżącej sesji).
nazwa_sekwencji.CurrVal
Słowem kluczowym polecenia zmiany schematu tabeli, tak jak każdego obiektu, jest ALTER. Jednak implementacje całego polecenia różnią się w MS SQL Server i ORACLE.
ALTER TABLE OSOBA ADD Pesel CHAR(11);
ALTER TABLE OSOBA ADD (Pesel CHAR(11));
ALTER TABLE OSOBA ALTER COLUMN Pesel VARCHAR(15);
ALTER TABLE OSOBA MODIFY (Pesel VARCHAR2(15));
ALTER TABLE OSOBA DROP COLUMN Pesel;
ALTER TABLE OSOBA DROP (Pesel);
Zmiana więzów spójności zawsze sprowadza się do modyfikacji tabeli, w której więzy istnieją, lub mają zostać dodane. Nie istnieje możliwość użycia instrukcji CREATE, ALTER, DROP w zastosowaniu bezpośrednio do więzów.
ALTER TABLE Nazwa_tabeli ADD CONSTRAINT Nazwa_więzów_spójności RODZAJ_WIĘZÓW (Definicja_więzów);
ALTER TABLE Nazwa_tabeli ADD (CONSTRAINT Nazwa_więzów_spójności RODZAJ_WIĘZÓW Definicja_więzów);
Jeśli klucz obcy w tabeli A odwołuje się do klucza głównego w tabeli B, a klucz obcy w tabeli B odwołuje się do klucza głównego w tabeli A, to mamy do czynienia z cyklicznymi więzami referencyjnymi. W takim przypadku wykonujemy instrukcje CREATE TABLE, nie definiując kluczy obcych. Następnie za pomocą instrukcji ALTER TABLE wprowadzamy więzy REFERENCES, odwołujące się do istniejących już tabel.
Na kolumnie Deptno tabeli EMP utwórz więzy referencyjne, odwołujące się do tabeli DEPT.
ALTER TABLE EMP ADD CONSTRAINT fk_Emp_dept FOREIGN KEY (Deptno) REFERENCES Dept ON DELETE SET NULL;
Jak już wcześniej wspomniano, dla kolumny może zostać zdefiniowana wartość domyślna, co pozwala pomijać ją przy wprowadzaniu danych do tabeli.
ALTER TABLE Nazwa_tabeli ADD CONSTRAINT Nazwa_więzów_spójności DEFAULT Wartość FOR Nazwa_kolumny;
W specyfikacji wartości domyślnej mogą występować wyrażenia, zawierające stałe i funkcje SQL, także funkcje User i Getdate (SQL Server) / Sysdate (ORACLE).
W przypadku warunku zdefiniowanego przez więzy CHECK, akceptowane są te wiersze, dla których ten warunek przyjmuje wartość TRUE lub NULL(!). System nie dopuszcza do pojawienia się wiersza, dla którego zdefiniowany warunek przyjmuje wartość FALSE (operacja kończona jest błędem).
Do tabeli EMP dodaj więzy integralności CHECK gwarantujące, że sumaryczna kwota płacy (Sal) i prowizji (Comm) nie przekroczy wartości 10 000.
ALTER TABLE EMP ADD CONSTRAINT Chk_sal_comm CHECK (Sal + Isnull(Comm, 0) < 10000);
Konieczność użycia w definicji więzów CHECK funkcji Isnull, wynika z tego, iż w kolumnie Comm dopuszczalne jest wystąpienie NULL.
Jeżeli instrukcja DELETE (lub instrukcja DROP TABLE) zamierza usunąć wiersz tabeli nadrzędnej, do którego istnieją odwołania z tabeli podrzędnej, musi zostać podjęte działanie, które nie dopuści do niespójności danych w bazie. Dopuszczenie do usunięcia takiego wiersza beż żadnej innej akcji prowadziło by do pojawienia się w tabeli podrzędnej tzw. „osieroconych rekordów”, czyli rekordów zawierających w kolumnie klucza obcego wartości, których już nie ma wśród zbioru wartości klucza głównego tabeli nadrzędnej. Inaczej mówiąc, klucz obcy tabeli podrzędnej wskazywał by na rekordy już nie istniejące w tabeli nadrzędnej. Identyczny problem (choć rzadko spotykany), może wystąpić przy zmianie wartości klucza głównego tabeli nadrzędnej.
W celu zapobieżenia takiej sytuacji może być podjęta jedna z następujących akcji:
Razem z wierszem tabeli nadrzędnej zostają usunięte wszystkie wiersze tabeli podrzędnej, w których wartości klucza obcego wskazują na usuwany wiersz (pod warunkiem, że usunięcie tych wierszy jest możliwe bez naruszenia innych więzów referencyjnych). W MS SQL Server to rozwiązanie nie jest dopuszczalne na związku rekurencyjnym (odwołującym się do tej samej tabeli). ORACLE taką możliwość dopuszcza.
Przy usuwaniu wiersza, do którego są odwołania przez wartości kluczy obcych, następuje wstawienie pseudo-wartości NULL lub wartości domyślnej kolumny, jako wartości klucza obcego. W MS SQL Server ta opcja została wprowadzona w wersji 2005.
Przy usuwaniu wiersza, do którego są odwołania przez wartości kluczy obcych, podnoszony jest błąd, a polecenie DELETE jest wycofywane. Jest to domyślne ustawienie w ORACLE i MS SQL Server.
Identyczne akcje mogą zostać zdefiniowane dla operacji UPDATE.
Wyłączenie (usunięcie) więzów integralności referencyjnej, może prowadzić w opisanej powyżej sytuacji do powstania niespójności danych w bazie.
Wszystkie więzy spójności usuwane są instrukcją o poniższej składni:
ALTER TABLE Nazwa_tabeli DROP CONSTRAINT Nazwa_więzów;
Jedyną możliwością zmiany działania więzów spójności, jest ich usunięcie i utworzenie nowych, zmodyfikowanych.
Usuwanie tabeli z bazy danych sprowadza się do polecenia o prostej konstrukcji:
DROP TABLE Nazwa_tabeli;
Jeżeli istnieją tabele, które mają klucze obce odwołujące się do usuwanej tabeli i nie zostały zadeklarowane inne akcje referencyjne niż ON DELETE NO ACTION, operacja nie powiedzie się (zostanie zakończona błędem).
Perspektywa (widok - view) to obiekt bazy danych, będący nazwaną i zapamiętaną w bazie danych definicją wykonania zapytania SELECT. Raz utworzona perspektywa może być wielokrotnie użyta. Perspektywa ma postać “wirtualnej” tabeli i może być używana tak samo jak tabela.
Podstawową rolą perspektyw jest dostosowania bazy danych do potrzeb różnych grup
użytkowników. Stanowią one “perspektywę”, z jakiej dana grupa użytkowników
postrzega bazę danych. Różne grupy użytkowników mogą mieć odmienne spojrzenie na
te same dane w bazie danych, w zależności od potrzeb i zainteresowań. Ułatwia to
precyzyjne użycie danych. Ponadto perspektywy stanowią element ochrony przed
niepowołanym lub nieprawidłowym dostępem do danych - każdy użytkownik bazy
danych ma dostęp tylko do danych dotyczących jego działalności w firmie.
Perspektywy z założenia, wynikającego z postulatów Cood’a, umożliwiają
wykonywanie instrukcji SELECT, INSERT, DELETE i UPDATE tak jak
tabele. Operacje te, poza SELECT, mają swoje ograniczenia,
które zostaną omówione w dalszej części wykładu.
CREATE VIEW Nazwa_perspektywy [(nazwa_kolumny, …)] AS zapytanie;
Pod pojęciem zapytania należy rozumieć praawidłową składniowo instrukcję SELECT.
Nazwy kolumn mogą być nadane explicite w definicji perspektywy. Jeżeli się tam nie pojawią, zostaną użyte aliasy kolumn w zapytaniu, lub przejęte oryginalne nazwy kolumn z tabel wchodzących w skład zapytaniu.
Wiersze zwracane przez perspektywę nie są zapisywane w bazie, lecz wyliczane przy każdym odwołaniu do perspektywy. W bazie przechowywana jest sama jej definicja. Stąd każde odwołanie do perspektywy może dawać inny zestaw wynikowych rekordów.
Utwórz perspektywę URZEDNICY, zwracającą dane (Empno, Ename, Sal) pracowników zatrudnionych na stanowisku CLERK.
CREATE VIEW Urzednicy (Numer, Nazwisko, Placa) AS SELECT Empno, Ename, Sal FROM EMP WHERE Job = 'CLERK';
Jeżeli w definicji perspektywy ma znaleźć się klauzula sortowania wierszy ORDER BY, w klauzuli SELECT definiującej perspektywę musi znaleźć się słowo TOP, określające liczbę zwracanych rekordów. Problem ten nie występuje w ORACLE.
Utwórz perspektywę URZEDNICY, zwracającą dane (Empno, Ename, Sal) pracowników zatrudnionych na stanowisku CLERK, sortującą wiersze według nazwisk (Ename).
CREATE VIEW URZEDNICY (Numer, Nazwisko, Placa) AS SELECT TOP 100 PERCENT Empno, Ename, Sal FROM EMP ORDER BY Ename;