Vytváření a úpravy tabulek

Cíl lekce
Po skončení této lekce byste měli umět:
Popsat hlavní databázové objekty
Vytvářet tabulky
Popsat datové typy, které mohou být použity při specifikování sloupců
Měnit definici tabulek
Rušit, přejmenovávat a vyprazdňovat tabulky
STRANA

Databázové objekty
STRANA

Pravidla pro názvy
Názvy tabulek a sloupců musí
Začínat písmenem
Smějí být dlouhé 1 – 30 znaků
Smějí obsahovat pouze A-Z, a-z, 0-9, _, $ a #
Nesmějí opakovat názvy jiných objektů vlastněných týmž uživatelem
Nesmějí to být vyhrazená slova serveru
STRANA

Příkaz CREATE TABLE
Musíte mít
Privilegium CREATE TABLE
Prostor pro uložení
Specifikujete:
Název tabulky
Název sloupce, jeho datový typ a velikost
CREATE TABLE [schema.]tabulka
(sloupec dat_typ [DEFAULT výraz][, ...]);
STRANA

Odkazy na tabulky jiného uživatele
Tabulky vlastněné jiným uživatelem nejsou součástí uživatelova schématu
Můžete použít uživatelovo jméno jako prefix k názvu tabulky
STRANA

Volba DEFAULT
Specifikujte defaultní hodnotu pro sloupec během vkládání
Platnými hodnotami jsou literál, výraz nebo SQL funkce
Název jiného sloupce nebo pseudosloupce jsou nepřípustné
Datový typ defaultní hodnoty musí souhlasit s datovým typem sloupce
STRANA

Vytváření tabulky
Vytvořte tabulku
Ověřte vytvoření tabulky
DESCRIBE dept
STRANA

Tabulky v databázi
Uživatelské tabulky:
Je to kolekce tabulek vytvořených a vlastněných uživatelem
Obsahují uživatelovy informace
Datový slovník:
Je to kolekce tabulek vytvořených a vlastněných databázovým serverem
Obsahují data o struktuře a stavu databáze
STRANA

Dotazy do datového slovníku
SELECT DISTINCT object_type
FROM user_objects ;
Vyber názvy tabulek uživatelem vlastněných
Vyber různé typy objektů uživatelem vlastněných
Vyber tabulky, pohledy, synonyma a sekvence vlastněné uživatelem

Datové typy

Datové typy pro datum a čas
Oracle 9i přinesl nové datové typy pro čas a datum
Jsou k dispozici nové způsoby ukládání dat
Byla rozšířena vazba na časové zóny a jejich lokalizaci
STRANA

Datové typy pro datum a čas
Základním typem je typ DATE
Datový typ TIMESTAMP je rozšířením datového typu DATE
Ukládá roky, měsíce a dny obdobně jako typ DATE, plus hodiny, minuty a sekundy a stejně zlomky sekund
Datový typ TIMESTAMP je definován:
TIMESTAMP[(přesnost_vteřinového_zlomku)]
STRANA

Vytváření tabulky s užitím poddotazu
Vytvořte tabulku a vložte do ní řádky kombinací příkazu CREATE TABLE a volby AS poddotaz
Počet specifikovaných sloupců musí souhlasit s počtem sloupců v poddotazu
Definujte sloupce s názvy sloupců a defaultními hodnotami
STRANA

Vytváření tabulky s užitím poddotazu
DESCRIBE oddel80
CREATE TABLE oddel80
AS SELECT zamestnanec_id, prijmeni,
plat*12 ROK_PLAT,
den_nastupu
FROM zamestnanci
WHERE oddeleni_id = 80;
Table created.

Příkaz ALTER TABLE
Příkaz ALTER TABLE umožňuje
Přidat nový sloupec
Modifikovat stávající sloupec
Definovat defaultní hodnotu pro sloupec
Zrušit sloupec
STRANA

Příkaz ALTER TABLE
Užijte příkaz ALTER TABLE pro přidání, změnu a zrušení sloupce
ALTER TABLE tabulka
ADD (sloupec datový_typ [DEFAULT výraz]
[, sloupec datový_typ]...);
ALTER TABLE tabulka
MODIFY (sloupec datový_typ [DEFAULT výraz]
[, sloupec datový_typ]...);
ALTER TABLE tabulka
DROP (sloupec);
STRANA

Přidání sloupce
ODDEL80
ODDEL80
Přidej nový sloupec do tabulky ODDEL80

Přidání sloupce
Přidejte nový sloupec PRÁCE_ID
Nový sloupec se objeví jako poslední
ALTER TABLE oddel80
ADD (prace_id VARCHAR2(9));
Table altered.
STRANA

Změna sloupce
U sloupce můžete změnit datový typ, velikost a defaultní hodnotu
Změna defaultní hodnoty má vliv pouze při pozdějším vkládání dat
STRANA

Zrušení sloupce
Použijte klauzuli DROP COLUMN pro odstranění nepotřebného sloupce
STRANA

Odstranění tabulky
Všechna data a struktura tabulky jsou zrušena
Všechny probíhající transakce jsou ukončeny s potvrzením
Všechny indexy jsou zrušeny
Tento příkaz nemůže být zrušen (vrácen)
STRANA

Změna názvu objektu
Ke změně názvu tabulky, pohledu, sekvence nebo synonyma použijte příkaz RENAME
Musíte být vlastníkem objektu
STRANA

Vyprázdnění tabulky
Příkaz TRUNCATE TABLE
Odstraní všechny řádky z tabulky
Uvolní skladový prostor obsazený tabulkou
Jestliže použijete TRUNCATE, nelze obsah tabulky vrátit
Alternativně můžete vyprázdnit tabulku příkazem DELETE
STRANA

Co je omezení?
Omezení (constraints) vynucují pravidla na úrovni tabulek
Omezení zabraňují vymazání dat, na které jsou vazby z jiných tabulek
Následující typy omezení jsou platná:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
STRANA

Pravidla pro omezení
Constraint může být pojmenován nebo je jeho jméno vytvářeno serverem
Vytvořit omezení lze:
Při vytváření tabulky
Dodatečně, po vytvoření tabulky
Omezení lze definovat na úrovni sloupce nebo tabulky
Omezení lze prohlížet v datovém slovníku
STRANA

Vytváření omezení
CREATE TABLE zamestanci (
zamestnanec_id NUMBER(6),
jmeno VARCHAR2(20),
...
funkce_id VARCHAR2(10) NOT NULL,
CONSTRAINT ZAM_ID_PK
PRIMARY KEY ( ZAMESTNANEC_ID ) );

Vytváření omezení
Omezení na úrovni sloupců
Omezení na úrovni tabulky
sloupec [CONSTRAINT název_omezení] typ_omezení,
sloupec,...
[CONSTRAINT název_omezení] typ_omezení
(sloupec, ...),

Omezení NOT NULL
Zajišťuje, že do sloupce nemůže být vložena hodnota NULL
NOT NULL constraint (žádný řádek nesmí být nulový)
NOT NULL constraint
Není NOT NULL constraint
STRANA

Omezení NOT NULL
Definuje se na úrovni sloupce
CREATE TABLE zamestnanci(
zamestnanec_id NUMBER(6),
prijmeni VARCHAR2(25) NOT NULL,
plat NUMBER(8,2),
premie_pct NUMBER(2,2),
den_nastupu DATE
CONSTRAINT zam_nast_dat_nn
NOT NULL,
...
STRANA

Omezení UNIQUE
Zabezpečuje, že v určitém sloupci budou pouze jedinečné hodnoty (bez opakování)
LZE
NELZE
UNIQUE
STRANA

Omezení UNIQUE
Lze jej definovat na úrovni řádku i tabulky
CREATE TABLE zamestnanci(
zamestnanec_id NUMBER(6),
prijmeni VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
palt NUMBER(8,2),
premie_pct NUMBER(2,2),
den_nastupu DATE NOT NULL,
...
CONSTRAINT zam_email_uk UNIQUE(email));
STRANA

Omezení PRIMARY KEY
Omezení PRIMARY KEY je sloupec, který jednojednoznačně identifikuje určitý řádek v tabulce
PRIMARY
KEY
Nulová hodnota
Již existuje
NELZE
STRANA

Omezení PRIMARY KEY
Lze definovat jak na úrovni tabulky, tak řádku
STRANA

Omezení FOREIGN KEY
Cizí klíč
INSERT
Přípustné
Nelze – klíč neexistuje
STRANA

FOREIGN KEY constraint
Lze definovat na úrovni tabulky i sloupce
CREATE TABLE zamestnanci(
zamestnanec_id NUMBER(6),
prijmeni VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
plat NUMBER(8,2),
premie_pct NUMBER(2,2),
den_nastupu DATE NOT NULL,
...
oddeleni_id NUMBER(4),
CONSTRAINT zam_odd_fk FOREIGN KEY (oddeleni_id)
REFERENCES oddeleni(oddeleni_id),
CONSTRAINT zam_email_uk UNIQUE(email));
STRANA

FOREIGN KEY constraint
Klíčová slova
FOREIGN KEY: identifikuje sloupec v dceřiné tabulce, který odpovídá vzoru
REFERENCES: identifikuje otcovskou tabulku a sloupec
ON DELETE CASCADE: při zrušení řádku v otcovské tabulce zruší závislé řádky v dceřiné tabulce
ON DELETE SET NULL: při zrušení řádku v otcovské tabulce nastaví závislé cizí klíče na NULL
STRANA

Omezení CHECK
Definuje podmínky, které musí každý řádek splňovat
Následující výrazy jsou nepřípustné
Odkazy na pseudosloupce CURRVAL, NEXTVAL, LEVEL a ROWNUM
Užití funkcí SYSDATE, UID, USER a USERENV
Dotazy, které používají hodnoty v jiných řádcích
STRANA

Přidávání omezení
Použijte příkaz ALTER TABLE
Přidat nebo odstranit omezení beze změny struktury
Povolit nebo zakázat omezení
Přidat NOT NULL omezení pomocí MODIFY klauzule
ALTER TABLE tabulka
ADD [CONSTRAINT omezení] type (sloupec);
STRANA

Přidání omezení
Přidejte omezení FOREIGN KEY, který zajistí, že manažer zaměstnance již musí existovat jako platný zaměstnanec v tabulce ZAMESTNANCI
STRANA

Odstranění omezení
Odstraňte manažerské omezení z tabulky ZAMESTANCI
Odstraňte PRIMARY KEY na tabulce ODDELENI a odstraňte související omezení FOREIGN KEY na sloupci ZAMESTNANCI.ODDELENI_ID
ALTER TABLE zamestnanci
DROP CONSTRAINT zam_manager_fk;
Table altered.
ALTER TABLE departments
DROP PRIMARY KEY CASCADE;
Table altered.
STRANA

Deaktivace omezení
Použijte DISABLE klauzuli příkazu ALTER TABLE k deaktivaci integritního omezení
Aplikujte volbu CASCADE k deaktivaci souvisejících integritních omezení
ALTER TABLE zamestnanci
DISABLE CONSTRAINT zam_zam_id_pk CASCADE;
Table altered.
STRANA

Aktivace omezení
Aktivujte integritní omezení v tabulce deaktivované užitím klauzule ENABLE
UNIQUE nebo PRIMARY KEY indexy se automaticky vytvoří při aktivaci omezení UNIQUE nebo PRIMARY KEY
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;
Table altered.
STRANA

Prohlížení omezení
Dotaz do tabulky USER_CONSTRAINTS ukáže všechny definice a jména omezení
STRANA