create table – DDL tworzenie tabel w bazie danych

Pracując z bazą danych prędzej czy później będziemy chcieli utworzyć własną tabelę, która będzie miała przechowywać dla nas jakie informacje. Warto poświęcić chwilę nad rozmyślaniem. Koniecznie trzeba odpowiedzieć sobię na pytanie do czego ma ta tabela służyć. Można powiedzieć że pod kątem użytkowym najważniejsze rodzaje tabel jakie można wyróżnić to:

tabela transakcyjna – przechowujemy w niej np nagłówki faktur, często dopisujemy tam jakieś dane i często je odczytujemy. Raczej nie usuwamy z niej danych, lub wręcz nie pozwalamy by danych nie było można usunąć.

tabela słownikowa – Tabela której zawartość niezbyt często się zmienia, ale jest często odpytywana. Przykładem takiej tabeli może być np tabela z informacją o użytkownikach systemu. System często pyta się o dane użytkowników ale rzadko je zmienia.

tabela log – do takiej tabeli wrzucamy informacje o zmianach, często taka tabela bardzo szybko rośnie. Rzadko do niej zaglądamy.

Jeszcze należy spojrzeć od strony danych na nasz przyszłościowy twór. Jakich potrzebujemy restrykcji? Czy nasza tabela będzie referencyjną czy referowaną? A może miks?

tabela referencyjna – np tabela z nagłowkiem faktur. Posiada klucz główny (Primary Key). Do niej odwołują się inne tabele, np tabela z pozycjami faktury posiada identyfikator nagłówka.
tabela referowana – Tabela która posiada klucz obcy innej tabeli. Np tabela pozycji dokumentów. Warto się zastanowić nad właściwościami klucza obcego.
tabela miks- ma swój własny klucz główny, posiada klucze obce. W sumie najczęściej spotykany przypadek. Tabela nagłówków faktur posiada najczęściej identyfikator klienta jako klucz obcy.

Następny etap to uzmysłowienie sobie w jakim kontekście będziemy wykorzystywać tą tabele?
Czy chcemy pytać się o konkretne wiersze? Jeśli tak to na jakiej podstawie?

Na przykładzie tabeli z fakturami. Często będziemy pytać się o listę faktur(np dla danego klienta, za dany okres). Najczęściej jednak będzie interesować nas konkretna faktura.

Na przykładzie tabeli pozycji, to będą nas raczej interesować wszystkie rekordy należące do określonego dokumentu. Czasem zapytamy się o to jakie towary znalazły się na fakturach w globalu.

Teraz trzeba się zastanowić nad formą tabeli. Musimy być świadomi istnienia formatu danych. Baza inaczej traktuje liczby, inaczej napisy i daty. Patrząc globalnie to takie 3 typy obsługuje praktycznie każda baza danych.

typy liczbowe reprezentują liczby (dziwne nie?), są one rozbite na podtypy. Możemy wyróżnić liczby całkowite oraz liczby zmiennoprzecinkowe. Kolejnym podziałem jest wielkość liczby. Ze względu na ograniczenia pamięci i chęć stosowania stałego rozmiaru wiersza przez bazę danych musimy określić przedział liczbowy jaki chcemy przechowywać w pamięci.
Patrząc najprościej baza udostępnia nam ileś typu podając przedziały liczb które możemy tam zapisać. Podając liczbę z przedziału mamy pewność że ona się tam zmieści. Baza dokładnie zna rozmiar każdego wiersza i może szybko przeszukiwać pliki w celu odnalezienia wiersza, który nas interesuje. Niestety różne systemy bazodanowe różnie traktują typy danych. Typy są różnie ponazywane i mają różne właściwości. W zapytaniach będę starał się korzystać z typów które działają na każdej bazie danych.

Po nudnym wstępie możemy przejść do konkretów. Zaczynając od podstaw zdefiniujmy naszą tabelę przyjaciele:

create table przyjaciele (
id int,
imie varchar2(255),
plec int,
wiek int,
zarobki int,
kolor int);

objaśniając powyższy kod:
create table – to konstrukt (słowo kluczowe)
kolejno mamy nazwe tabeli. Systemy bazodanowe określają normy nazewnictwa. Ogólnie przyjeło się iż nazwy tabel nie zawierają polskich znaków, spacji i innych udziwnień.
Następnie mamy nawias w którym po przecinku wymieniamy listę kolumn i ich właściwości.

Zaczynamy od nazwy kolumny, zaleca się stosowanie podobnych zasad jak w przypadku nazw tabel. O sposobach nazewnictwa pisałem między innymi we wpisie odnośnie klucza głównego bądź modelu relacyjnego.

Następnie podajemy typ kolumny czyli format danych. Format liczby całkowitej nazywa się w większości systemów ‘int’. Z typami zmiennoprzecinkowymi jest różnie niektóre systemy akceptują zapis number(x,y) gdzie x to maksymalna ilość znaków przed przecinkiem, a y to ilość znaków po przecinku. Analogiczny zapis (w MSSQL) to decimal(x,y).

W przypadku zapisu ciągów znaków nazywanych ‘string’ w bazach danych operujemy nazwą varchar, jest ona raczej powszechnie akceptowalna. W nawiasie podajemy maksymalna ilość znaków jaką chcemy zapisać w kolumnie. Przyjęło się nie podawać wartości większej niż 255. Kiedyś się po prostu nie dało, w chwili obecnej można podawać w zależności od systemu wartości idące w tysiące, tylko po co?

Załóżmy że chcielibyśmy aby nasza tabela przechowywała datę urodzenia przyjaciela. Jeśli wykonaliśmy poprzednie polecenie staniemy przed pewnym problemem. Otóż albo zmodyfikujemy tabele albo po prostu ją usuniemy. W chwili obecnej założymy że usuwamy tabele ( nie mamy w niej jeszcze danych)

drop table przyjaciele;

Pisałem o tym w innych wpisach. Tworzenie tabel i ich usuwanie, tak samo jak ich czyszczenie to operacje typu ddl.
Więc są to operacje nie obsługiwane przez transakcje. Cofnięcie takiej operacji jest czasem nie możliwe. Więc należy być ostrożnym.

Wracając do tematu daty. W różnych systemach jest to różnie realizowane ale najczęściej są dostępne typy date, datetime. Należy zajrzeć do dokumentacji i zobaczyć jak są one realizowane. Generalnie data jest zapisywana jako ilość sekund jakie upłyneły od jakiegoś tam momentu. By zapisać datę do bazy najczęściej będziemy potrzebowali jakiejś funkcji. Niektóre RDBMS umożliwiają zapisanie daty bezpośrednio ze stringa(napisu) ale jest to zły nawyk. Różne kraje stosują różny zapis daty RRRR-MM-DD, DD-MM-RRRR itd. MSSQL potrafi na źle wpisanej dacie wykonać dziwne operacje, więc lepiej uważać.

create table przyjaciele (
id int,
imie varchar2(255),
plec int,
wiek int,
zarobki int,
kolor int,
data_urodzin date);

Rozważmy też opcję iż chcemy zapisywać informację o tym kiedy kogoś uznaliśmy za przyjaciela. Powinniśmy skorzystać z typu datetime lub timestamp (Znacznik czasu). W różnych systemach znów mamy różne implementacje. Jednak najbardziej uniwersalne jest stworzenie triggera(wyzwalacza). Narazie tylko utwórzmy pole, które w przyszłości będzie przechowywać datę utworzenia:

create table przyjaciele (
id int,
imie varchar2(255),
plec int,
wiek int,
zarobki int,
kolor int,
data_urodzin date,
data_wstawienia datetime);

Teraz trochę o atrybutach. Nie chcemy by nasze ID mogło być puste. Istnieje atrybut not null

create table przyjaciele (
id int not null,
imie varchar2(255),
plec int,
wiek int,
zarobki int,
kolor int,
data_urodzin date,
data_wstawienia datetime);

wymusza on przy insercie sprawdzenie czy kolumna ma przypisaną jakąś wartość. Sprawdzenie następuje after insert na moment przed zakończeniem transakcji. Tak więc w triggerze before insert można dodać taką wartość.

możemy także rozważyć sytuację w której chcemy przypisać jakąś wartość do wiersza, jest to tak zwane default value. Czyli wartość która zostanie przypisana w przypadku gdy pominiemy ją w insercie.

create table przyjaciele (
id int not null,
imie varchar2(255),
plec int default 1,
wiek int,
zarobki int,
kolor int,
data_urodzin date,
data_wstawienia datetime);

Do płci przypiszemy wartość 1 w każdym insercie.

Warto także utworzyć klucz główny czyli jednoznaczny identyfikator wiersza

create table przyjaciele (
id int not null Primary Key,
imie varchar2(255),
plec int default 1,
wiek int,
zarobki int,
kolor int,
data_urodzin date,
data_wstawienia datetime);

Więcej szczegółów o primary key można przeczytać tutaj

Leave a Reply

Your email address will not be published. Required fields are marked *

nine − 1 =