Relacje między tablicami

Powiązania pomiędzy tablicami związane są z pojęciami klucza głównego i klucza obcego.

Klucz główny jest atrybutem (kolumną) zapewniającym unikalność rekordu w obrębie tabeli, w której wystepuje. W relacyjnej bazie danych, każda relacja (tabela) musi zawierać klucz główny.

Klucz obcy jest kolumną, która nie musi być unikatowa w obrębie tabeli, w której występuje, ale jest unikatowa w tabeli, do której odnosi się ta kolumna. Oznacza to więc, że musi istnieć tabela, w której występuje kolumna będąca kluczem głównym, do której odnosi się kolumna będąca kluczem obcym w innej tabeli. Należy zauważyć, że nie zachodzi zależność odwrotna, tzn. w tabeli z kluczem głównym, do którego odnosi się klucz obcy z innej tabeli, nie musi istnieć kolumna, będąca kluczem obcym dla innej tabeli.

Na tablicę z kluczem głównym mówi się potocznie tablica rodzic, natomiast na tablicę z kluczem obcym mówi sie potocznie tablica dziecko.

Przykład:

Mamy cztery tablice dotyczące realizacji zamówień pewnych towarów. W każdej z tych tablic występuje co najmniej jeden klucz główny i/lub obcy. Oto te tablice:

Tabela zamowienia zawiera klucz główny id_zamowienia oraz klucz obcy id_klienta, który odwołuje się do klucza głównego id_klienta w tabeli klient.

Tabela towary zawiera klucz główny id_towaru, który jest kluczem obcym w tabeli dostawy.

Tabela dostawy jest tutaj dość specyficzną tabelą. Zawiera ona bowiem tylko dwie kolumny, z których każda jest kluczem obcym. Kolumna id_zamowienia odwołuje się do klucza głównego w tabeli zamowienia, natomiast klumna id_towaru odwołuje się do klucza głównego w tabeli towary.

Choć nie ma tu takiego przykładu, to możliwe jest utworzenia klucza głównego, który złożony jest z kilku kolumn. Wtedy unikalność rekordów jest zapewniona dzięki istnieniu unikalnych par danych. Możliwe jest zatem również zdefiniowanie w taki sposób klucza obcego.

Często jest tak, że klucz główny w tabeli jest jednocześnie kluczem obcym. Wtedy klucz taki identyfikuje unikalnie rekordy w tabeli, jak również odwołuje się do rekordów z innej tabeli. Zwykle wskazuje to na relację jeden do wielu pomiędzy wierszami dwóch tabel.

Co to wszystko nam daje?

Jeśli w tabeli zamowienia mamy w polu id_klienta wartość, która nie odpowiada żadnej z wartości klucza głównego w tabeli klient to mamy problem. Oznacza to bowiem, że mamy zamówienie i nie wiemy kto je złożył. Co prawda, możemy w aplikacji, która z takiej bazy danych korzysta, wprowadzić odpowiednie sprawdzenie i reakcję na tego typu błędy, to jednak dużo bezpieczniej i wygodniej jest dbać o integralność danych w samej bazie danych. A zatem definiowanie relacji pomiędzy danymi w różnych tabelach umożliwia nam zachowanie logicznej integralności danych, w taki sposób aby można było bezpiecznie i logicznie (a jednocześnie nie powtarzając danych) wprowadzać i przechowywać dane i korzystać z nich.

Dane w takich tabelach.

Sprawdźmy jakie kluczowe dane znajdują się w tablicy zamowienia

mysql> SELECT id_zamowienia, id_klienta FROM zamowienia;

+---------------+------------+  | id_zamowienia | id_klienta |  +---------------+------------+  |             1 |          3 |  |             2 |          8 |  |             3 |          8 |  |             4 |          9 |  |             5 |         16 |  |             6 |         18 |  +---------------+------------+

Jak widać, mamy w tabeli zamowienia 6 wierszy, w których wartości z kolumny id_klienta odwołują się do danych z kolumny o takiej samej nazwie w tabeli klient. W tabeli zamowienia wystepują odwołania do klientów o numerach 3,8,9,16,18. Odwołania dotyczą tylko pięciu wierszy ponieważ wiersze z identyfikatorami id_zamowienia o wartościach 2 i 3 odwołują się do tego samego klienta. Domyślamy się więc, że klient o identyfikatorze id_klienta o wartości 8 złożył dwa zamówienia.

Spróbujmy teraz usunąc z tabeli klient wiersz o identyfikatorze id_klienta równym 3:

mysql> DELETE FROM klient WHERE id_klienta='3';

ERROR 1030: Got error 1000011 from table handler

MySQL otrzymał od procedury obsługującej tabele sygnał o istnieniu wiersza podrzędnego i zablokował usuwanie rekordu. Bez problemu natomiast możemy usuwac wiersze, które nie posiadają wierszy podrzędnych.

mysql> DELETE FROM klient WHERE id_klienta='4';

Query OK, 1 row affected (0.00 sec)

Jak widać, tym razem można było bez problemów usunąć odpowiedni rekord z tabeli klient, gdyż w tym przypadku do wiersza o identyfikatorze id_klienta o wartości równej 4 nikt nie odwoływały się żadne inne dane z innych tabel.

Sprawdzanie dotyczy również prób wstawienia do tabeli zamowienia wiersza, który odwołuje się do danych z tabeli klient.

mysql> INSERT INTO zamowienia(id_zamowienia,id_klienta)        VALUES(DEFAULT,'250');

ERROR 1030: Got error 1000010 from table handler

Jak widać wystąpi błąd podczas próby wstawienia rekordu do tablicy zamowienia, jeśli w tablicy klient nie będzie odpowiedniego rekordu o identyfikatorze id_klienta równym 250. Wynika to bezpośrednio ze zdefiniowania kluczy obcych i głównych w tablicach zamowienia oraz klient.

Jeszcze jeden ważny problem

Czasasmi zdarza się, że musimy zmienić wartości kluczy głównych id_klienta w tabeli klient, a do nich odwołują sie przecież klucze obce z tabeli zamowienia. Nie da się łatwo przeprowadzić tej operacji, gdyż klucz obcy z tabeli zamowienia zablokuje tą operację z powodu braku odpowiedniego wiersza w tabeli klient.

Nie możemy zmienić wartości id_klienta w tabeli zamowienia, ponieważ pozycja w tabeli klient jeszzce nie istnieje; nie możemy również zmienić wartości w tabeli klient, ponieważ do tej wartości odwołuje się klucz obcy id_klienta z tabeli zamowienia.

Jak sobie z tym zatem poradzić? Standard SQL wprowadza słowo DEFERRABLE dodawane na końcu definicji klucza obcego podczas definicji tablicy (lub zmiany tej definicji). Jeśli zastosuje się opcję DEFERRABLE, wtedy baza danych będzie pozwalała na naruszenie klucza obcego ale jedynie wewnątrz transakcji. Jak wiadomo, transakcje są grupami instrukcji SQL, które musza być wykonane w całości lub niewykonane w ogóle. Z tego powodu możemy rozpocząć transakcję, zmienić wartość id_klienta w tabeli klient, nastepnie zmienić id_klienta w wierszach tabeli zamowienia, które odwoływały się do poprzedniej wartości i zatwierdzić transakcję.

Jednak, z tego co wiadomo, MySQL nie umożliwia stosowanie opcji DEFERRABLE podczas definiowania kluczy obych. Zamiast tego wprowadza opcje ON DELETE CASCADE lub ON DELETE SET NULL oraz ON UPDATE CASCADE lub ON UPDATE SET NULL.

Jeśli w definicji klucza obcego dodamy ON DELETE CASCADE i spróbujemy usunąć wiersz z tabeli rodzic, wtedy procedury osbługujące tabele (relacyjne InnoDB) automatycznie usuną też wiersze w tabeli dziecko, które zawierają w kolumnach będących kluczami obcymi takie same wartości jak te, które były kluczami głównymi w tablicy rodzic, i które zamierzamy usunąć. jeśli w definicji klucza obcego dodamy ON DELETE SET NULL i spróbujemy usunąć wiersz z tabeli rodzic, wtedy procedury osbługujące tabele (relacyjne InnoDB) automatycznie zmienią dane w rekordach z kluczami obcymi w tabeli dziecko nadając tym polom wartość NULL nie bacząc nawet na deklarację SET DEFAULT podczas definicji tablicy.

Podobnie działają polecenia ON UPDATE CASCADE oraz ON UPDATE SET NULL.

Jest to więc dośc wygodne rozwiązanie, gdyż w przypdaku definicji kluczy głównych i obcych w naszych tabelach relacyjnych, podczas ich modyfikacji zostanie zachowana logiczna struktura danych. Trzeab jednak mieć świadomość rozwiązań jakie stosuje MySQL podczas próby zmiany lub usuwania kluczy głównych.

Aby powiedzieć o relacjach w MySQL trzeba najpierw powiedzieć o tablicach w MySQL. A zatem:

Tablice w MySQL

MySQL udostępnia dwa rodzaje tablic: transakcyjne i nietransakcyjne.

• Pierwotną tablicą w MySQL była nietransakycjna tablica ISAM, zastąpiona później tablicą MyISAM.

• W wersji 3.23.0 MySQL-a wprowadzono tablice MyISAM oraz HEAP. Tablice MyISAM są następcami tablic ISAM, natomiast tablice HEAP są tablicami do przechowywania danych wyłącznie w pamięci operacyjnej komputera. Istnieją też tablice typu MERGE, dzięki którym możemy przetrzymywać identyczne tablice typu MyISAM w postaci jednej tablicy MERGE. Wszystkie te trzy rodzaje tablic, umożliwiają dokonywanie operacji nietransakcyjnych.

• Tablice InnoDB oraz BDB wprowadzone w późnych wersjach 3.23 MyQL-a, umożliwiające dokonywanie działań transakcyjnych na danych. Używanie ich nie jest konieczne. Można przy konfiguracji bazy danych zablokować możliwość korzystania (lub znacznie ograniczyć to korzystanie) z takich tablic.

• Tablice NDB Cluster, które używane są przez klastry bazodanowe, w których baza danych jest rozproszona na kilku komputerach.

Podczas tworzenia tablicy, można zadeklarować jej konkretny typ. Należy w tym celu dodać jedno z dwóch następujących wyrażeń podczas pisania polecenia CREATE TABLE: ENGINE = INNODB; lub TYPE = MEMORY; (gdzie INNODB, MEMORY są dwoma rodzajami tablic wymienionymi wyżej).

Na przykład:

CREATE TABLE tab (i INT) ENGINE = INNODB; CREATE TABLE tab (i INT) TYPE = MEMORY;

Jeśli ominiemy opcję ENGINE lub TYPE, wtedy domyślnym rodzajem tablicy, który zostanie utworzony poleceniemm CREATE TABLE będzie typ zdefiniowany w zmiennej table_type (najczęściej jest to typ MyISAM).

Aby zmienić rodzaj istniejącej już tablicy, można posłużyć się poleceniem ALTER TABLE, na przykład:

ALTER TABLE tab ENGINE = MYISAM; ALTER TABLE tab TYPE = BDB;

Jeśli spróbujemy urzyć tablicy takiego typu, który nie został wkompilowany w MySQL-a (bo na przykład podczas instalacji nie zadano sobie trudu), wtedy MySQL nie zgłosi błędu, tylko utworzyzamiennie tablicę typu MYISAM i zwróci ostrzerzenie (ang. warning) o tym fakcie.

MySQL zawsze tworzy pliki `.frm', które przechowują definicję tablicy i definicje kolumn. Indeksy i dane mogą być przechowywane w jednym lub kilku inyych plikach, zależnie od rodzaju tablicy. Serwer bazy danych tworzy pliki `.frm' niezależnie od systemu składowania danych na poziomie zwyczajnych operacji składowania wykonywanych przez bazę danych.

Każda baza dnych może zawierać tablice różnego typu. Nie ma ograniczenia do jednego typu tablic w jednej bazie danych.

Tablice transakcyjne mają względem tablic nietransakcyjnych wiele zalet:

• są bezpieczniejsze. Nawet jeśli serwer bazy danych MySQL nagle przerwie swoje działanie (na przykład z powodu uszkodzenia komputera) to i tak można odtworzyć dane, albo przy pomocy automatycznemu mechanizmowi odtwarzania danych, albo z backupu i danych dziennika transakcji.

• Możemy wykonywać wiele poleceń jako jedno, które zostaną rzeczywiście wykonane wszystkie w jednym czasie. (Jest to właśnie idea transakcji). (COMMIT)

• Możemy cofnąć wszystkie zmiany (ROLLBACK) jeśli coś się nie powiodło.

• Jeśli zmiany w danych nie powiodą się, wtedy wszystkie zmiany zostaną odtworzone do postaci przed transakcją. W przypadku tablic nietransakcyjnych wszystkie zmiany są permanentne.

• System transakcji daje znacznie bezpieczniejsze i prostsze zasady konkurencyjności dostępu do danych. Na przykład kiedy jeden z klientów chce dane zmienić a drugi jednocześnie chce je odczytać, to system transakcji pozwala zablokować dostęp do danych na tą krótka chwile zmian w bazie danych.

Tablice nietrasakcyjne mają jednak pewne zalety względem tablic transakcyjnych. Wszystkie one wynikają z braku narzutu mnogości operacji związanych z transakcjami.

• operacje na tablicach nietransakcyjnych są znacznie szybsze.

• nie potrzeba tak dużej ilości wolnego miejsca na dysku.

• mniejsza ilość pamięci jest potrzebna do wykonywania zmian w danych tablic.

Można zatem kombinować dwa rodzaje tablic (transakcyjnych i nietransakcyjnych) nawet w tym samym wyrażeniu SQL, w celu uzyskania jak najlepszych rezultatów, tzn. bezpieczeństwa i szybkości jednocześnie. Jednak trzeba zaznaczyć, że podczas transakcji z wyłączoną opcją autocommit, zmiany w tablicach nietransakcyjnych i tak są nadal wykonywane automatycznie, tak, jakby opcja autocommit była włączona. Nie można zatem cofnąć zmian.

Tworzenie, usuwanie i zmiana struktury tablic w MySQL

• CREATE TABLE

Składnia polecenia CREATE TABLE jest następująca

CREATE   [TEMPORARY] TABLE [IF NOT EXISTS] nazwa_tablicy   [(definicja_tabeli,...)]   [opcje] [wyrazenie_select];

lub

CREATE   [TEMPORARY] TABLE [IF NOT EXISTS] nazwa_tab   [(] LIKE nazwa_innej_tab [)];

definicja_tabeli   definicja_kolumny  | [CONSTRAINT [symbol]] PRIMARY KEY [typ_indeksu] (nazwa_kol,...)  | KEY [nazwa_indeksu] [typ_indeksu] (nazwa_kol,...)  | INDEX [nazwa_indeksu] [typ_indeksu] (nazwa_kol,...)  | [CONSTRAINT [symbol]] UNIQUE [INDEX]     [nazwa_indeksu] [typ_indeksu] (nazwa_kol,...)  | [FULLTEXT|SPATIAL] [INDEX] [nazwa_indeksu] (nazwa_kol,...)  | [CONSTRAINT [symbol]] FOREIGN KEY     [nazwa_indeksu] (nazwa_kol,...) [definicja_relacji]  | CHECK (expr)

definicja_kolumny:   nazwa typ [NOT NULL | NULL] [DEFAULT wartosc_domyslna]     [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']     [definicja_relacji]

typ:    TINYINT[(dlugosc)] [UNSIGNED] [ZEROFILL]   | SMALLINT[(dlugosc)] [UNSIGNED] [ZEROFILL]   | MEDIUMINT[(dlugosc)] [UNSIGNED] [ZEROFILL]   | INT[(dlugosc)] [UNSIGNED] [ZEROFILL]   | INTEGER[(dlugosc)] [UNSIGNED] [ZEROFILL]   | BIGINT[(dlugosc)] [UNSIGNED] [ZEROFILL]   | REAL[(dlugosc,dziesietne)] [UNSIGNED] [ZEROFILL]   | DOUBLE[(dlugosc,dziesietne)] [UNSIGNED] [ZEROFILL]   | FLOAT[(dlugosc,dziesietne)] [UNSIGNED] [ZEROFILL]   | DECIMAL(dlugosc,dziesietne) [UNSIGNED] [ZEROFILL]   | NUMERIC(dlugosc,dziesietne) [UNSIGNED] [ZEROFILL]   | DATE   | TIME   | TIMESTAMP   | DATETIME   | CHAR(dlugosc) [BINARY | ASCII | UNICODE]   | VARCHAR(dlugosc) [BINARY]   | TINYBLOB   | BLOB   | MEDIUMBLOB   | LONGBLOB   | TINYTEXT   | TEXT   | MEDIUMTEXT   | LONGTEXT   | ENUM(wart1,wart2,wart3,...)   | SET(wart1,wart2,wart3,...)

definicja_relacji:   REFERENCES nazwa_tabeli [(nazwa_kol,...)]     [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]     [ON DELETE reakcja]     [ON UPDATE reakcja]

reakcja:   RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

opcje:    {ENGINE|TYPE} = {BDB|HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|MYISAM}   AUTO_INCREMENT = wartosc  | AVG_ROW_LENGTH = wartosc  | CHECKSUM = {0 | 1}  | COMMENT = 'string'  | MAX_ROWS = wartosc  | MIN_ROWS = wartosc  | PACK_KEYS = {0 | 1 | DEFAULT}  | PASSWORD = 'string'  | DELAY_KEY_WRITE = {0 | 1}  | ROW_FORMAT = { DEFAULT | DYNAMIC | FIXED | COMPRESSED }  | RAID_TYPE = { 1 | STRIPED | RAID0 }   RAID_CHUNKS = wartosc   RAID_CHUNKSIZE = wartosc  | UNION = (nazwa_tabeli[,nazwa_tabeli]...)  | INSERT_METHOD = { NO | FIRST | LAST }  | DATA DIRECTORY = 'sciezka_bezwzgledna_do_katalogu'  | INDEX DIRECTORY = 'sciezka_bezwzgledna_do_katalogu'  | [DEFAULT] CHARACTER SET nazwa_kodowania [COLLATE nazwa_porownywania]

wyrazenie_select:   [IGNORE | REPLACE] [AS] SELECT ... (jakies_wyrazenie_select)

Polecenie CREATE TABLE tworzy tablicę o zadanej nazwie. Aby można było utworzyć tablicę w danej bazie danych, trzeba posiadać odpowiednie przywileje.

Domyślnie tabela jest tworzona w bierzącej bazie danych. Jeśli tabela o podanej nazwie istnieje lub jeśli nie wybrano bazy danych lub też w przypadku jeśli podana baza danych nie istnieje, wówczas podczas tworzenia tabeli jest zgłaszany błąd.

Można zatem używać notacji krokpowej. Na przykład wyrażenie baza_danych.nazwa_tabeli jest poprawnym wyrażeniem i oznacza tabelę o nazwie nazwa_tabeli w bazie danych baza_danych. Nie potrzeba więc w takim wypadku wybierac wcześniej bazy_danych osobnym poleceniem. Jeśli chcemy w wypadku notacji kropkowej używać cytowania za pomocą apostrofów, musimy pamiętać, o osobnym cytowaniu nazwy bazy danych i nazwy tabeli. Zatem, poprawnie jest `baza`.`tabela` podczas gdy niepoprawnie jest `baza.tabela`.

Trochę o definicji kolumn:

• Jeśli podczas tworzenia tabeli nie zadano przy definicji kolumny NULL lub NOT NULL wówczas kolumna jest domyślnie traktowana tak, jakby podano NULL. Zatem, jeśli nie podamy wprost, że nie chcemy aby w kolumnie mogły się znaleźć wartości NULL, wtedy takie wartości będą mogły się tam pojawiać.

• Kolumny typu całkowitego mogą mieć dodatkowy atrybut AUTO_INCREMENT. Jeśli do takiej kolumny wstawimy wartość NULL lub 0, wtedy wartością wstawioną będzie kolejna w sekwencji wartość, najczęściej ostatnia_najwieksza_wartosc+1. Sekwencje AUTO_INCREMENT rozpoczynają się od wartości 1, ale istnieją możliwości zdefiniowania wstawiania 0 do takich kolumn. Jeśli wstawimy wartośc mniejszą od zera do kolumny z atrybutem AUTO_INCREMENT wtedy zostanie to potraktowane tak, jakbyśmy chcieli wstawić bardzo dużą wartość większą od zera

W danej tabeli może istnieć tylko jedna kolumna z atrybutem AUTO_INCREMENT.

Dla kompatybilności z aplikacjami ODBC, MySQL wprowadził możliwość znalezienia ostatnio wprowadzonej wartości AUTO_INCREMENT za pomocą następującego polecenia:

SELECT * FROM tabela WHERE auto_kol IS NULL

• Od wersji 4.1 MySQL-a można definiować dla każdej kolumny sposób kodowania znaków, jak również sposób ich porównywania, np.

mysql> CREATE TABLE tab (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);

• gdzie utf8 jest nazwa sposobu kodowania, natomiast utf8_bin jest nazwą sposobu porównywania zakodowanych w utf8 znaków.

• Aby dowiedzieć się o sposobach kodowania znaków w MyQL należy wydać następującą komendę SQL

mysql> SHOW CHARACTER SET;

• Powinniśmy zobaczyć coś podobnego:

• +----------+-----------------------------+---------------------+--------+ 
  | Charset  | Description                 | Default collation   | Maxlen | 
  +----------+-----------------------------+---------------------+--------+ 
  | dec8     | DEC West European           | dec8_swedish_ci     |      1 | 
  | cp850    | DOS West European           | cp850_general_ci    |      1 | 
  | hp8      | HP West European            | hp8_english_ci      |      1 | 
  | koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 | 
  | latin1   | ISO 8859-1 West European    | latin1_swedish_ci   |      1 | 
  | latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 | 
  | swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 | 
  | ascii    | US ASCII                    | ascii_general_ci    |      1 | 
  | hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 | 
  | koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 | 
  | greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 | 
  | cp1250   | Windows Central European    | cp1250_general_ci   |      1 | 
  ...

• Aby dowiedzieć się w jaki sposób można zdefiniować sposób porównywania znaków w MySQL należy użyć polecenia SHOW COLLATION. Na przykład aby dowiedzieć się o możliwych sposobach porównywania znaków zakodowanych w systemie latin2 należy posłużyć się komendą:

mysql> SHOW COLLATION LIKE 'latin2%';

• Powinniśmy zobaczyć coś podobnego:

• +---------------------+---------+----+---------+----------+---------+ 
  | Collation           | Charset | Id | Default | Compiled | Sortlen | 
  +---------------------+---------+----+---------+----------+---------+ 
  | latin2_general_ci   | latin2  |  9 | Yes     |          |       0 | 
  | latin2_hungarian_ci | latin2  | 21 |         |          |       0 | 
  | latin2_croatian_ci  | latin2  | 27 |         |          |       0 | 
  | latin2_bin          | latin2  | 77 |         |          |       0 | 
  +---------------------+---------+----+---------+----------+---------+ 

• Istnieje pewna konwencja przy nazywaniu sposobów porównywania. Mianowicie, zaczynają się one zawsze nazwą systemu kodowania, następnie występuje zwykle nazwa języka, a kończą się albo _ci (ang. case insensitive, tzn. nieczułe na wielkość liter), albo _cs (ang. case sensitive, tzn. czułe na wielkość liter), albo _bin (ang. binary, tzn. tak jakby znak był znakiem ze stringu binarnego).

• Kiedy dodamy DEFAULT do definicji pola w tablicy, wtedy, poza jednym przypadkiem, domyślną wartością przyjmowaną przez to pole (a więc wówczas kiedy przy wprowadzaniu rekordu do tablicy nie zdefiniujemy wartości w tym polu) będzie wartość zdefiniowana przez nas po słowie DEFAULT. Tym wyjątkiem jest niemożliwość wprowadzenia w miejscu wartości domyślnej, wartości, która nie jest stałą; wartością tą nie może być funkcja lub wyrażenie, np. wartością domyślną kolumny typu date nie może być wartość zwracana przez funkcje NOW()lub CURRENT_DATE. Z kolei wyjątkiem od tego wyjątku jest to, że można dla kolumny typu TIMESTAMP użyć CURRENT_TIMESTAMP

Jeśli nie zadamy explicite wartości domyślnej a jedynie słowo DEFAULT wtedy wartość domyślna zostanie wyznaczona według następujących reguł:

• jeśli wartością w kolumnie może być NULL, wtedy domyślną wartością jest NULL;

• dla typów numerycznych innych niż te zadeklarowane jako AUTO_INCREMENT domyślną wartością jest 0. Dla kolumn typu AUTO_INCREMENT domyślną wartością jest nastepna w sekwencji.

• dla kolumn typu data i czas innych niż TIMESTAMP domyślną wartością jest odpowiednie zero.

• dla kolumn z danymi znakowymi, innymi niż ENUM, wartością domyślną jest pusty string. Dla danych ENUM wartością domyślną jest pierwsza wartość z listy.

• Relacje pomiędzy tablicami

Fragment polecenia, który służy do zadeklarowania kolumny jako klucza obcego w innej tabeli jest umieszczany po deklaracji wszystkich kolumn i posiada następującą składnię:

mysql> [CONSTRAINT [nazwa]] FOREIGN KEY (lista_kolumn)        REFERENCES nazwa_tabeli_obcej(lista_kolumn);

Należy zauważyć, że w tym poleceniu wystąpienie [CONSTRAINT [nazwa]] jest opcjonalne.

W przypadku tabel typu InnoDB musi być spełniony dodatkowy warunek, mianowicie kolumna, dla której tworzymy klucz obcy musi posiadać indeks. A zatem najpierw dodajemy indeks dla tej kolumny, a dopiero później definiujemy klucz główny.

Oto proste przykłady definiowania relacji pomiędzy tablicami w MySQL.
Na początku proste użycie tablicy "rodzic" i tablicy "dziecko" i powiązanie danych z tablicy "dziecko" z danymi z tablicy "rodzic" poprzez klucz obcy w jednej kolumnie tablicy "dziecko"

	Definicja tablicy "rodzic":
	mysql> CREATE TABLE rodzic(          id INT NOT NULL,          PRIMARY KEY (id)        )TYPE=InnoDB;
	Definicja tablicy "dziecko":
	mysql> CREATE TABLE dziecko(          id INT,          rodzic_id INT,          INDEX rodzic_ind(rodzic_id),          FOREIGN KEY (rodzic_id) REFERENCES rodzic(id)          ON DELETE CASCADE        )TYPE=InnoDB;

W powyższym, najprostszym przykładzie definicji relacji pomiędzy danymi pochodzącymi z różnych tablic, dane z tablicy "dziecko" są zależne od danych z tablicy "rodzic" poprzez parę kluczy głównego i obcego, tj. odpowiednio rodzic.id oraz dziecko.rodzic_id.

Nastepny przykład jest bardziej złożonym przypadkiem definicji relacji, w której jedna z tablic posiada dwa klucze obce, każdy związany z innym kluczem głównym z innej tablicy.

	Definicja tablicy powiaty:
	mysql> CREATE TABLE powiaty(          id INT NOT NULL,          nazwa CHAR(50) NOT NULL,          PRIMARY KEY (id)        )TYPE=InnoDB;
	Definicja tablicy wojewodztwa:
	mysql> CREATE TABLE wojewodztwa(          id INT,          nazwa CHAR(50) NOT NULL,          PRIMARY KEY (id)        )TYPE=InnoDB;
	Definicja tablicy osoba:
	mysql> CREATE TABLE osoba(          id INT,          imie CHAR(50) NOT NULL,          nazwisko CHAR(50) NOT NULL,          adres_wojew INT NOT NULL,          adres_powiat INT NOT NULL,          PRIMARY KEY (id),          INDEX adres_wojew,          FOREIGN KEY (adres_wojew) REFERENCES wojewodztwa(id)          ON UPDATE CASCADE ON DELETE RESTRICT,          INDEX adres_powiat,          FOREIGN KEY (adres_powiat) REFERENCES powiaty(id)          ON UPDATE CASCADE ON DELETE RESTRICT        )TYPE=InnoDB;

Uwaga! Zawsze przed zdefiniowaniem klucza obcego w tabeli należy najpierw utworzyć odpowiedni indeks.
A zatem, zanim użyjemy FOREIGN KEY ... najpierw trzeba użyć INDEX ....

Przykład

Oto zestaw poleceń tworzących tablice relacyjne wraz z połączeniami między nimi dla omówionego wcześniej przypadku zamówień w sklepie.

Są to cztery tablice: klient, zamowienia, towary oraz dostawy. Tablice klient oraz towary nie zawierają definicji kluczy obcych, podczas gdy tablice zamowienia oraz dostawy zawierają klucze obce. Wszystkie tablice, oprócz dostawy zawierają klucze główne.

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

CREATE TABLE klient ( 
 
  id_klienta       int auto_increment NOT NULL, 
  nazwa            varchar(30)  NOT NULL, 
  miasto           varchar(50)  NOT NULL, 
  ulica            varchar(50)  NOT NULL, 
  kod_pocztowy     varchar(10)  NOT NULL, 
  telefon          varchar(50)  NOT NULL, 
 
  PRIMARY KEY  (id_klienta), 
  INDEX (id_klienta) 
 
) TYPE=InnoDB; 

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

CREATE TABLE zamowienia ( 
 
  id_zamowienia     int  auto_increment NOT NULL, 
  id_klienta        int  NOT NULL, 
  data_zamowienia   date NOT NULL, 
  data_realizacji   date NOT NULL, 
 
  PRIMARY KEY  (id_zamowienia), 
  INDEX (id_zamowienia), 
  INDEX (id_klienta), 
  CONSTRAINT id_klienta_obcy FOREIGN KEY(id_klienta) 
    REFERENCES klient(id_klienta) 
 
) TYPE=InnoDB; 

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

CREATE TABLE towar ( 
 
  id_towaru         int  auto_increment NOT NULL, 
  opis_towaru       varchar(50)   NOT NULL, 
  cena_zakupu       numeric(10,2) NOT NULL, 
  cena_sprzedazy    numeric(10,2) NOT NULL, 
 
  PRIMARY KEY  (id_towaru), 
  INDEX (id_towaru) 
 
) TYPE=InnoDB; 

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

CREATE TABLE dostawy ( 
 
  id_zamowienia     int  NOT NULL, 
  id_towaru         int  NOT NULL, 
 
  INDEX (id_zamowienia), 
  INDEX (id_towaru), 
  CONSTRAINT id_zamowienia_obcy FOREIGN KEY(id_zamowienia) 
    REFERENCES zamowienia(id_zamowienia), 
  CONSTRAINT id_towaru_obcy FOREIGN KEY(id_towaru) 
    REFERENCES towar(id_towaru) 
 
) TYPE=InnoDB; 

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

Warto też zauważyć, że po ostatnim poleceniu podczas definicji struktury tablicy nie występuje przecinek, co wydaje się zupełnie oczywiste, jeśli przyjmie się, że te "wewnętrzne" polecenia w definicji tablicy są jakby elementami listy poleceń rozdzielonymi przecinkami.

PRIMARY KEY jest kluczem unikalnym, zdefiniowanym dla kolumny, w której nie może się znaleźć wartość równa NULL (jeśli nie zdefiniujemy tego podczas tworzenia tablicy, wtedy MySQL zrobi to za nas "po cichu"). Każda tablica może mieć tylko jeden klucz unikalny.

• Aby dowiedzieć się czegoś więcej na temat polecenia, za pomocą którego utworzono tablicę (żeby na przykład dowiedzieć się jak zostały zdefiniowane klucze obce) można posłużyć się odpowiednim poleceniem SHOW.

Na przykład:

mysql> SHOW TABLE STATUS FROM nazwa_bazy LIKE 'nazwa_tablicy';

albo też

mysql> SHOW CREATE TABLE nazwa_tablicy;

• Struktura tablicy z innej tablicy

Podczas tworzenia struktury tablicy można też posłużyć się już istniejącą tablicą i wykorzystać część jej struktury za pomocą komendy SELECT dodanej na końcu polecenia CREATE TABLE

mysql> CREATE TABLE nowa_tablica SELECT * FROM inna_tablicy;

Kolumny z wyrażenia SELECT są dodawane na koniec istniejącej listy kolumn. Na przykład, wyobraźmy sobie, że utworzyliśmy już tablicę o nazwie stara, która wygląda z zawartością mniej więcej następująco:

mysql> SELECT * from stara;

+---+ | n | +---+ | 1 | +---+

Następnie utworzymy tablicę, do której zostanie dołączona struktura tablicy stara.

mysql> CREATE TABLE nowa (x INT) SELECT n FROM stara;

Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0

Zobaczmy jak po takiej operacji wygląda nowo utworzona tablica nowa

mysql> SELECT * FROM nowa;

+------+---+ | x    | n | +------+---+ | NULL | 1 | +------+---+

Warto zauważyć, że wraz z definicją kolumny została dołączona do nowej tablicy również zawartośc tej tablicy. A ponieważ w tablicy nowa nie było wcześniej żadnych danych, więc w każdym wierszu nowej tablicy w polach, w których nie ma danych znajdą się albo wartości domyślne albo wartości NULL.

Uwaga. Polecenie CREATE TABLE ... SELECT nie utworzy automatycznie indeksów na kopiowanych z innej tabeli kolumnach. Jeśli chcemy mieć indeksy na nowo utworzonych kolumnach tabeli, wtedy musimy to zadać explicite przed poleceniem SELECT

mysql> CREATE TABLE nowa (UNIQUE (n)) SELECT n FROM stara;

Od wersji 4.1 MySQL-a możemy też użyć słowa LIKE w celu utworzenia pustej tablicy na podstawie definicji innej tablicy. Podczas takiego sposobu tworzenia nowej tablicy wszystkie informacje zawarte w oryginalnej tablicy sa kopiowane (również indeksy).

mysql> CREATE TABLE nowa LIKE stara;

Należy pamiętać, że polecenie CREATE TABLE ... LIKE nie kopiuje żadnych danych ze starej tablicy do nowej.

• DROP TABLE

Składnia polecenia DROP TABLE jest następująca

DROP   [TEMPORARY] TABLE [IF EXISTS] nazwa_tab1 [, nazwa_tab2] ...   [RESTRICT | CASCADE]

Polecenie DROP TABLE służy do usuwania jednej lub większej ilości tablic.
Uwaga! Usuwane sa wszystkie dane zawarte w usuwanej tablicy.

• wyrażenie logiczne IF EXISTS zapobiega przed pojawieniem się informacji o błędzie podczas próby usunięcia niestniejącej tablicy.

• opcje RESTRICT i CASCADE na razie nic nie robią. Można ich jednak używać aby łatwiej przenieść dane z innych nie-MySQL-owych serwerów bazodanowych.

• użycie słowa TEMPORARY powoduje:

  • usunięcie tylko tablic tymczasowych (TEMPORARY),

  • że wyrażenie DROP TEMPORARY TABLE nie kończy rozpoczętej transakcji (normalnie polecenie DROP TEMPORARY TABLE kończy automatycznie transakcję (jeśli była rozpoczęta)),

  • że nie są sprawdzane przywileje do usunięcia tablicy (tablica tymczasowa jest widoczna tylko dla klienta, i nie ma potrzeby sprawdzać, czy ma on prawo ją usunąć).

• ALTER TABLE

Składnia polecenia ALTER TABLE jest następująca

ALTER   [IGNORE] TABLE nazwa_tab   opis_zmiany [, opis_zmiany] ...

A dokładniej

opis_zmiany:   ADD [COLUMN] definicja_kolumny [FIRST | AFTER kolumna]  | ADD [COLUMN] (definicja_kolumny,...)  | ADD INDEX [nazwa_indeksu] [typ_indeksu] (kolumna,...)  | ADD [CONSTRAINT [symbol]]     PRIMARY KEY [typ_indeksu] (kolumna,...)  | ADD [CONSTRAINT [symbol]]     UNIQUE [nazwa_indeksu] [typ_indeksu] (kolumna,...)  | ADD [FULLTEXT|SPATIAL] [nazwa_indeksu] (kolumna,...)  | ADD [CONSTRAINT [symbol]]     FOREIGN KEY [nazwa_indeksu] (kolumna,...)     [definicja_relacji]  | ALTER [COLUMN] kolumna {SET DEFAULT literał | DROP DEFAULT}  | CHANGE [COLUMN] stara_nazwa definicja_kolumny     [FIRST|AFTER nazwa_kolumny]  | MODIFY [COLUMN] definicja [FIRST | AFTER kolumna]  | DROP [COLUMN] kolumna  | DROP PRIMARY KEY  | DROP INDEX nazwa_indeksu  | DROP FOREIGN KEY nazwa_symbolu_klucza_obcego  | DISABLE KEYS  | ENABLE KEYS  | RENAME [TO] nowa_nazwa_tabeli  | ORDER BY kolumna  | CONVERT TO CHARACTER SET kodowanie [COLLATE nazwa_porówn]  | [DEFAULT] CHARACTER SET kodowanie [COLLATE nazwa_porówn]  | DISCARD TABLESPACE  | IMPORT TABLESPACE  | opcje_tabeli

Polecenie ALTER TABLE służy do zmian struktury istniejących tablic. Można na przykład dodać lub usunąć kolumny, utworzyć lub usunąć istniejące indeksy, zmienić typ kolumn, zmienić nazwy kolumn, zmienić nazwy tabel lub zmienić typ tabeli.

Składnia odpowiednich poleceń służących do zmiany definicji tablicy jest w wielu przypadkach podobna do polecenia CREATE TABLE

Polecenie ALTER TABLE pracuje na kopii zmienianej tablicy; najpierw tworzy tymczasową kopię oryginalnej tablicy, później dokonuje zmian na kopii, później usuwa tablice oryginalną, a następnie "zmienia nazwę" nowej tablicy. Kiedy ALTER TABLE dokonuje zmian na tablicy tymczasowej, wtedy tablica oryginalna jest cały czas dostępna do czytania, a inne polecenia (zmiana zawartości tablicy) są automatycznie przekierowywane do nowej, zmienionej tablicy.

• Jeśli w poleceniu ALTER TABLE użyjemy parametru IGNORE wówczas możemy się spodziewać, że przy próbie wprowadzenia dwa razy takiej samej wartości do pola z kluczami unikalnymi, wiersze z takimi błędami są usuwane poza pierwszym, w którym wystąpi wartość unikalna (pierwsze wystąpienie). Jeśli ominiemy parametr IGNORE wówczas kopiowanie tablicy jest cofnięte i cała operacja zmiany tablicy nie zachodzi.

• Możemy w jednym poleceniu ALTER TABLE użyć wielokrotnie klauzul ADD, ALTER, DROP i CHANGE.

Na przykład aby usunąć kilka kolumn z tabeli przy pomocy jednego polecenia ALTER TABLE możemy wydać następujące polecenie:

mysql> ALTER TABLE tab DROP COLUMN c, DROP COLUMN d;

• Jeśli używamy ALTER TABLE stara RENAME TO nowa bez żadnych innych opcji, wtedy MySQL zwyczajnie zmienia nazwy plików odpowiadających odpowiednim tabelom. Zamiast powyższego wyrażenia można użyć polecenia RENAME TABLE, opisanego niżej.

• Podczas zmiany nazwy kolumny możemy też określić nowy typ danych

mysql> ALTER TABLE tab CHANGE a b INTEGER;

• Jeśli chcemy zmienić typ danych w kolumnie, jednak nie chcemy zmieniać nazwy kolumny, możemy wydać polecenie

mysql> ALTER TABLE tab CHANGE b b BIGINT NOT NULL;

• Jak widać, w miejsce nowej nazwy kolumny wprowadzona została poprzednia nazwa.

• Podobny efekt można uzyskać używając opcji MODIFY

mysql> ALTER TABLE tab MODIFY b BIGINT NOT NULL;

• Opcje FIRST i AFTER służą do dokładniejszej specyfikacji umiejscowienia nowej kolumny w tabeli. Domyślnie, nowa kolumna jest zawsze dodawana na koniec.

• Jeśli tabela zawiera tylko jedną kolumnę, wtedy próba usunięcia jej poleceniem ALTER TABLE nie powiedzie się. Do usunięcia tablicy służy polecenie DROP TABLE.

• W przypadku tablic InnoDB możemy definiować klucze obce

mysql> ALTER TABLE tab ADD [CONSTRAINT [symbol]]        FOREIGN KEY (...) REFERENCES ... (...)

• Składnia jest takla sama jak przy definiowaniu struktury tablicy poleceniem CREATE TABLE . Możemy też usuwać klucze obce z tabeli

mysql> ALTER TABLE tab DROP FOREIGN KEY obcy_klucz_symbol;

• Możemy też zmieniać system kodowania znaków

mysql> ALTER TABLE tab CONVERT TO CHARACTER SET kodowanie;

• Aby zmienić domyślny sposób kodowania dla całej tabeli, można napisać:

mysql> ALTER TABLE tab DEFAULT CHARACTER SET kodowanie;

• Za pomocą ALTER TABLE można też zmienić dane z jednego systemu kodowania na inny. Na przykład w tablicy w kolumnie c2 mieliśmy dane zakodowane w latin1 i chcemy przekonwertować je do danych zakodowanych w utf8 W tym celu należy wydać polecenia

mysql> ALTER TABLE tab CHANGE c2 c2 BLOB; mysql> ALTER TABLE tab CHANGE c2 c2 TEXT CHARACTER SET utf8;

• Inne przykłady:

Załóżmy, że utworzyliśmy tablicę w następujący sposób

mysql> CREATE TABLE tab1 (a INTEGER, b CHAR(10));

Zmiana nazwy tablicy

mysql> ALTER TABLE tab1 RENAME tab2;

Zmiana typu danych w kolumnach z INTEGER na TINYINT NOT NULL, zostawinie jednak niezmienionej nazwy kolumny, oraz jednocześnie zmiana typu danych w kolumnie CHAR(10) na CHAR(20) i zmiana nazwy kolumny

mysql> ALTER TABLE tab2 MODIFY a TINYINT NOT NULL,        CHANGE b c CHAR(20);

Dodanie nowej kolumny typu TIMESTAMP do już istniejących

mysql> ALTER TABLE tab2 ADD d TIMESTAMP;

Dodanie indeksów na kolumnach d oraz a

mysql> ALTER TABLE tab2 ADD INDEX (d), ADD INDEX (a);

Usunięcie kolumny o nazwie c

mysql> ALTER TABLE tab2 DROP COLUMN c;

Dodanie nowej kolumny o nazwie c typu AUTO_INCREMENT

mysql> ALTER TABLE tab2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,        ADD PRIMARY KEY (c);

W powyższym przykładzie zadeklarowano kolumnę jako klucz główny, gdyż kolumny z opcją AUTO_INCREMENT muszą być indeksowane oraz dlatego, że zadeklarowano c jako NOT NULL, gdyż z kolei klucze główne nie mogą być NULL.

• RENAME TABLE

Składnia polecenia RENAME TABLE jest następująca

RENAME TABLE   bierzaca_nazwa_tab1 TO nowa_nazwa_tab1   [, bierzaca_nazwa_tab2 TO nowa_nazwa_tab2] ...

Polecenie RENAME TABLE służy do zmiany nazwy jednej lub wiekszej ilości tablic.

Jeśli mamy w bazie danych tabelę stara i utworzymy tabelę nowa, która ma taką samą strukture jak struktura tablicy stara ale jest pusta, to możemy zamienić je w nastepujący sposób:

mysql> CREATE TABLE nowa (...); mysql> RENAME TABLE        stara TO tymcz,        nowa TO stara,        tymcz TO nowa;

Inaczej mówiąc, zmiana nazw tabel jest dokonywana automatycznie, od lewej do prawej.

Za pomocą polecenia RENAME TABLE można też przenieśc tabelę z jednej bazy do drugiej (pod warunkiem, że obie bazy danych rezydują na tym samym systemie plików)

mysql> RENAME TABLE baza1.nazwa1 TO baza2.nazwa2;

Kiedy chcemy użyć RENAME TABLE nie możemy mieć w tym czasie zablokowanego dostępu do tabeli i nie może być żadnych aktywnych transakcji.

• DESCRIBE

Składnia polecenia DESCRIBE jest następująca

{DESCRIBE | DESC} nazwa_tab [nazwa_kol | wyr_reg];

Za pomocą tego polecenia możemy dowiedzieć się o strukturze tablicy.

Skrótem od DESCRIBE jest słowo DESC. Zatem zamiast pisać DESCRIBE nazwa_tab można napisać DESC nazwa_tab.

Polecenie DESCRIBE jest skrótem od SHOW COLUMNS FROM, tak więc można używać zamiennie tych poleceń w celu uzyskania tego samego wyniku.

Podczas wykonywania polecenia DESCRIBE można uzyskać informacje o wybranych kolumnach tablicy a nie wszystkich kolumnach tablicy. W tym celu należy obok nazwy tablicy podać też nazwę kolumny lub też wyrażenie regularne wyr_reg określające, o które kolumny nam chodzi. Parser dokona wtedy dopasowania odpowiednich nazw kolumn do wyrażenia regularnego i przekaże listę kolumn do polecenia DESCRIBE.

Przykład:

mysql> DESCRIBE users; +--------+----------+------+-----+----------------+ | Field  | Type     | Null | Key | Extra          | +--------+----------+------+-----+----------------+ | id     | int(11)  |      | PRI | auto_increment | | sid    | char(64) |      |     |                | | login  | char(32) |      |     |                | | ip     | char(32) |      |     |                | | opened | int(1)   |      |     |                | | closed | int(1)   |      |     |                | +--------+----------+------+-----+----------------+ 6 rows in set (0.00 sec)

Jak widać, wynikiem jest tabela z kolumnami o nazwach Type, Null, Key, Extra, czasem jest też dodatkowo pole Default.

W polu Type jest typ danych kolumny. W polu Null jest informacja o tym, czy w kolumnie mogą się znaleźć wartości NULL. W polu Extra znajdują się informacje o dodatkowych parametrach stanowiących o właściwości danych w tym polu, na przykład parametr auto_increment. W polu Default znajdują się wartości domyślne, jakie przyjmują dane w tych polach, jeśli nie podamy ich explicite podczas wstawiania rekordów do bazy. W polu Key znajdują się informacje o tym, czy kolumna jest kluczem głównym (PRI) czy też kluczem obcym (MUL) w tabeli.

Polecenia SHOW CREATE TABLE oraz SHOW TABLE STATUS również dostarczają informacji na temat tabel w bazie.

• Inne polecenia

Istnieją też inne polecenia dokonujące sprawdzenia spójności tablic, danych w tych tablicach, i które potrafią naprawić niektóre błędy, które mogą wystąpić w tablicach, na przykład w wyniku nagłego braku zasilania. Do poleceń tych należą:

• ANALYZE TABLE sprawdza tylko klucze (główne i obce) w tabeli.

• CHECK TABLE sprawdza tabelę pod względem jakichkolwiek błędów jak również spójnośc samych danych w tabeli.

• OPTIMIZE TABLE. Polecenie to jest używane po wykonaniu większej ilości zmian w tabeli (usunięcie sporej ilości rekordów, update większej ilości danych). Usuwane lub zmieniane rekordy są zapisywane na specjalnej liście i kolejna operacja INSERT powoduje przesunięcie odpowiednich indeksów w góre. Jest to więc polecenie podobne do defragmentacji dysku w systemie Windows.

• REPAIR TABLE. Reperuje prawdopodobnie zepsutą tabelę. Może się to zdarzyć na przykład po gwałtownym wyłączeniu zasilania. Jednak normalnie nie powinno się to zdarzać.

• BACKUP TABLE. Polecenie to powoduje skopiowanie minimalnej ilości plików z tablicami potrzebnych do odtworzenia tabeli z backupu. Są jednak lepsze polecenia. Na przykład wykonywane z linii komend polecenie mysqldump.

• RESTORE TABLE. Polecenie odtwarza tablę lub tabele z backupu wykonanego poleceniem BACKUP TABLE