SQL CREATE | DROP TABLE - OracleDev

Tabela jest podstawową formą przechowywania danych w bazie Oracle. W tym kursie pokażę jak zarządzać tabelami w bazie danych. Kurs SQL CREATE | DROP TABLE jest też kursem w którym pokażę jak zacząć pracę z językiem SQL. W tym kursie:

Pokażę jak tworzyć tabele przy pomocy instrukcji SQL: CRETE TABLE
- Wyświetlenie tabeli SQL: DESC TABLE
Poznamy CONSTRAINTY a w szczególności:
- SQL: PRIMARY KEY czyli klucz główny tabeli
- SQL: FOREIGN KEY czyli klucz obcy tabeli
Pokażę jak usuwać tabele przy pomocy instrukcji SQL: DROP TABLE
- Błąd Oracle ORA-02449

Jeżeli jesteś początkujący w SQL odwiedź wcześniejsze kursy:

Ten kurs jest częścią cyklu Kurs SQL

Zapisz się na autorskie Szkolenie SQL

Przygotowanie

Baza danych w dużej mierze składa się tabel. W poprzednich kursach opisywałem czym są bazy danych, jak powstają, czym jest tabela oraz jakie typy danych przechowuje. W kursie dotyczącym normalizacji bazy danych projektowaliśmy bazę tak aby spełniała 3 postać normalną. W trakcie projektowania bazy danych wyszliśmy z jednej tabeli która reprezentowała całą bazę danych pizzeri. Tabela wyglądała następująco:

Aż do spełnienia 3 postaci normalnej. Finalnie baza wyglądała jak poniżej:

Baza ta składa się z 5 powiązanych tabel. W poniższym kursie utworzymy ją i będziemy na niej operować.

CRETE TABLE

Tabela jest podstawową formą przechowywania danych w bazie Oracle. Aby móc utworzyć tabelę należy określić jej nazwę, kolumny jakie posiada tj. ich nazwy oraz typy i dodatkowo znać jej powiązania z innymi tabelami.

Do utworzenia tabeli w języku SQL służy polecenie CREATE TABLE

CREATE TABLE nazwa_tabeli (
    nazwa_pierwszej_kolumny    typ_danych_kolumny_pierwszej
 ,  nazwa_drugiej_kolumny      typ_danych_kolumny_drugiej
     ...
 ,  nazwa_x_kolumny            typ_danych_kolumny_x
);

Na podstawie powyższego schematu utwórzmy bazę danych dla pizzeri składającą się z jednej dużej tabeli. Tabela która wymyśliliśmy “przed normalizacją”. Tabelę tą nazwijmy PIZZERIA wraz z kolumnami:

IMIE typu VARCHAR2(30 CHAR)
NAZWISKO typu VARCHAR2(30 CHAR)
NAZWA typu VARCHAR2(30 CHAR)
ROZMIAR typu VARCHAR2(3 CHAR)
CENA typu NUMBER
ADRES typu VARCHAR2(100 CHAR)
DATA_ZAMOWIENIA typu DATE

Przykładowe polecenie do utworzenia tabeli może wyglądać zgodnie z poniższym SQL:

CREATE TABLE PIZZERIA (
    IMIE VARCHAR2(30 CHAR)
  , NAZWISKO VARCHAR2(30 CHAR)
  , NAZWA VARCHAR2(30 CHAR)
  , ROZMIAR VARCHAR2(3 CHAR)
  , CENA NUMBER
  , ADRES VARCHAR2(100 CHAR)
  , DATA_ZAMOWIENIA DATE
);

Gdy wywołamy powyższą instrukcję SQL: CREATE TABLE powinniśmy z bazy otrzymać komunikat “Table PIZZERIA created.” Jeżeli taki komunikat otrzymaliśmy to oznacza, że tabela została utworzona.

DESC TABLE

Aby podejrzeć strukturę tabeli można wykonać polecenie DESC które pokaże nam kolumny tabeli np. poprzez wywołanie:

DESC PIZZERIA;

powinniśmy otrzymać wynik:

Name           Null?  Type               
-------------- ------ ----
IMIE                  VARCHAR2(30 CHAR)  
NAZWISKO              VARCHAR2(30 CHAR)  
NAZWA                 VARCHAR2(30 CHAR)  
ROZMIAR               VARCHAR2(3 CHAR)   
CENA                  NUMBER             
ADRES                 VARCHAR2(100 CHAR) 
DATA_ZAMOWIENIA       DATE

Jak widzimy, wynik zwraca nazwy kolumn tabeli wraz z ich typami.

CONSTRAINT

PRIMARY KEY

PRIMARY KEY (klucz główny) to kolumna lub kilka kolumn które oznaczają jednoznacznie i unikalnie dany wiersz w obrębie całej tabeli. Każda tabela może mieć jeden klucz główny, który w efekcie nazywa wiersz i zapewnia, że nie istnieją żadne zduplikowane wiersze. Primary key może być naturalny lub wygenerowany. Naturalny oznacza, że same dane gwarantują, unikalność rekordu. Dla przykładu w bazie pizzeri kluczem taki może zostać kolumna ROZMIAR ponieważ nie ma możliwości aby istniały dwie ceny dla tego samego rozmiaru. Innym przykładem naturalnego klucza może być pesel osób bo niemożliwe jest aby dwie osoby posiadały taki sam numer pesel. Klucz główny generowany to numer nadawany przez bazę, najczęściej jako kolejne wartości z sekwencji liczbowej np. ID. Kolumny które są częścią klucza głównego nie mogą zawierać wartości null.

Utwórzmy więc pierwszą tabelę z kluczem głównym czyli CENNIK przechowującą rozmiary oraz ceny pizzy. Tabela ta przechowywać będzie dwie kolumny: rozmiar pizzy określony jako L, XL, XXL oraz cenę pizzy a kluczem głównym kolumny zostanie kolumna ROZMIAR.

PRIMARY KEY w SQL zapisujemy jako:

CONSTRAINT nazwa_klucza_głównego PRIMARY KEY (nazwa_kolumna_lub_kolumny)

Utwórzmy więc taką tabelę wraz z kluczem głównym:

CREATE TABLE CENNIK (
    ROZMIAR VARCHAR2(3 CHAR)
  , CENA    NUMBER
  
  , CONSTRAINT CENNIK_PK PRIMARY KEY (ROZMIAR) 
);

Nałożenie PRIMARY KEY na kolumnę ROZMIAR spowodowało, że nie ma możliwości aby dwa rekordy w bazie miały tą samą wartość w kolumnie rozmiar lub żeby kolumna ta miała wartość null.

FOREIGN KEY

FOREIGN KEY (klucz obcy) jest oznaczeniem kolumny lub kilku kolumn jako referencja do kolumn innej lub tej samej tabeli. Referencja ta jest typem relacji w której występuje rodzic i dziecko. Oznacza to, że np. rodzic może mieć wiele dzieci oraz, że każde dziecko może mieć co najwyżej jednego rodzica. Istnieje możliwość zdefiniowania foreign key tak aby wskazywał na null w tabeli rodziców ale nie może wskazywać, na wartość rodzica która nie występuje. W przypadku naszej bazy FOREIGN KEY może być kolumna ROZMIAR ale z tabeli z menu pizzy.

FOREIGN KEY w SQL zapisujemy jako:

CONSTRAINT nazwa_klucza_obcego FOREIGN KEY (nazwa_kolumny_lub_kolumn_z_tabeli) REFERENCES nazwa_tabeli_do_której_referujemy(nazwa_kolumny_z_tabeli_do_której_referujemy)

Utwórzmy więc kolejną tabelę przechowującą MENU z kolumnami: ID, NAZWA oraz ROZMIAR. Nałóżmy na ID klucz główny generowany automatycznie oraz klucz obcy na kolumnę ROZMIAR.

CREATE TABLE MENU (
    ID      NUMBER GENERATED ALWAYS AS IDENTITY
  , NAZWA   VARCHAR2(20 CHAR)
  , ROZMIAR VARCHAR2(3 CHAR)
  
  , CONSTRAINT MENU_PK PRIMARY KEY (ID)
  , CONSTRAINT MENU_CENNIK_FK FOREIGN KEY (ROZMIAR) REFERENCES CENNIK(ROZMIAR)
);

Przy nowej tabeli MENU widzimy dwie nowe rzeczy:

kolumna ID jest typu number z adnotacją “GENERATED ALWAYS AS IDENTITY”. Oznacza to, że w przypadku gdy będziemy dodawać nowy rekord do tabeli MENU baza sama podstawi nam kolejną unikalną wartość do kolumny ID.
CONSTRAINT FOREIGN KEY czyli klucz obcy do tabeli CENNIK

Dzięki zastosowaniu klucza obcego każdy rekord w tabeli MENU musi mieć odnośnik do tabeli CENNIK. Istnieje możliwość wstawienia do tej kolumny wartość NULL natomiast nie ma możliwości wstawienia wartości która nie występuje w tabeli CENNIK.

DROP TABLE

Wiemy już jak tworzyć tabele. Co natomiast w sytuacji gdy nie chcemy utworzonej tabeli i chcemy ją usunąć? Najprościej jest użyć dedykowanego polecenia które w SQL wygląda tak:

DROP TABLE nazwa_tabeli;

Skoro znamy już składnie usuńmy jedną z utworzonych tabel poleceniem:

DROP TABLE PIZZERIA;

W odpowiedzi powinniśmy otrzymać komunikat

Table PIZZERIA dropped.

Oracle błąd: ORA-02449

Czasami przy próbie usunięcia tabeli otrzymujemy błąd ora-02449. Spróbujmy więc usunąć tablę CENNIK poleceniem SQL:

DROP TABLE CENNIK;

Otrzymamy komunikat błędu:

Error report -
 ORA-02449: tabela ma unikatowe/główne klucze, do których odwołują się obce klucze
 00000 -  "unique/primary keys in table referenced by foreign keys"
 *Cause:    An attempt was made to drop a table with unique or
        primary keys referenced by foreign keys in another table.
 *Action:   Before performing the above operations the table, drop the
        foreign key constraints in other tables. You can see what
        constraints are referencing a table by issuing the following
        command:
        SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

Błąd ten oznacza, że próbujemy usunąć tabelę do której występują referencje. I faktycznie tak jest w tym przypadku, do tabeli CENNIK referuje tabela MENU. Dlatego też dopóki tabela MENU istnieje lub posiada referencje do tabeli CENNIK nie możemy jej usunąć. Tak więc aby usunąć tabelę CENNIK pierw musimy usunąć tabelę MENU.

DROP TABLE MENU;
DROP TABLE CENNIK;

oraz wynik:

Table MENU dropped.
Table CENNIK dropped.

Podsumowanie

Aby utworzyć tabelę należy użyć polecenia: CREATE TABLE nazwa_tabeli
Aby wyświetlić kolumny tabeli należy użyć polecenia: DESC nazwa_tabeli
Aby usunąć tabele należy użyć polecenia: DROP TABLE nazwa_tabeli
Gdy pojawi się błąd ORA-02449 należy poszukać referencji i je usunąć.
CONSTRAINT PRIMARY KEY ( klucz główny) to kolumna lub kilka kolumn które oznaczają jednoznacznie i unikalnie dany wiersz w obrębie całej tabeli
CONSTRAINT FOREGIN KEY (klucz obcy) jest oznaczeniem kolumny lub kilku kolumn jako referencja do kolumn innej lub tej samej tabeli

Zadanie

Należy utworzyć całą bazę danych zgodną z schematem z 3 postaci normalnej zgodnej z poniższym diagramem.

Kod SQL czyli odpowiedź do zadania znajduje się poniżej.

CREATE TABLE CENNIK (
    ROZMIAR VARCHAR2(3 CHAR)
  , CENA    NUMBER
  
  , CONSTRAINT CENNIK_PK PRIMARY KEY (ROZMIAR) 
);

CREATE TABLE MENU (
    ID      NUMBER GENERATED ALWAYS AS IDENTITY
  , NAZWA   VARCHAR2(20 CHAR)
  , ROZMIAR VARCHAR2(3 CHAR)
  
  , CONSTRAINT MENU_PK PRIMARY KEY (ID)
  , CONSTRAINT MENU_CENNIK_FK FOREIGN KEY (ROZMIAR) REFERENCES CENNIK(ROZMIAR)
);

CREATE TABLE KLIENT (
    ID       NUMBER GENERATED ALWAYS AS IDENTITY
  , IMIE     VARCHAR2(20 CHAR)
  , NAZWISKO VARCHAR2(20 CHAR)
  
  , CONSTRAINT KLIENT_PK PRIMARY KEY (ID)
);

CREATE TABLE ADRES (
    ID_ADRESU     NUMBER GENERATED ALWAYS AS IDENTITY
  , ULICA         VARCHAR2(20 CHAR)
  , NR_BLOKU      VARCHAR2(10 CHAR)
  , NR_MIESZKANIA VARCHAR2(10 CHAR)
  , MIASTO        VARCHAR2(20 CHAR)
  
  , CONSTRAINT ADRES_PK PRIMARY KEY (ID_ADRESU)
);

CREATE TABLE ZAMOWIENIA (
    ID_PIZZA        NUMBER
  , ID_KLIENTA      NUMBER
  , ID_ADRESU       NUMBER
  , DATA_ZAMOWIENIA DATE
  
  , CONSTRAINT ZAM_PIZZA_FK FOREIGN KEY (ID_PIZZA) REFERENCES MENU(ID)
  , CONSTRAINT ZAM_KLIENT_FK FOREIGN KEY (ID_KLIENTA) REFERENCES KLIENT(ID)
  , CONSTRAINT ZAM_ADRES_FK FOREIGN KEY (ID_ADRESU) REFERENCES ADRES(ID_ADRESU)
);