Creare e modificare le tabelle in SQL

Nelle lezioni precedenti abbiamo visto come interrogare i database attraverso SQL, utilizzando istruzioni come SELECT, WHERE, JOIN, GROUP BY e le altre funzionalità del linguaggio.

Finora abbiamo lavorato principalmente con dati già presenti nelle tabelle. Ora vediamo come creare e modificare la struttura di un database.

I tipi di dato in MySQL

Quando creiamo una tabella, per ogni colonna dobbiamo specificare un tipo di dato. Il tipo di dato stabilisce quali valori possono essere memorizzati nella colonna e, in alcuni casi, come MySQL deve memorizzarli.

Tra i tipi di dato più utilizzati troviamo:

Vediamo i tipi di dato in dettaglio.

Il tipo INT

INT viene utilizzato per memorizzare numeri interi.

eta INT

INT è il tipo predefinito per le colonne id.

Il tipo BIGINT

BIGINT salva numeri interi, ma permette di rappresentare numeri molto più grandi rispetto a INT.

Può essere utile, ad esempio, quando dobbiamo memorizzare identificativi o contatori che potrebbero superare il limite di INT (di 4 byte, circa 4 miliardi di numeri). 

id BIGINT

Il tipo VARCHAR

VARCHAR viene utilizzato per memorizzare stringhe di lunghezza variabile. Tra parentesi, è necessario indicare la lunghezza massima che avranno le stringhe salvate nella colonna.

Ad esempio, per una stringa di massimo 100 caratteri:

nome VARCHAR(100)

Il tipo TEXT

Il tipo TEXT è pensato per contenere testi più lunghi. Può essere utilizzato per descrizioni, articoli, commenti e altri contenuti testuali di dimensioni maggiori.

descrizione TEXT

Il tipo DECIMAL

DECIMAL viene utilizzato per numeri decimali che richiedono precisione esatta, come prezzi e importi monetari. È necessario indicare il numero massimo di cifre complessive e il numero di cifre decimali.

In questo caso 10 indica il numero massimo di cifre complessive, mentre 2 indica quante cifre possono essere presenti dopo la virgola:

prezzo DECIMAL(10, 2)

Il tipo DATE

DATE permette di memorizzare una data, generalmente nel formato YYYY-MM-DD.

data_nascita DATE

Il tipo DATETIME

DATETIME permette di memorizzare data e ora, di solito nel formato YYYY-MM-DD HH:MM:SS.

creato_il DATETIME

Il tipo JSON

Il tipo JSON permette di memorizzare dati strutturati nel formato JSON.

impostazioni JSON

Nella colonna impostazioni dell'esempio, possiamo salvare dati di questo tipo:

{"tema": "dark", "notifiche": true}

Il tipo ENUM

ENUM permette di definire un insieme di valori consentiti. Ad esempio:

stato ENUM('attivo', 'inattivo')

La colonna stato dell'esempio potrà quindi contenere uno dei valori definiti, attivo o inattivo.

È importante scegliere ENUM con criterio: è utile quando l'insieme dei valori è stabile e limitato, mentre per valori che possono cambiare frequentemente può essere più appropriata un tipo di dato diverso, come VARCHAR.

Il tipo BOOLEAN

BOOLEAN permette di memorizzare i valori boolean true o false nella colonna.

attivo BOOLEAN

Creare una tabella con CREATE TABLE

Per creare una nuova tabella utilizziamo l'istruzione CREATE TABLE, che ha la seguente sintassi:

CREATE TABLE nome_tabella (
    colonna1 TIPO,
    colonna2 TIPO,
    colonna3 TIPO
);

Per esempio, possiamo creare una tabella utenti:

CREATE TABLE utenti (
    id INT,
    nome VARCHAR(100),
    email VARCHAR(255),
    data_nascita DATE
);

Eseguendo la query, abbiamo creato una tabella con quattro colonne:

Ogni definizione di colonna contiene quindi almeno il nome della colonna e tipo di dato che può salvare. 

Possiamo aggiungere anche diversi vincoli, che stabiliscono ulteriori regole sui dati.

La PRIMARY KEY

Una delle colonne più importanti di una tabella è la chiave primaria, definita con PRIMARY KEY. La chiave primaria identifica in modo univoco ogni riga della tabella.

Ad esempio:

CREATE TABLE utenti (
    id INT PRIMARY KEY,
    nome VARCHAR(100),
    email VARCHAR(255)
);

In questo esempio, id è la chiave primaria. Quando una colonna è una chiave primaria:

Attributo AUTO_INCREMENT

Quando utilizziamo un identificativo numerico, può essere utile fare in modo che MySQL generi automaticamente un nuovo valore per ogni riga.

Possiamo utilizzare AUTO_INCREMENT:

CREATE TABLE utenti (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100),
    email VARCHAR(255)
);

Ora possiamo inserire un utente senza specificare manualmente id:

INSERT INTO utenti (nome, email)
VALUES ('Marco', '[email protected]');

MySQL assegnerà automaticamente un valore alla colonna id. Ad esempio, se sono già state inserite 20 righe alla tabella, al nuovo id verrà assegnato 21.

Attributo NOT NULL

Per impostazione predefinita, una colonna può generalmente contenere NULL. Se invece vogliamo obbligare una colonna ad avere un valore, possiamo utilizzare NOT NULL.

Ad esempio, potrebbe essere necessario che un account abbia sempre un'email:

CREATE TABLE utenti (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    nome VARCHAR(100)
);

La seguente istruzione di inserimento, quindi, non sarà valida, perché non viene fornito un valore per email:

INSERT INTO utenti (nome) VALUES ('Andrea');

Nota: approfondiremo INSERT nella lezione sugli inserimenti dei dati.

Attributo UNIQUE

Il vincolo UNIQUE permette di impedire che una colonna contenga valori duplicati.

Per esempio, l'indirizzo email di un utente dovrebbe essere univoco:

CREATE TABLE utenti (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    nome VARCHAR(100)
);

Non sarà quindi possibile inserire due righe con lo stesso valore di email.

Attributo DEFAULT

DEFAULT permette di specificare un valore predefinito per una colonna. Se eseguiamo una query di inserimento senza specificare un valore per una determinata colonna, MySQL utilizzerà il valore predefinito della colonna.

Ad esempio:

CREATE TABLE utenti (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    attivo BOOLEAN DEFAULT TRUE
);

Se inseriamo un utente senza specificare attivo, verrà scelto il valore true.

Vincolo di integrità CHECK

CHECK permette di stabilire una condizione che deve essere rispettata dal valore inserito.

Per esempio, vogliamo che prezzo sia sempre positivo:

CREATE TABLE prodotti (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    prezzo DECIMAL(10, 2) CHECK (prezzo >= 0)
);

Combinare più vincoli

Possiamo utilizzare più vincoli sulla stessa colonna.

Per esempio:

CREATE TABLE utenti (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    attivo BOOLEAN NOT NULL DEFAULT TRUE
);

In questo esempio:

Collegare le tabelle con le foreign key

Nelle lezioni sull'integrità referenziale abbiamo visto come collegare più tabelle attraverso colonne correlate.

Per rappresentare questo collegamento a livello di struttura del database possiamo utilizzare una foreign key, o chiave esterna.

Supponiamo di avere:

Possiamo creare le tabelle così:

CREATE TABLE utenti (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL
);
CREATE TABLE ordini (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT,
    totale DECIMAL(10, 2),
    FOREIGN KEY (cliente_id) REFERENCES utenti(id)
);

La sintassi FOREIGN KEY (cliente_id) REFERENCES utenti(id) stabilisce che cliente_id fa riferimento alla colonna id della tabella utenti. In questo modo MySQL può verificare l'integrità referenziale della relazione.

ON DELETE e ON UPDATE

Quando utilizziamo una foreign key, possiamo stabilire cosa deve accadere alle righe collegate quando viene eliminato o modificato il valore della tabella principale.

Possiamo farlo con ON DELETE e ON UPDATE.

Ad esempio:

CREATE TABLE ordini (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT,
    totale DECIMAL(10, 2),

    FOREIGN KEY (cliente_id)
        REFERENCES utenti(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CASCADE propaga l'operazione alla tabella collegata. Con ON DELETE CASCADE se viene eliminato un utente, vengono eliminati automaticamente anche gli ordini associati. Con ON UPDATE CASCADE una modifica alla chiave referenziata viene copiata anche sulla foreign key.

Si possono anche utilizzare:

Modificare una tabella

Dopo aver creato una tabella, possiamo aver bisogno di modificarne la struttura. Per farlo utilizziamo ALTER TABLE. Possiamo utilizzare le seguenti clausole:

Vediamoli in dettaglio.

Aggiungere una colonna

Per aggiungere una nuova colonna a una tabella esistente, usiamo ADD.

ALTER TABLE utenti
ADD telefono VARCHAR(20);

La tabella avrà quindi una nuova colonna telefono.

Possiamo anche aggiungere un vincolo o una proprietà:

ALTER TABLE utenti
ADD data_registrazione DATETIME DEFAULT CURRENT_TIMESTAMP;

Modificare una colonna

Possiamo modificare il tipo o altre caratteristiche di una colonna utilizzando MODIFY. Per esempio:

ALTER TABLE utenti
MODIFY nome VARCHAR(150) NOT NULL;

In questo caso la lunghezza massima di nome passa da quella precedente a 150 caratteri e la colonna viene impostata come NOT NULL.

Quando utilizziamo MODIFY, è importante specificare la definizione che vogliamo mantenere per la colonna, comprese eventuali caratteristiche come NOT NULL o DEFAULT.

Eliminare una colonna

Per eliminare una colonna utilizziamo DROP COLUMN seguito dal nome della colonna, ad esempio:

ALTER TABLE utenti
DROP COLUMN telefono;

La colonna telefono verrà rimossa dalla struttura della tabella, insieme ai dati che conteneva.

Eliminare una tabella

Se vogliamo eliminare un'intera tabella, possiamo utilizzare DROP TABLE, seguito dal nome della tabella. In questo modo, viene eliminata la struttura della tabella, le righe e i dati in essa contenuti, i relativi vincoli e indici definiti sulla tabella.

DROP TABLE utenti;

Rimuovere i dati di una tabella

Se vogliamo eliminare tutte le righe mantenendo la struttura della tabella, possiamo utilizzare la sintassi di TRUNCATE TABLE. Ad esempio:

TRUNCATE TABLE utenti;

Dopo l'operazione, la tabella continuerà a esistere e manterrà colonne, indici e vincoli, ma sarà vuota.