ALTER TABLE i DROP

ALTER TABLE

ALTER TABLE permet modificar l'estructura d'una taula existent sense eliminar les dades que conté. És l'eina central de les migracions d'esquema: quan l'aplicació evoluciona i l'esquema ha de canviar, s'usen instruccions ALTER TABLE.

Afegir una columna

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Afegir una columna al final
ALTER TABLE clients ADD COLUMN adreca VARCHAR(300);

-- Afegir amb valor per defecte
ALTER TABLE clients ADD COLUMN punts_fidelitat INTEGER NOT NULL DEFAULT 0;

-- Afegir una columna NOT NULL amb valor per defecte (cal valor per defecte per a files existents)
ALTER TABLE clients ADD COLUMN data_modificacio TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Afegir al final
ALTER TABLE clients ADD COLUMN adreca VARCHAR(300);

-- Afegir en una posició específica
ALTER TABLE clients ADD COLUMN punts_fidelitat INT NOT NULL DEFAULT 0 AFTER email;

-- Afegir al principi
ALTER TABLE clients ADD COLUMN codi_intern CHAR(10) FIRST;

-- Afegir una columna
ALTER TABLE clients ADD adreca NVARCHAR(300) NULL;

-- Afegir amb valor per defecte
ALTER TABLE clients ADD punts_fidelitat INT NOT NULL DEFAULT 0;

-- Afegir una columna
ALTER TABLE clients ADD adreca VARCHAR2(300);

-- Afegir amb valor per defecte
ALTER TABLE clients ADD punts_fidelitat NUMBER(10) DEFAULT 0 NOT NULL;

-- Afegir amb marca de temps
ALTER TABLE clients ADD data_modificacio TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

Modificar una columna

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Canviar el tipus de dades
ALTER TABLE clients ALTER COLUMN telefon TYPE VARCHAR(20);

-- Afegir NOT NULL
ALTER TABLE clients ALTER COLUMN email SET NOT NULL;

-- Eliminar NOT NULL
ALTER TABLE clients ALTER COLUMN email DROP NOT NULL;

-- Canviar el valor per defecte
ALTER TABLE clients ALTER COLUMN actiu SET DEFAULT FALSE;

-- Reanomenar una columna
ALTER TABLE clients RENAME COLUMN telefon TO telefon_mobil;

-- Modificar tipus i restriccions (MODIFY)
ALTER TABLE clients MODIFY COLUMN telefon VARCHAR(20) NOT NULL;

-- Reanomenar i modificar (CHANGE)
ALTER TABLE clients CHANGE COLUMN telefon telefon_mobil VARCHAR(20) NOT NULL;

-- Canviar només el nom (RENAME COLUMN, MySQL 8.0+)
ALTER TABLE clients RENAME COLUMN telefon TO telefon_mobil;

-- Canviar el tipus de dades
ALTER TABLE clients ALTER COLUMN telefon NVARCHAR(20);

-- Reanomenar una columna (procediment del sistema)
EXEC sp_rename 'clients.telefon', 'telefon_mobil', 'COLUMN';

-- Canviar el tipus de dades
ALTER TABLE clients MODIFY telefon VARCHAR2(20);

-- Afegir NOT NULL (la columna no ha de tenir valors NULL)
ALTER TABLE clients MODIFY email NOT NULL;

-- Canviar el valor per defecte
ALTER TABLE clients MODIFY actiu DEFAULT 0;

-- Reanomenar una columna (Oracle 9i R2+)
ALTER TABLE clients RENAME COLUMN telefon TO telefon_mobil;

Eliminar una columna

Atenció: eliminar una columna és irreversible

DROP COLUMN elimina la columna i totes les dades que conté de forma permanent. Verificar sempre que cap aplicació ni vista depèn de la columna antes d'executar-ho en producció.

PostgreSQLMySQL / MariaDBSQL ServerOracle

ALTER TABLE clients DROP COLUMN adreca;

-- Amb IF EXISTS (no dona error si no existeix)
ALTER TABLE clients DROP COLUMN IF EXISTS adreca;

-- Eliminar i propagar (elimina les dependències com vistes que usen la columna)
ALTER TABLE clients DROP COLUMN adreca CASCADE;

ALTER TABLE clients DROP COLUMN adreca;

ALTER TABLE clients DROP COLUMN adreca;

ALTER TABLE clients DROP COLUMN adreca;

-- Oracle no suporta IF EXISTS en DROP COLUMN
-- Alternativa: marcar com a inutilitzada (operació instantània)
ALTER TABLE clients SET UNUSED COLUMN adreca;
-- Eliminar totes les columnes inutilitzades en un moment de poca càrrega
ALTER TABLE clients DROP UNUSED COLUMNS;

Gestió de restriccions

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Afegir una restricció CHECK
ALTER TABLE productes ADD CONSTRAINT chk_preu CHECK (preu > 0);

-- Afegir una FOREIGN KEY
ALTER TABLE comandes ADD CONSTRAINT fk_com_cli
    FOREIGN KEY (id_client) REFERENCES clients(id_client);

-- Afegir UNIQUE
ALTER TABLE clients ADD CONSTRAINT uq_clients_dni UNIQUE (dni);

-- Eliminar una restricció
ALTER TABLE productes DROP CONSTRAINT chk_preu;

-- Desactivar/activar una restricció temporalment
ALTER TABLE comandes DISABLE TRIGGER ALL;  -- Triggers
-- Per a FK: no hi ha DISABLE directe; cal DROP i recrear

-- Afegir FK
ALTER TABLE comandes ADD CONSTRAINT fk_com_cli
    FOREIGN KEY (id_client) REFERENCES clients(id_client);

-- Eliminar FK (cal conèixer el nom)
ALTER TABLE comandes DROP FOREIGN KEY fk_com_cli;

-- Desactivar comprovació de FK temporalment (per a càrregues massives)
SET FOREIGN_KEY_CHECKS = 0;
-- ... operació de càrrega ...
SET FOREIGN_KEY_CHECKS = 1;

-- Afegir CHECK
ALTER TABLE productes ADD CONSTRAINT chk_preu CHECK (preu > 0);

-- Desactivar una restricció
ALTER TABLE productes NOCHECK CONSTRAINT chk_preu;

-- Reactivar
ALTER TABLE productes CHECK CONSTRAINT chk_preu;

-- Eliminar
ALTER TABLE productes DROP CONSTRAINT chk_preu;

-- Afegir CHECK
ALTER TABLE productes ADD CONSTRAINT chk_preu CHECK (preu > 0);

-- Afegir FOREIGN KEY
ALTER TABLE comandes ADD CONSTRAINT fk_com_cli
    FOREIGN KEY (id_client) REFERENCES clients(id_client);

-- Afegir UNIQUE
ALTER TABLE clients ADD CONSTRAINT uq_clients_dni UNIQUE (dni);

-- Desactivar una restricció
ALTER TABLE comandes DISABLE CONSTRAINT fk_com_cli;

-- Reactivar
ALTER TABLE comandes ENABLE CONSTRAINT fk_com_cli;

-- Eliminar
ALTER TABLE productes DROP CONSTRAINT chk_preu;

Reanomenar una taula

PostgreSQLMySQL / MariaDBSQL ServerOracle

ALTER TABLE clients RENAME TO clients_v2;

RENAME TABLE clients TO clients_v2;
-- O bé:
ALTER TABLE clients RENAME TO clients_v2;

EXEC sp_rename 'clients', 'clients_v2';

ALTER TABLE clients RENAME TO clients_v2;
-- O bé (sintaxi curta):
RENAME clients TO clients_v2;

---

DROP TABLE i DROP DATABASE

DROP TABLE

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Eliminar una taula
DROP TABLE linies_comanda;

-- IF EXISTS: no dona error si no existeix
DROP TABLE IF EXISTS linies_comanda;

-- CASCADE: elimina també les taules i objectes que en depenen
DROP TABLE clients CASCADE;

-- RESTRICT (per defecte): rebutja si hi ha dependències
DROP TABLE clients RESTRICT;

-- Eliminar una taula
DROP TABLE linies_comanda;

DROP TABLE IF EXISTS linies_comanda;

-- Eliminar múltiples taules
DROP TABLE linies_comanda, comandes, clients;

-- Eliminar una taula
DROP TABLE linies_comanda;

DROP TABLE IF EXISTS linies_comanda;

-- Verificar si existeix antes d'eliminar (estil clàssic)
IF OBJECT_ID('linies_comanda', 'U') IS NOT NULL
    DROP TABLE linies_comanda;

-- Eliminar una taula (es mou a la Recycle Bin)
DROP TABLE linies_comanda;

-- Eliminar sense Recycle Bin (permanent i immediat)
DROP TABLE linies_comanda PURGE;

-- Oracle no suporta IF EXISTS (fins Oracle 23c)
-- Verificar prèviament:
SELECT COUNT(*) FROM user_tables WHERE table_name = 'LINIES_COMANDA';

-- Buidar la Recycle Bin
PURGE RECYCLEBIN;

DROP DATABASE

DROP DATABASE és irrecuperable

DROP DATABASE elimina la base de dades completa amb totes les seves taules i dades. No hi ha confirmació prèvia. Si no hi ha un backup recent, les dades es perden per sempre. Mai executeu DROP DATABASE en un sistema de producció sense verificar tres vegades i sense un backup valid i testat.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Cal no estar connectat a la BD que és vol eliminar
-- Des de psql, connectat a 'postgres':
DROP DATABASE gbd_practica;

-- IF EXISTS
DROP DATABASE IF EXISTS gbd_practica;

DROP DATABASE gbd_practica;
DROP DATABASE IF EXISTS gbd_practica;
-- 'SCHEMA' és sinònim de 'DATABASE' a MySQL
DROP SCHEMA IF EXISTS gbd_practica;

USE master;
GO
DROP DATABASE gbd_practica;
GO

-- A Oracle no s'elimina una BD completa com en altres motors.
-- Per eliminar una PDB (Pluggable Database):
ALTER PLUGGABLE DATABASE gbd_practica CLOSE IMMEDIATE;
DROP PLUGGABLE DATABASE gbd_practica INCLUDING DATAFILES;

-- Per eliminar tot el contingut d'un esquema (usuari):
DROP USER vendes CASCADE;
-- CASCADE elimina tots els objectes (taules, vistes, etc.) de l'usuari

TRUNCATE TABLE

TRUNCATE TABLE elimina totes les files d'una taula molt més ràpidament que DELETE FROM taula (sense WHERE), perquè no registra cada fila eliminada al log de transaccions. Pero té algunes diferències importants:

Característica	DELETE (sense WHERE)	TRUNCATE
Velocitat	Lent (registra cada fila)	Molt ràpid
Pot fer ROLLBACK	Sí (dins d'una transacció)	Depèn del motor
Reseteja SERIAL/AUTO_INCREMENT	No	Sí (en alguns motors)
Activa triggers DELETE	Sí	No
Respecta FK	Sí	Depèn del motor

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Truncar una taula
TRUNCATE TABLE linies_comanda;

-- Truncar i reiniciar la seqüència (SERIAL)
TRUNCATE TABLE linies_comanda RESTART IDENTITY;

-- Truncar en cascada (taules referenciades per FK)
TRUNCATE TABLE clients CASCADE;

-- TRUNCATE a MySQL/MariaDB no pot fer ROLLBACK
TRUNCATE TABLE linies_comanda;

TRUNCATE TABLE linies_comanda;
-- TRUNCATE és transaccional a SQL Server (pot fer ROLLBACK dins una transacció)

TRUNCATE TABLE linies_comanda;
-- TRUNCATE a Oracle és una instrucció DDL: fa un COMMIT implícit i no pot fer ROLLBACK

-- Alliberar l'espai físic (per defecte Oracle reutilitza l'espai però no el retorna)
TRUNCATE TABLE linies_comanda DROP STORAGE;

AC0372/03/06 — Miniactivitat

RA3 · CA3.7

Practiqueu les migracions d'esquema en el vostre contenidor Docker:

1. Creeu la taula clients de l'exemple anterior.
2. Afegiu una columna adreça amb ALTER TABLE.
3. Modifiqueu la columna telefon per ampliar la seva mida.
4. Afegiu una restricció CHECK que garanteixi que el DNI te exactament 9 caràcters.
5. Reanomeneu la taula a clients_v2.

Documenteu cada pas amb l'SQL executat i verifiqueu l'estructura de la taula amb els comandos del catàleg.