2. blokk · Táblák és adatok · 5. témakör
A táblák ritkán véglegesek: új igények jönnek, oszlopot kell hozzáadni, típust módosítani, megszorítást felvenni. Az ALTER TABLE a már létező táblák szerkezetét módosítja — anélkül, hogy újra kellene építeni őket. A témakör végén élőben is kipróbálhatod.
Az ALTER TABLE a DDL utasítások közé tartozik (mint a CREATE), és
egy létező tábla szerkezetét módosítja. A leggyakoribb műveletek:
| Cél | Kulcsszó |
|---|---|
| Új oszlop hozzáadása | ADD COLUMN |
| Oszlop típusának módosítása | MODIFY COLUMN |
| Oszlop átnevezése (+ típus) | CHANGE COLUMN / RENAME COLUMN |
| Oszlop törlése | DROP COLUMN |
| Megszorítás / index hozzáadása-törlése | ADD / DROP |
| Tábla átnevezése | RENAME TO |
Az ALTER éles, adatokat tartalmazó táblán is fut, de egyes műveletek (pl. oszlop törlése, típusszűkítés) adatvesztéssel járhatnak, és nagy táblán lassúak lehetnek. Mindig legyen mentés, és teszteld előbb fejlesztői környezetben!
Tegyük fel, hogy a termékekhez szeretnénk egy leiras mezőt és egy
letrehozva időbélyeget:
ALTER TABLE termek ADD COLUMN leiras TEXT; -- Több művelet egyszerre, vesszővel elválasztva: ALTER TABLE termek ADD COLUMN suly DECIMAL(6,2) NULL, ADD COLUMN aktiv TINYINT(1) NOT NULL DEFAULT 1;
Az új oszlop pozícióját is megadhatjuk (ez MySQL-specifikus kiegészítés). Pontosan három lehetőség van:
FIRST — az új oszlop a tábla első oszlopa
lesz.AFTER oszlopnev — közvetlenül a megadott oszlop
mögé kerül.-- A vonalkod közvetlenül a nev mögé kerül: ALTER TABLE termek ADD COLUMN vonalkod VARCHAR(20) AFTER nev; -- Ugyanezek a jelölők MEGLÉVŐ oszlop áthelyezésére is jók (a MODIFY/CHANGE végén): ALTER TABLE termek MODIFY COLUMN vonalkod VARCHAR(20) FIRST;
„Elé tevő” BEFORE jelölő nem létezik a
MySQL-ben — ha egy oszlop elé szeretnél szúrni, használd az előtte álló oszlop
AFTER-jét, a tábla elejére pedig a
FIRST-öt.
Ha létező adatokhoz adsz NOT NULL oszlopot, adj hozzá
DEFAULT értéket is — különben a meglévő soroknak nem lenne mit
beírni, és hibát kapsz.
-- A leiras legyen VARCHAR(500) a TEXT helyett, és kötelező: ALTER TABLE termek MODIFY COLUMN leiras VARCHAR(500) NOT NULL;
-- A 'leiras' oszlop új neve 'megnevezes', új típussal: ALTER TABLE termek CHANGE COLUMN leiras megnevezes VARCHAR(300) NOT NULL;
ALTER TABLE vasarlo RENAME COLUMN email TO email_cim;
(A RENAME COLUMN a MySQL 8.0 óta létezik — régebbi szerveren az
átnevezés a CHANGE-dzsel oldható meg.)
| Utasítás | Átnevez? | Típust állít? |
|---|---|---|
MODIFY COLUMN | Nem | Igen |
CHANGE COLUMN | Igen | Igen (kötelező) |
RENAME COLUMN | Igen | Nem |
Az ADD, MODIFY,
CHANGE és DROP után a
COLUMN kulcsszó csak az olvashatóságot szolgálja — nélküle is
ugyanúgy működik: ALTER TABLE termek ADD leiras TEXT;.
Kivétel a RENAME COLUMN: ott a
COLUMN kötelező, mert a sima
RENAME a táblát nevezné át.
(Megtévesztő lehet, hogy a lenti SQLite-alapú gyakorló a
RENAME ar TO netto_ar formát is elfogadja — az SQLite-ban a
COLUMN itt is elhagyható. Éles MySQL-en ez szintaxishiba!)
Ha szűkebb típusra váltasz (pl. VARCHAR(500) →
VARCHAR(50), vagy INT →
TINYINT), a be nem férő adatok csonkolódhatnak vagy hibát
okoznak. Ellenőrizd a meglévő adatokat módosítás előtt!
ALTER TABLE termek DROP COLUMN vonalkod;
A DROP COLUMN az oszlopot az összes benne tárolt adattal
együtt törli. Nincs „visszavonás” — ezért fontos a mentés.
Utólag is felvehetünk vagy eltávolíthatunk elsődleges/idegen kulcsot, egyedi megszorítást és indexet:
-- Egyedi megszorítás felvétele: ALTER TABLE vasarlo ADD UNIQUE (email); -- Index felvétele a gyorsabb kereséshez: ALTER TABLE termek ADD INDEX idx_nev (nev); -- Idegen kulcs felvétele (névvel, hogy később törölhető legyen): ALTER TABLE rendeles ADD CONSTRAINT fk_rendeles_vasarlo FOREIGN KEY (vasarlo_id) REFERENCES vasarlo(id); -- Törlés: ALTER TABLE rendeles DROP FOREIGN KEY fk_rendeles_vasarlo; ALTER TABLE termek DROP INDEX idx_nev; ALTER TABLE vasarlo DROP PRIMARY KEY;
(Vigyázat: a DROP PRIMARY KEY hibát ad, amíg a kulcs oszlopa
AUTO_INCREMENT — MySQL-ben az auto-increment oszlopnak kulcsnak
kell lennie, ezért előbb MODIFY-jal el kell venni tőle az
AUTO_INCREMENT tulajdonságot.)
Ha idegen kulcsot adsz hozzá, érdemes CONSTRAINT névvel ellátni
(pl. fk_rendeles_vasarlo). Így később egyértelműen tudsz rá
hivatkozni a DROP FOREIGN KEY-nél.
-- Két egyenértékű megoldás: ALTER TABLE kategoria RENAME TO termekkategoria; RENAME TABLE kategoria TO termekkategoria;
A RENAME TABLE előnye, hogy egyszerre több táblát is átnevezhetsz,
sőt sémák között is mozgathatsz.
Próbáld ki az ALTER műveleteket a webshop adatbázison! A séma és a példaadatok be vannak
töltve. Módosíts táblákat, majd egy SELECT-tel nézd meg az
eredményt.
A beépített motor SQLite-alapú, és az SQLite ALTER-támogatása szűkebb a
MySQL-énél. Itt működik: ADD COLUMN,
RENAME COLUMN, DROP COLUMN,
RENAME TO. Itt NEM fut (de éles MySQL-en igen):
MODIFY, CHANGE, valamint a
megszorítások ALTER-rel való hozzáadása/törlése (ADD UNIQUE,
ADD FOREIGN KEY stb.). Ha ilyet próbálsz, a gyakorló külön
jelzi. Az éles MySQL-en a fenti, tananyagbeli szintaxis a helyes.
⭐ alap · ⭐⭐ haladó · ⭐⭐⭐ kihívás — a kódírós feladatokat futtasd is le a fenti élő gyakorlóban!
Mit csinál pontosan ez az utasítás? Térj ki az AFTER szerepére is!
ALTER TABLE termek ADD COLUMN vonalkod VARCHAR(20) AFTER nev;
Hogyan nevezel át egy táblát? Adj meg legalább egy lehetőséget!
Mivel pozícionálhatod az új oszlopot a tábla elejére, illetve egy adott oszlop mögé?
Az alábbi utasítás hibát ad egy adatokat tartalmazó táblán. Miért, és hogyan javítanád?
ALTER TABLE termek ADD COLUMN gyarto VARCHAR(100) NOT NULL;
Hogyan adsz utólag egyedi megszorítást a vasarlo
tábla email oszlopára?
Mi a két különbség az alábbi két utasítás között?
ALTER TABLE termek MODIFY COLUMN leiras VARCHAR(300) NOT NULL; ALTER TABLE termek CHANGE COLUMN leiras megnevezes VARCHAR(300) NOT NULL;
Kódírás: adj a termek táblához egy
leiras nevű, hosszú szöveges (nem kötelező) oszlopot!
Futtasd le a gyakorlóban, és ellenőrizd egy SELECT-tel.
Kódírás: a rendeles táblához adj egy
szallitasi_dij oszlopot (pontos tizedes, nem lehet üres,
alapból 0), majd nevezd át a táblát megrendeles-re!
Kódírás: vegyél fel egy névvel ellátott idegen kulcsot a
termek.kategoria_id-ra (a
kategoria.id-ra hivatkozva), majd írd le, hogyan törölnéd!
(A gyakorló erre MySQL-jelzést ad — éles MySQL-en futtatható.)
Minden kérdésnél egy válasz helyes. A végén pontszámot kapsz.
| Fogalom | Jelentés |
|---|---|
| ALTER TABLE | létező tábla szerkezetének módosítása (DDL) |
| ADD COLUMN | új oszlop felvétele — NOT NULL esetén DEFAULT-tal |
| MODIFY COLUMN | típus/megszorítás módosítása, a név marad |
| CHANGE COLUMN | átnevezés + típus egyben (a típus megadása kötelező) |
| RENAME COLUMN | csak átnevezés, típus nélkül |
| DROP COLUMN | oszlop törlése az adataival együtt — visszavonhatatlan |
| AFTER / FIRST | oszloppozíció az ADD/MODIFY/CHANGE végén — jelölő nélkül a tábla vége (MySQL-specifikus) |
| ADD CONSTRAINT név | névvel ellátott megszorítás — később név szerint törölhető |
| RENAME TO / RENAME TABLE | tábla átnevezése (utóbbival több tábla egyszerre) |
| típusszűkítés | kisebb kapacitású típusra váltás — csonkolás- és hibaveszély |
ALTER TABLE létező tábla szerkezetét módosítja (DDL).ADD COLUMN — új oszlop (NOT NULL-hoz adj DEFAULT-ot!);
pozíció: AFTER / FIRST
(jelölő nélkül a tábla végére).MODIFY (típus), CHANGE
(név + típus), RENAME COLUMN (csak név).DROP COLUMN — oszlop és adatai törlése (visszavonhatatlan).ADD /
DROP (idegen kulcsot nevezz el!).RENAME TO / RENAME TABLE — tábla
átnevezése.