Corso di SQL: 2 – Creazione e Gestione Tabelle

2 - Creazione e gestione tabelle

Questo modulo spiega tutto quello che serve per progettare e costruire schemi relazionali: creare database, dichiarare tabelle, scegliere tipi di dato (INT, VARCHAR, DATE, BOOLEAN), imporre vincoli, modificare strutture con ALTER TABLE, cancellare con DROP TABLE. Ogni concetto è corredato da esempi pratici e da consigli operativi e di deploy.

Nota: mostro esempi compatibili con MySQL / MariaDB e PostgreSQL quando ci sono differenze rilevanti. Le DDL sono in SQL standard ove possibile; per operazioni specifiche evidenzio la sintassi dialettale.

---

1) Creare un database

Sintassi di base

MySQL

CREATE DATABASE IF NOT EXISTS ecommerce
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;
USE ecommerce;

PostgreSQL

CREATE DATABASE ecommerce
  WITH OWNER = myuser
       ENCODING = 'UTF8'
       LC_COLLATE = 'en_US.utf8'
       LC_CTYPE = 'en_US.utf8'
       TEMPLATE = template0;
-- poi connettersi:
\c ecommerce

Buone pratiche

• specificare IF NOT EXISTS per rendere idempotente la DDL;

• scegliere utf8mb4 (MySQL) o UTF8 (Postgres) per supportare emoji e simboli;

• definire owner/permessi corretti.

---

2) CREATE TABLE — sintassi e componenti principali

Struttura tipica:

CREATE TABLE nome_tabella (
  col1   TIPO [NOT NULL] [DEFAULT ...],
  col2   TIPO [NOT NULL] [DEFAULT ...],
  ...,
  CONSTRAINT nome_vincolo PRIMARY KEY (col1),
  CONSTRAINT fk_nome FOREIGN KEY (colX) REFERENCES altra_tabella(pk) ON DELETE CASCADE
);

Tipi di dato principali (focus richiesto)

• INT, BIGINT — interi (usa INT per id, BIGINT per contatori molto grandi).
  MySQL: INT ~ 4 byte.

• VARCHAR(n) — stringa di lunghezza variabile (specifica un limite n); conserva spazio rispetto a CHAR.

• TEXT — testo lungo (quando VARCHAR non basta).

• DATE — data (YYYY-MM-DD).

• TIMESTAMP / DATETIME — data+ora (attenzione a fusi orari: Postgres TIMESTAMP WITH TIME ZONE vs WITHOUT).

• BOOLEAN — vero/falso. In MySQL spesso TINYINT(1) è usato storicamente; MySQL 8 supporta alias BOOLEAN.

• DECIMAL(p,s) — numeri decimali esatti (prezzo: DECIMAL(10,2)).

Scelta tipica per prezzi: price DECIMAL(10,2) (massimo 8 cifre prima della virgola e 2 decimali).

---

3) Esempio completo di schema: ecommerce

Creiamo un piccolo schema con customers, products, orders, order_items.

Postgres (sintassi standard):

-- 1. customers
CREATE TABLE customers (
  customer_id   SERIAL PRIMARY KEY,     -- autoincrement (Postgres)
  email         VARCHAR(255) NOT NULL UNIQUE,
  full_name     VARCHAR(150) NOT NULL,
  signup_date   DATE DEFAULT CURRENT_DATE,
  is_active     BOOLEAN DEFAULT TRUE
);
-- 2. products
CREATE TABLE products (
  product_id    SERIAL PRIMARY KEY,
  sku           VARCHAR(50) NOT NULL UNIQUE,
  name          VARCHAR(255) NOT NULL,
  description   TEXT,
  price         DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  available_qty INT        DEFAULT 0
);
-- 3. orders
CREATE TABLE orders (
  order_id      BIGSERIAL PRIMARY KEY,
  customer_id   INT NOT NULL,
  created_at    TIMESTAMP WITH TIME ZONE DEFAULT now(),
  total_amount  DECIMAL(12,2) NOT NULL DEFAULT 0,
  status        VARCHAR(20) NOT NULL DEFAULT 'pending',
  CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);
-- 4. order_items (righe d'ordine)
CREATE TABLE order_items (
  order_item_id SERIAL PRIMARY KEY,
  order_id      BIGINT NOT NULL,
  product_id    INT NOT NULL,
  unit_price    DECIMAL(10,2) NOT NULL,
  quantity      INT NOT NULL CHECK (quantity > 0),
  CONSTRAINT fk_oi_orders FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
  CONSTRAINT fk_oi_products FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
);

MySQL (note sulle differenze):

• usare INT AUTO_INCREMENT invece di SERIAL/BIGSERIAL.

• definire engine InnoDB per supporto FK: ... ) ENGINE=InnoDB;

---

4) Vincoli (constraints) — cosa e perché

• PRIMARY KEY — identifica univocamente la riga; crea implicitamente indice univoco.

• UNIQUE — garanzia di unicità su una/e colonna/e.

• NOT NULL — obbliga valore presente.

• DEFAULT — valore di default quando INSERT non fornisce la colonna.

• CHECK (condizione) — validazione (Postgres applica sempre, MySQL la supporta pienamente solo da versioni recenti).

• FOREIGN KEY — integrità referenziale (ON DELETE/ON UPDATE: CASCADE, RESTRICT, SET NULL).

Esempio: vincolo CHECK

ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price >= 0);

---

5) Indici e performance (breve ma pratico)

• PRIMARY KEY e UNIQUE creano indici automaticamente.

• creare indici aggiuntivi per colonne usate in WHERE, JOIN, ORDER BY:

  CREATE INDEX idx_products_sku ON products(sku);
  CREATE INDEX idx_orders_customer ON orders(customer_id);

• evitare indici su colonne con pochi valori distinti (low cardinality) a meno che non siano parte di composite index.

---

6) ALTER TABLE — modificare tabelle in produzione

Operazioni comuni

• Aggiungere colonna:

  ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
  

• Aggiungere colonna con default e NOT NULL (procedura corretta):

  1. aggiungi colonna nullable con default (veloce);

  2. popoli i valori esistenti (se occorre);

  3. imposti NOT NULL.

  ALTER TABLE customers ADD COLUMN country VARCHAR(2) DEFAULT 'IT';
  UPDATE customers SET country = 'IT' WHERE country IS NULL;
  ALTER TABLE customers ALTER COLUMN country SET NOT NULL;

• Modificare tipo colonna:

  • Postgres:

    ALTER TABLE products ALTER COLUMN price TYPE DECIMAL(12,2) USING price::DECIMAL(12,2);
    

  • MySQL:

    ALTER TABLE products MODIFY COLUMN price DECIMAL(12,2) NOT NULL;
    

• Rinominare colonna:

  • Postgres: ALTER TABLE table RENAME COLUMN old TO new;

  • MySQL: ALTER TABLE table CHANGE old new TIPO; (o RENAME COLUMN su versioni recenti)

• Aggiungere chiave esterna:

  ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id) ON DELETE RESTRICT;

• Rimuovere colonna / vincolo:

  ALTER TABLE order_items DROP COLUMN order_item_id;
  ALTER TABLE products DROP CONSTRAINT chk_price_positive;  -- Postgres

Cambiare schema su tabelle grandi: tecnica sicura

Per modifiche pesanti su tabelle molto grandi (ad es. cambiare tipo o aggiungere NOT NULL):

1. creare nuova colonna temporanea con il tipo desiderato;

2. eseguire UPDATE in batch per popolare (evitando lock lunghi);

3. verificare;

4. rinominare colonne (in transazione dove possibile) e rimuovere la vecchia.

---

7) DROP TABLE e cancellazione sicura

• Sintassi:

  DROP TABLE IF EXISTS order_items;
  

• Caveat:

  • DROP TABLE elimina struttura e dati. Usare con cautela.

  • in Postgres: DROP TABLE nome CASCADE; rimuove anche oggetti dipendenti (es. view, FK). Usare solo se si sa cosa si fa.

• Consiglio operativo: in produzione, prima RENAME TABLE orders TO orders_old; e solo dopo verifica DROP TABLE orders_old;.

---

8) Transazioni e DDL

• Postgres: molte DDL (CREATE TABLE, ALTER TABLE, ...) sono transazionali — si possono fare BEGIN; CREATE TABLE...; ROLLBACK; e annullare.

• MySQL: alcune operazioni DDL sono auto-commit e non sono reversibili con ROLLBACK (dipende dalla versione e motore).
  Prudenza: eseguire DDL in finestre di manutenzione, fare backup.

---

9) Normalizzazione e scelta delle chiavi

• 1NF: colonne atomiche.

• 2NF/3NF: evitare ridondanze; separare entità distinte in tabelle correlate.

• Chiavi surrogate (es. id autoincrement) vs chiavi naturali (es. email): spesso si preferisce una PK surrogate e UNIQUE per chiavi naturali, perché è più flessibile per modifiche future.

---

10) Esempi pratici con dati e output simulato

Inserimenti (INSERT) e controllo struttura

INSERT INTO customers (email, full_name) VALUES
('alice@example.com', 'Alice Rossi'),
('bob@example.com', 'Bob Bianchi');
SELECT * FROM customers;

Output atteso (tabella):

 customer_id |      email        |   full_name   | signup_date | is_active
-------------+-------------------+---------------+-------------+-----------
 1           | alice@example.com | Alice Rossi   | 2025-09-13  | true
 2           | bob@example.com   | Bob Bianchi   | 2025-09-13  | true

Visualizzare struttura tabella

• MySQL

  DESCRIBE products;
  SHOW CREATE TABLE products;

• Postgres

  \d+ products
  

---

11) Esercizi pratici (proposti con soluzione guida)

Esercizio 1 — Crea DB e tabella semplice

Crea database library, tabella books(id, title, author, published DATE, pages INT, available BOOLEAN).

Soluzione (Postgres):

CREATE DATABASE library;
\c library
CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author VARCHAR(150),
  published DATE,
  pages INT CHECK (pages > 0),
  available BOOLEAN DEFAULT TRUE
);

Esercizio 2 — Aggiungi colonna e imposta NOT NULL (su tabella esistente con dati)

Aggiungere isbn VARCHAR(20) e poi assicurarsi che sia NOT NULL per le nuove righe.

Soluzione (sicura):

ALTER TABLE books ADD COLUMN isbn VARCHAR(20);
-- popola isbn per le righe esistenti se possibile
UPDATE books SET isbn = 'UNKNOWN' WHERE isbn IS NULL;
ALTER TABLE books ALTER COLUMN isbn SET NOT NULL;
ALTER TABLE books ADD CONSTRAINT unq_books_isbn UNIQUE (isbn);

Esercizio 3 — Relazione 1:N (orders & customers)

Crea tabelle customers e orders e una FK con ON DELETE CASCADE.

Soluzione:

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY, customer_id INT NOT NULL,
  total DECIMAL(10,2) DEFAULT 0,
  CONSTRAINT fk_orders_cust FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

---

12) Checklist di deploy e sicurezza

• fare backup (dump) prima di ALTER/DROP: pg_dump, mysqldump;

• usare IF EXISTS e IF NOT EXISTS per script idempotenti;

• testare DDL in ambiente di staging prima di produzione;

• considerare maintenance window per modifiche che bloccano tabelle;

• monitorare locks: SHOW PROCESSLIST (MySQL) / pg_locks (Postgres);

• limitare permessi: solo DBAs possono fare DDL; usare role/ruoli.

---

13) Riassunto pratico — comandi rapidi

• Creare DB

  • MySQL: CREATE DATABASE name CHARACTER SET utf8mb4; USE name;

  • Postgres: CREATE DATABASE name WITH ENCODING 'UTF8';

• Creare tabella: CREATE TABLE ...(...)

• Aggiungere colonna: ALTER TABLE ... ADD COLUMN ...;

• Modificare colonna: ALTER TABLE ... ALTER COLUMN ... TYPE ...; (Postgres) / MODIFY (MySQL)

• Aggiungere FK: ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ...

• Eliminare tabella: DROP TABLE IF EXISTS name CASCADE;