Úprava tabulky - defaultní hodnoty a sekvence

Tato kapitola navazuje na povídání o ALTER TABLE. Probírá další možnosti tohoto příkazu, ale určitě tím nevyčerpává všechny jeho možnosti, ani se tu nedozvíte nic extra světoborného.

Už ani vlastně nevím, proč tahle kapitola vznikla a proč probírá zrovna tyto varianty příkazu ALTER TABLE. Asi jsem jí napsal v době, kdy jsem tyto varianty potřeboval. Znát ALTER TABLE se vám ale určitě vyplatí.

Přidání defaultní hodnoty
Přidání typu serial
MySQL
SQLite
Oracle

Přidání defaultní hodnoty

Defaultní hodnotu sloupce můžete určit už při vytváření tabulky, nebo jí ke sloupci přidat později. Pokud existuje, můžete jí taky smazat pomocí příkazu ALTER TABLE. Jak se to dělá zjistíte v nápovědě k tomuto příkazu.

O vytváření nového sloupce jsem již povídal v kapitole o úpravě tabulky, odstavci o ALTER TABLE. Dozvěděli jste se, jak přidat do tabulky sloupec s datovým typem, ale nic víc.

Ukážu příklad, kde do tabulky dluhy přidám sloupec navrat_dluhu typu DATE jehož defaultní hodnota bude deset dní od aktuálního času (current_date+10). V Postgresu se to dělá tak, že se nejdříve přidá sloupec a teprve poté k němu DEFAULT hodnota.

Udělá se to pomocí ALTER TABLE. Příklad předvedu na tabulce navrat_dluhu, kterou jsem vytvořil v kapitole o Úprava tabulky. Pro připomenutí vám znovu ukážu SQL pro vytvoření této tabulky:

rimmer1=> CREATE TABLE dluhy (
jmeno VARCHAR(10),
prijmeni VARCHAR(15),
dluh NUMERIC(8,1),
zadluzen DATE
);
rimmer1=> INSERT INTO dluhy VALUES ('Linus','Torvalds','3105.0', '2002-09-22');
rimmer1=> INSERT INTO dluhy VALUES ('Martin','Doktor',1590.0,'2002-09-22');
rimmer1=> ALTER TABLE dluhy ADD navrat_dluhu DATE;
rimmer1=> ALTER TABLE dluhy ALTER navrat_dluhu SET DEFAULT current_date+10;
rimmer1=> SELECT * FROM dluhy;
jmeno | prijmeni | dluhy | zadluzen | navrat_dluhu
--------+----------+--------+------------+--------------
Linus | Torvalds | 3105.0 | 2002-09-22 |
Martin | Doktor | 1590.0 | 2002-09-22 |
(2 rows)

Jak vidíte, přibyl nový sloupec, který je celý prázdný i přes to, že má defaultní hodnotu. Defaultní hodnota se dosazuje pouze při vkládání nového řádku. Sloupec navrat_dluhu je nutné vyplnit tak, jak jsem to dělal v odstavci o ALTER TABLE.

Sloupec lze přidat do tabulky rovnou s nastavenou defaultní hodnotou. V takovém případě se do sloupece hned defaultní hodnota vloží.

Přidání typu serial

Pokud mluvím o typu serial, dopouštím se tím jisté nepřesnosti. Serial je ve své podstatě typ INTEGER, který má jako defaultní hodnotu funkci nextval nějaké sekvence a integritní omezení NOT NULL ~~a UNIQUE~~.

rimmer1=> CREATE TABLE tabulka (cislo serial);
NOTICE: CREATE TABLE will create implicit sequence 'tabulka_cislo_seq' for SERIAL column 'tabulka.cislo'
CREATE
rimmer1=> \d tabulka
Table "tabulka"
Column | Type | Modifiers
-------+---------+-------------------------------------------------------
cislo | integer | not null default nextval('"tabulka_cislo_seq"'::text)

Z výpisu tabulky tabulka (jak originální název, že :-) je patrné, že sloupec cislo je typu integer (!) s výše jmenovanými vlastnostmi. Funkce nextval obsahuje jako argument sekvenci tabulka_cislo_seq. Tato sekvence se vytvořila automaticky při vytvoření tabulky.

Pokud chci „sestrojit“ typ SERIAL, vytvořím nejdřív sloupec s typem INTEGER a k němu přidám defaultní hodnotu s funkcí nextval. Sekvenci si budu muset vytvořit sám. K tomu slouží příkaz CREATE SEQUENCE. Pak ještě omezím nový sloupec pouze na unikátní hodnoty (dřív byl typ SERIAL automaticky unikátní, ale to už neplatí) pomocí příkazu CREATE INDEX. Nový sloupec bude prázdný, proto jej nakonec vyplním nějakými hodnotami.

CREATE TABLE lide (jmeno VARCHAR(10));
INSERT INTO lide VALUES('Petr');
INSERT INTO lide VALUES('Petr');
INSERT INTO lide VALUES('Pavel');
ALTER TABLE lide ADD id INTEGER;
ALTER TABLE lide ALTER id SET DEFAULT nextval('"lide_id_seq"'::text);
CREATE UNIQUE INDEX lide_id_key ON lide (id);

rimmer1=> INSERT INTO lide values ('Martin');
ERROR: pg_aclcheck: class "lide_id_seq" not found
rimmer1=> CREATE SEQUENCE lide_id_seq;
CREATE SEQUENCE
rimmer1=> INSERT INTO lide(jmeno) values ('Martin');
INSERT 0 1
rimmer1=> SELECT * FROM lide;
jmeno | id
--------+----
Petr |
Petr |
Pavel |
Martin | 1
(4 rows)

První insert s Martinem se nepovedl, protože ještě nebyla vytvořená sekvence lide_id_seq, kterou využívá defaultní hodnota pro id.

Jelikož již v řádku se jménem Matin hodnota id je, a já ji z nějakého důvodu nechci změnit, použiji při vyplňování podmínku WHERE.

A nakonec přidám NOT NULL podmínku:

rimmer1=> ALTER TABLE lide ALTER id SET NOT NULL;
rimmer1=> INSERT INTO lide(id,jmeno) VALUES(NULL,'Ruprt');
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (Ruprt, null).

MySQL

MySQL umí použít jako defaultní hodnotu aktuální čas jen s datovým typem TIMESTAMP (který navíc může být v tabulce jen jednou). Takže pro vytvoření defaultní hodnoty aktuální čas se musí změnit typ sloupce.

Všiměte si, že je navrat_dluhu vyplněn defaultní hodnotou.

MySQL neumí ALTER TABLE … SET DEFAULT, takže přidání defaultní hodnoty se musí vždy dělat znovuvytvořením definice sloupce, obdobně jako v tomto příkladu. MySQL neumí použít pro defaultní hodnotu výrazy, takže defaultní hodnotu s časem +10 dní nevytvoříte.

MySQL nemá sekvence, takže si v něm ani nevytvoříte „typ“ SERIAL. Můžete ovšem dodatečně přidat AUTO_INCREMENT, což jde ovšem jen na primární klíč.

mysql> ALTER TABLE lide CHANGE COLUMN id id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL;
mysql> INSERT INTO lide(jmeno) values ('Martin');
mysql> SELECT * FROM lide;
+--------+----+
| jmeno | id |
+--------+----+
| Petr | 1 |
| Petr | 2 |
| Pavel | 3 |
| Martin | 4 |
+--------+----+
4 rows in set (0.00 sec)

SQLite

ALTER TABLE toho v SQLite moc neumí. Už je to popsáno v kapitole o úpravě tabulky. Prostě budete muset tabulku vytvořit znova …

SQLite také nezná sekvence, čímž se mi výklad zjednodušuje.

Oracle

Oracle neumí ALTER TABLE … SET DEFAULT. Místo toho se používá toto:

oracle> ALTER TABLE dluhy MODIFY(navrat_dluhu DEFAULT CURRENT_TIMESTAMP);
oracle> SELECT * FROM dluhy;
JMENO PRIJMENI DLUH ZADLUZEN NAVRAT_DLUHU
Linus Torvalds 3105 09/22/2002 -
Martin Doktor 1590 09/22/2002 -

Toto v Oracle funguje stejně jako v Postgresu:

oracle> ALTER TABLE dluhy ADD navrat_dluhu DATE DEFAULT CURRENT_DATE+10;
oracle> SELECT * FROM dluhy;
JMENO PRIJMENI DLUH ZADLUZEN NAVRAT_DLUHU
Linus Torvalds 3105 09/22/2002 01/31/2014
Martin Doktor 1590 09/22/2002 01/31/2014

Oracle nemá ani AUTO_INCREMENT, ani neumí použít sekvence jako defaultní hodnoty, čímž se mi výklad zase zjednodušuje :-).