Capitolo 8

Creare e mantenere tabelle

Il capitolo precedente era dedicato a quella parte di SQL che assolve alle funzioni di DML (Data Manipulation Language). In questo capitolo tratteremo invece, quella parte di SQL che implementa le funzioni di DDL (Data Description Language) ovvero quelle funzioni che ci permettono di progettare, di manipolare e di distruggere quelle strutture che contengono i dati. L’acronimo Description infatti, sta a indicare ‘descrizione’ o meglio ‘definizione’ delle strutture che conterranno i dati.

Vedremo in dettaglio le istruzioni CREATE TABLE, ALTER TABLE, DROP TABLE, per creare, modificare ed eliminare tabelle. Esistono anche i comandi CREATE DATABASE e DROP DATABASE per creare e distruggere database; questi comandi però non verranno mostrati in questo corso in quanto la loro implementazione varia notevolmente da un DBMS ad un altro. Inoltre tutti i DBMS, oggi in commercio, hanno dei tools di tipo grafico che permettono di creare e di distruggere un database in modo facile e senza scrivere codice SQL.

CREATE TABLE

Questa istruzione, come facilmente si immagina, serve a creare una tabella; il suo schema sintattico è il seguente:

CREATE TABLE nomeTabella
               (
                 campo1 tipoDati [valore di default] [vincoli],
                 campo2 tipoDati [valore di default] [vincoli],
                 campo3 tipoDati [valore di default] [vincoli],
                 . . . . . . . . . . . . . . . . . . . . . . . .
                );

Vediamo un esempio:

CREATE TABLE ANAGRAFICA
          (
           NOME VACHAR2(25),
           COGNOME VARCHAR2(25),
           DATA_NASCITA DATE
          );

Questa soprastante è la tabella ANAGRAFICA che è stata creata; essa ha tre campi: due di tipo testo che possono contenere fino a 25 caratteri alfanumerici ognuna e un campo di tipo data che può contenere date o orari.

Particolare attenzione deve essere rivolta alle parole chiave utilizzate per la dichiarazione dei tipi dei campi, infatti le parole chiave preposte a questo scopo potrebbero cambiare da DBMS a DBMS. Per gli esempi di questo capitolo, come del resto anche per gli altri, si è fatto riferimento a SQL Plus 8 Oracle; la sintassi e le parole chiave della create precedente sono solo in parte accettate da Access. Provate a lanciare questa create dal modulo Query di Access, avendo però sostituito prima i tipi dati VARCHAR2 in CHAR e vi accorgerete che la tabella verrà creata, va però fatto notare che non sarà sempre così.

In Access esistono i corrispondenti tipi di dato che implementa Oracle ma le parole chiave utilizzate per dichiararli cambiano. Vediamo tramite lo schema seguente le parole chiave che utilizza Oracle per implementare la dichiarazione dei tipi di dato.

NOT NULL

In un campo dichiarato con il vincolo NOT NUILL non è possibile non inserire un valore. Se non inseriamo un valore valido in un campo di questo tipo i valori inseriti nella riga non vengono accettati. Facciamo un esempio:

Vogliamo creare una tabella di nome di TIFOSI con i campi: IDENTIFICATIVO, NOME, SQUDRA. Il campo IDENTIFICATIVO deve contenere obbligatoriamente un valore.

CREATE TABLE TIFOSI
     (
       IDENTIFICATIVO CHAR(9) NOT NULL,
       NOME VARCHAR2(25),
       SQUADRA VARCHAR2(25)
      );

Questa soprastante è la tabella generata dalla create dell’esempio. Se vogliamo che i dati inseriti nella riga vengono accettati dobbiamo per forza aver inserito anche un valore valido nel campo IDENTIFICATIVO, infatti questa è la funzione del vincolo NOT NULL. Anche questa create, dopo aver sostituito i tipi dati VARCHAR2 in CHAR, se lanciata dal modulo Query di Access viene accettata e genera la tabella corrispondente.

Qualcuno potrebbe pensare che più valori NULL in una tabella e magari nella stessa colonna possiedono lo stesso valore, ma non è così: due valori NULL nella stessa tabella non hanno lo stesso valore. Il valore NULL non è lo zero o il carattere space, ma è un ‘nulla’ virtuale ogni volta diverso e mai uguale ad ogni altro.

PRIMARY KEY

Questo vincolo ci permette di dichiarare un campo come chiave primaria. Ricordiamo che la chiave primaria rappresenta quel campo il cui valore rappresenta in maniera univoca la riga; quindi non è possibile trovare all’interno della tabella due valori uguali della chiave primaria. Facciamo un esempio:

Abbiamo bisogno di creare una tabella di nome CALCIATORI con i campi: ID_IDENTIFICATIVO, NOME, COGNOME. Il campo ID_IDENTIFICATIVO deve essere dichiarato chiave primaria:

CREATE TABLE CALCIATORI
     (
       ID_IDENTIFICATIVO CHAR(3) PRIMARY KEY,
       NOME VARCHAR2(20),
       COGNOME VARCHAR2(20)
      );

Questa create ha costruito una tabella in cui il campo ID_IDENTIFICATIVO è chiave primaria; questo significa che non è possibile avere all’interno della tabella due valori del campo ID_IDENTIFICATIVO uguali. Access non accetta la clausola PRIMARY KEY se usata insieme al comando CREATE TABLE.

FOREIGN KEY

La chiave esterna di una tabella rappresenta la chiave primaria della tabella master a cui è associata.

In questa immagine acquisita da Access possiamo vedere la tabella master Proprietari con la chiave primaria id_proprietario e la tabella slave Auto con la chiave esterna proprietario. Tramite la chiave esterna proprietario, riusciamo a sapere a chi appartiene quella auto.

Per sapere come si implementa un chiave esterna su di una tabella consultare il paragrafo ALTER TABLE.

UNIQUE

Questo vincolo non ci permette di inserire nella stessa colonna più di una volta lo stesso valore. Vediamo, con un esempio, di capire quale è la sua utilità:

Immaginiamo di aver bisogno di una tabella PERSONE la cui chiave primaria (ID_PERSONA) sia costituita da un valore numerico che si incrementi in maniera automatica ad ogni inserimento di una nuova persona. Vediamo come potrebbe essere e cosa potrebbe contenere questa tabella:

Nella tabella appaiono due MARIA. Considerando che nella realtà rappresentata dalla tabella diamo per scontato che non ci sono due persone con lo stesso nome è evidente che la doppia MARIA costituisce un errore dovuto al doppio inserimento. Dunque la chiave primaria ID_PERSONE non assolve al suo scopo di rappresentare in maniera univoca l’entità persona. Dovremo dunque rinunciare a questo tipo di chiave costituita da un contatore che si incrementa automaticamente ad ogni inserimento? (consultare il sottoparagrafo Campi contatori del paragrafo INSERT del capitolo 7, per saperne di più sui contatori) Sicuramente no! Infatti il problema si risolve facilmente inserendo il vincolo di non poter inserire valori uguali nella colonna NOME:

CREATE TABLE PERSONE
     (
       ID_PERSONA NUMBER PRIMARY KEY,
       NOME VARCHAR2(25) UNIQUE
      );

Nella tabella generata da questa create non sarà più possibile, grazie al vincolo UNIQUE, inserire inutilmente due volte la stessa persona. Qualcuno però potrebbe obbiettare che la realtà che stiamo rappresentando con la tabella potrebbe cambiare a tal punto da far si che ci siano persone con lo stesso nome. A questo punto dovremmo rinunciare a questo tipo di chiave? Niente affatto! Dovremo però utilizzare una tabella così creata:

CREATE TABLE PERSONE
      (
        ID_PERSONA NUMBER PRIMARY KEY,
        NOME VARCHAR2(25),
        COGNOME VARCHAR2(25),
        UNIQUE (NOME, COGNOME)
       );

In questo caso potremmo essere sicuri, come vuole la teoria dei database relazionali, che tutte le persone ‘presenti’ nella tabella appaiono una sola volta. Quanto affermato non è esattamente vero: ricordiamo che il valore NULL è un ‘nulla’ virtuale ogni volta diverso. Cosa accadrebbe nella tabella così creata se inserissimo due volte i dati di Mario Rossi, ma senza inserire il nome? Accadrebbe che il sistema non riconosce i due Rossi come la stessa persona, in quanto il valore del campo NOME risulterebbe diverso. Quindi per ovviare anche a questa eventualità dobbiamo utilizzare una tabella creata in questo modo:

CREATE TABLE PERSONE
    (
      ID_PERSONA NUMBER PRIMARY KEY,
      NOME VARCHAR2(25) NOT NULL,
      COGNOME VARCHAR2(25) NOT NULL,
      UNIQUE (NOME, COGNOME)
      );

Il vincolo UNIQUE è utilizzato non solo per far fronte a ipotetiche situazioni tanto complesse, ma anche semplicemente a non permettere di non inserire un dato fondamentale per l’entità che si sta rappresentando.
La parola chiave UNIQUE non è implementata da Access.

DEFAULT

Questa parola chiave, che non rappresenta un vincolo, ci permette di assegnare un valore ad un attributo quando viene inserita una riga senza che sia specificato un valore per l’attributo stesso. Cercheremo di capire con un esempio quanto affermato:

Vogliamo che nel campo NUMERO_FIGLI della tabella DIPENDENTI che creeremo venga inserito automaticamente il valore 0 se non specificato diversamente. Vediamo lo schema sintattico di una possibile create per assolvere allo scopo:

CREATE TABLE DIPENDENTI
      (
        . . . . . . . . . . . . . . . . . . . . . .
        . . . . . . . . . . . . . . . . . . . . . .
        NOME VARCHAR2(25),
        COGNOME VARCHAR2(25),
        NUMERO_FIGLI NUMBER DEFAULT 0,
        . . . . . . . . . . . . . . . . . . . . . .
        );

Nella ipotetica tabella DIPENDENTI quando si conferma l’inserimento della riga senza aver inserito alcun valore nel campo NUMERO_FIGLI a tale campo verrà inserito automaticamente il valore impostato come default.

CHECK

Questa parola chiave ci permette di verificare che il valore che si sta inserendo nel campo rientri in un certo range o abbia un determinato formato. In caso contrario il valore in questione non verrà accettato. Vengono riportate come esempio alcune CREATE TABLE testate su Oracle:

CREATE TABLE componenti_gruppi (
       rif_gruppo          NUMBER,
       componente       CHAR(8),
       capogruppo        NUMBER CHECK (capogruppo IN (0,1)), /* Permette l’inserimento dei soli valori 0 o 1 */
       dal                       DATE NOT NULL
        );

CREATE TABLE indirizzi_elettronici (
           rif_indirizzo           NUMBER,
           tipo                       CHAR(1) CHECK (tipo in ('E','W')), /* Permette l’inserimento dei soli caratteri ‘E’ o ‘W’ */
           address                VARCHAR2(80)
CREATE TABLE classi_di_rischio0 (
           id_classe              NUMBER(3) CHECK (id_classe >= 900), /* Permette l’inserimento solo se è verificata la condizione */
           descrizione            VARCHAR2(100),
           stato                      CHAR(1) CHECK (stato IN ('A','D'))
);

CREATE TABLE tipi_di_rischio0 (
     id_classe                     NUMBER(3) NOT NULL,
     id_tipo_rischio             NUMBER(3) CHECK (id_tipo_rischio >= 1 AND id_tipo_rischio < 900),
     descrizione                  VARCHAR2(100),
     premio_minimo            NUMBER,
     durata_minima             NUMBER,
     proroga_minima           NUMBER,
     tasso_grandi_clienti     NUMBER,
     tasso_clienti_medi       NUMBER,
     tasso_clienti_base       NUMBER,
     coefficienti_autonomia NUMBER,
     numero_appendice      NUMBER,
     stato_rischio                 CHAR(2) CHECK (stato_rischio IN ('RO','RN','RS')),
     stato                             CHAR(1) CHECK (stato IN ('A','D')),
     dal                                DATE NOT NULL
);

La parola chiave CHECK non è implementata da Access.

Creare una tabella da una già esistente

Se per ipotesi dobbiamo creare una tabella che dovrà contenere i dati duplicati di un’altra tabella già esistente si può usare l’istruzione CREATE TABLE con una particolare sintassi; vediamo lo schema:

CREATE TABLE nomeTabella (campo1, campo2, . . .)
AS (SELECT Campo1, Campo2, . . .
        FROM vecchiaTabella
        WHERE . . . );

questa create così strutturata consente di creare una nuova tabella caricando i dati dalla vecchia nei corrispondenti campi della nuova. La corrispondenza dei campi avviene per posizione, cioè il primo campo che viene indicato dopo ‘CREATE TABLE ( . . . ‘ corrisponde con il primo campo che viene indicato dopo ‘AS (SELECT . . . ‘ , stesso discorso vale per il secondo campo, per il terzo e così via. A conferma di quanto detto va fatto notare che i nomi dei campi della nuova tabella e quelli della vecchia non devono necessariamente corrispondere, ovvero essere uguali. Saranno uguali invece, i tipi di dato dei campi tra nuova e vecchia tabella, infatti in questa particolare create non appare nessuna dichiarazione al tipo di dati dei campi. La clausola WHERE infine ci permette di selezionare e quindi di caricare nella nuova tabella, solo le righe che soddisfano una particolare condizione.
Questa sintassi non è implementata da access.

ALTER TABLE

Molte volte nasce la necessità di modificare una tabella già esistente modificando il tipo e il nome di una colonna o aggiungendone una nuova. Questo accade principalmente per due motivi: o in fase di progettazione non si è tenuto conto di tutti i campi necessari o la realtà che si sta rappresentando è cambiata a tal punto da rendere necessaria una o più modifiche su quella particolare tabella.
Si utilizza ALTER TABLE anche in fase di implementazione o progettazione del database, per semplificare il lavoro. Ad esempio, prima si creano tutte le tabelle per mezzo di uno script, poi per mezzo di un altro script vi si inseriscono tutte le chiavi primarie e poi le chiavi esterne. In questo modo si avrà una lista separata di tutte le chiavi primarie (script che inserisce le chiavi primarie), una lista di tutte le chiavi esterne (script che aggiunge le chiavi esterne) e così via. Vediamo alcuni esempi:

Aggiungere un campo:

ALTER TABLE DIRIGENTI
ADD NOTE VARCHAR2 (2000);

Questa alter table aggiunge un capo di nome NOTE e di tipo VACHAR2 con lunghezza massima di 2000 caratteri alla tabella DIRIGENTI. Access non implementa il tipo dati VARCHAR2.

Modificare il tipo ad un campo:

Supponiamo ora che alla tabella DIRIGENTI vogliamo modificare il tipo del campo TITOLO_STUDIO che è un CHAR(30) in CHAR(60):

ALTER TABLE DIRIGENTI
MODIFY TITOLO_STUDIO CHAR(60);         /* MODIFY non è accettato da Access */

Modificare l’opzione NOT NULL:

L’opzione MODIFY può anche essere utilizzata per cambiare l’opzione di un campo da NOT NULL a NULL; vediamo un esempio:

Supponiamo che vogliamo cambiare l’opzione del campo ID_DIRIGENTE da NOT NULL a NULL

ALTER TABLE DIRIGENTI
MODIFY ID_DIRIGENTE CHAR(3) NULL;       /* MODIFY non è accettato da Access */

è possibile anche effettuare l’operazione inversa, ovvero modificare l’opzione NULL di un campo in NOT NULL a patto però che in quella colonna non appaiono valori nulli.

Aggiungere un CHECK:

Viene mostrata la tabella CREDITI attraverso la sua CREATE ; come si può vedere non esiste nessun CHECK:

CREATE TABLE crediti (
     id_credito       CHAR(8),
     rif_cliente       CHAR(8),
     tipo_rischio     NUMBER(3),
     ammontare     NUMBER NOT NULL,
     residuo           NUMBER NOT NULL
);

Per aggiungere un CHECK si utilizza la seguente sintassi:

ALTER TABLE crediti
     ADD CONSTRAINT ck_crediti
     CHECK (residuo <= ammontare);      /* Questa sintassi è stata testata solo su Oracle */

una volta lanciata questa ALTER TABLE non sarà più possibile inserire un valore numerico nel campo residuo maggiore del valore presente nel campo ammontare o inserire un valore numerico nel campo ammontare minore del valore presente nel campo residuo.

Modificare un CHECK:

Per meglio comprendere come modificare un CHECK verrà mostrato un esempio. Abbiamo la necessità di creare una tabella STUDENTI strutturata nel seguente modo:

CREATE TABLE studenti (
      nome varchar(20),
      cognome varchar(20),
      livello_corso CHAR(2) CHECK (livello_corso IN ('1°', '2°', '3°'))
);
/* Questa sintassi è testata solo su Oracle */

Come si può vedere nella tabella già esiste un controllo CHECK . Ci accorgiamo però, solo dopo aver creato la tabella, che il suo CHECK deve essere modificato. Per modificare un CHECK si utilizza la stessa sintassi utilizzata per aggiungere un CHECK:

ALTER TABLE STUDENTI
      ADD CONSTRAINT ck_livello_corso
      CHECK (livello_corso IN ('1°', '2°'));
/* Questa sintassi è stata testata solo su Oracle */

Ancora una volta ci siamo accorti di aver sbagliato il nostro CHECK e dunque dovremo rimodificarlo. Per modificare un CHECK già modificato dovremo prima distruggere lo specifico CHECK; vediamo come:

ALTER TABLE STUDENTI
           DROP CONSTRAINT ck_livello_corso;
/* Questa sintassi è stata provata solo su Oracle */

A questo punto non essendoci più alcun CHECK potremmo aggiungerne un altro.

Inserire chiavi primarie

A volte nasce la necessità di dichiarare un particolare campo di una tabella, chiave primaria. Vediamo quali sono le sintassi per ottenere ciò:

ALTER TABLE nome_tabella
      ADD CONSTRAINT nome_chiave PRIMARY KEY (nome_campo);

Può presentarsi il caso che la chiave primaria debba esse di tipo composto:

ALTER TABLE nome_tabella
      ADD CONSTRAINT nome_chiave PRIMARY KEY
                                                  (nome_campo_a, nome_campo_b, nome_campo_c );

Aggiungere chiavi esterne

La necessità di aggiungere chiavi esterne è abbastanza frequente. Vediamo quali sono le sintassi:

ALTER TABLE nome_tabella
      ADD CONSTRAINT nome_chiave FOREIGN KEY (nome_campo)
      REFERENCES nome_tabella_master;

Alcuni DBMS come Access non supportano l’uso della clausola MODIFY ; altri DBMS implementano, per l’istruzione ALTER TABLE, sintassi diverse o ulteriori estensioni; quindi si consiglia di consultare la documentazione del sistema che si sta utilizzando per conoscere la sintassi esatta.

DROP TABLE

Questa istruzione serve per eliminare completamente una tabella dal database. Se a questa tabella ci sono associati degli indici o delle view essi verranno eliminati insieme alla tabella.
Ci rendiamo conto che questa istruzione è particolarmente pericolosa. È consigliabile usarla solo per tabelle temporanee, ovvero per quelle tabelle non importanti per il database e create per un uso temporaneo e marginale. Vediamo alcuni esempi:

Vogliamo eliminare la tabella DIRIGENTI dal nostro database.

DROP TABLE DIRIGENTI;

Il comando non chiede conferma dell’utente ed elimina definitivamente la tabella. Quindi bisogna far attenzione a eliminare la tabella giusta; infatti è possibile trovare in database molto grandi tabelle con lo stesso nome ma comunque appartenenti ad utenti diversi. In questo caso è bene indicare prima del nome della tabella il nome dell’utente; facciamo finta che il nome dell’utente della tabella DIRIGENTI sia Arturo:

DROP TABLE Arturo.DIRIGENTI;

È importante utilizzare sempre questa sintassi per non correre il grave rischio di cancellare la tabella sbagliata.

INDICE

pag. 13 di 20