L'istruzione CREATE

Per creare tabelle SQL fornisce l’istruzione CREATE TABLE. Nel momento in cui si crea una tabella, si devono definire i suoi attributi per ognuno dei quali è necessario indicare un tipo. Vanno inoltre definite la chiave primaria e le eventuali chiavi esterne e vanno infine indicati eventuali vincoli di dominio.

CREATE TABLE

Nella forma più semplice, nell’istruzione CREATE TABLE si indicano:

• nome della tabella e
• lista dei nomi e tipo delle colonne.

CREATE TABLE Student(
    id INT,
    first_name VARCHAR(255),
    second_name VARCHAR(255)
    birth_date DATE
);

L’esempio crea una tabella di nome Student con 4 colonne: 2 stringhe di lunghezza massima 255, una intera ed una indicante una data.

Normalmente i DBMS rifiutano, generando un errore, la creazione di una tabella con lo stesso nome di una tabella già esistente nel database. In certi casi, vogliamo che il DBMS crei la tabella se non esista, ma che non faccia nulla se è già presente (anziché generare un errore). SQL prevede una variante per questo scopo.

CREATE TABLE IF NOT EXISTS Student(
    ...
);

Tipi di dati SQL

Ogni attributo di una relazione deve avere un tipo ed un dominio associato, in SQL sono disponibili numerosi tipi (ben più di quelli che discuteremo qui) ad ognuno dei quali è associato un dominio. Inoltre il dominio può essere ristretto inserendo vincoli sui valori ammessi.

Tipi numerici

• INT: Questo tipo di dati rappresenta numeri interi, ovvero numeri senza parte frazionaria. Può essere utilizzato per memorizzare valori come 1, 10, -5, ecc.
• DECIMAL: Il tipo di dati DECIMAL, noto anche come NUMERIC, è utilizzato per memorizzare numeri decimali con una precisione e una scala specificate. È adatto per rappresentare valori monetari o altre quantità precise.

Tipi stringa

• CHAR(M): Il tipo di dati CHAR rappresenta una stringa di lunghezza fissa di M caratteri. La stringa viene spesso riempita con spazi bianchi per raggiungere la lunghezza specificata.
• VARCHAR(M): Il tipo di dati VARCHAR rappresenta una stringa di lunghezza variabile con una lunghezza massima di M caratteri. Non riempie la stringa con spazi bianchi inutili, quindi è più efficiente per le stringhe più corte.
• TEXT(M): Il tipo di dati TEXT rappresenta una stringa di lunghezza variabile con una lunghezza massima opzionale di M caratteri. È adatto per memorizzare testi lunghi, come descrizioni o note.
• DATE: Il tipo di dati DATE rappresenta una data, inclusi giorno, mese e anno.
• TIME: Il tipo di dati TIME rappresenta un orario del giorno, inclusi ore, minuti, secondi e frazioni di secondo.
• DATETIME: Il tipo di dati DATETIME rappresenta una combinazione di data e orario, inclusi giorno, mese, anno, ore, minuti, secondi e frazioni di secondo.

Tipi Large Object

• BLOB: Il tipo di dati BLOB, che sta per “Binary Large Object,” è utilizzato per memorizzare dati binari di grandi dimensioni, come immagini, file audio o file binari. È adatto per contenuti non testuali e offre una capacità di memorizzazione estesa.

Vediamo un esempio di utilizzo di alcuni dei tipi sopra elencati. Riprendiamo la tabella Student definita sopra, aggiungendo alcuni campi e creano anche una tabella Teacher.

-- Definizione della tabella Student
CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    birth_date DATE,
    registration_date DATETIME,
    home_address TEXT,
    email VARCHAR(100)
);

-- Definizione della tabella Teacher
CREATE TABLE Teacher (
    teacher_id INT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    birth_date DATE,
    hire_date DATE,
    email VARCHAR(100),
    phone_number VARCHAR(15)
);

AUTO INCREMENT

Spesso si creano chiavi primarie sotto forma di valori interi, in molti DBMS (ad esempio MariaDB) è possibile indicare che un attributo intero deve assumere valori consecutivi e distinti, AUTO_INCREMENT permette di fare questo. Nell’esempio sotto, le chiavi student_id e teacher_id avranno valori interi via via crescenti: 1,2,3,....

CREATE TABLE Student (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    -- ...
);

CREATE TABLE Teacher (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    -- ...
);

CREATE TABLE Student (
    -- Errore (non segnalato da DB Browser!)
    student_id INT AUTO_INCREMENT PRIMARY KEY,

    -- Ok, ma non usa ROWID
    student_id INT AUTOINCREMENT PRIMARY KEY,
    
    -- Ok, usa ROWID come chiave primaria (scelta da preferire)
    student_id INT PRIMARY KEY,
);

Chiavi

Chiave primaria

Nell’esempio presentato sopra sono stati definiti di attributi (uno per tabella) come PRIMARY KEY, in SQL è previsto che ogni tabella creata abbia una chive primaria che, nel caso in cui si tratti di un singolo attributo, può essere indicata dopo la dichiarazione del tipo.

Quando la chiave primaria è formata da più attributi, non è possibile utilizzare la stessa sintassi, in altre parole la seguente istruzione SQL non è valida

CREATE TABLE ClassTeacher (
    class_id INT PRIMARY KEY,
    teacher_id INT PRIMARY KEY,
    ...
)

Per indicare una chiave primaria con più attributi la si dichiara dopo aver elencato tutti gli attributi

CREATE TABLE ClassTeacher (
    teacher_id INT,
    class_id INT,
    PRIMARY KEY (teacher_id, class_id),
    ...
);

Chiave esterna

Nell’esempio sopra, la tabella ClassTeacher mette in collegamento insegnanti e classi in cui questi insegna. Si è visto che in un database relazione questo collegamento avviene mediante chiavi esterne. In SQL è possibile dichiarare le chiavi esterne indicando quale/i attributo/i sono chiavi esterne e a che attributo si riferiscono.

Per l’esempio sopra avremo quindi (ipotizzando di avere una tabelle Class)

CREATE TABLE ClassTeacher (
    teacher_id INT,
    class_id INT,
    year VARCHAR(4),
    PRIMARY KEY (teacher_id, class_id),
    FOREIGN KEY (teacher_id) REFERENCES Teacher(id),
    FOREIGN KEY (class_id) REFERENCES Class(id)
);

Vincoli di dominio

Un vincolo di dominio è una regola che specifica il tipo di dati o il range di valori che un attributo può contenere. Questo vincolo definisce le restrizioni sui valori che possono essere inseriti in un attributo. Ad esempio, è possibile definire un vincolo di dominio che limita un attributo a contenere solo valori interi positivi o stringhe di lunghezza massima di 50 caratteri.

I vincoli di dominio sono utili per garantire l’integrità dei dati nel database e per evitare l’inserimento di dati non validi.

Ecco un esempio di come definire un vincolo di dominio in SQL:

CREATE TABLE Class (
    class_id INT PRIMARY KEY,
    level INT CHECK (level >= 1 AND level <= 5)
    section VARCHAR(8) NOT NULL,
    location VARCHAR(16),
);

NUT NULL e UNIQUE

Esistono due vincoli di dominio che possono essere espressi in SQL:

• NOT NULL impone che un attributo non possa avere il valore NULL (non indicando questo vincolo, i valori NULL sono ammessi di default).
• UNIQUE impone che i valori per un attributo siano unici nella relazione. In altre parole non possono esistere due tuple con lo stesso valore di attributi che sono identificati con UNIQUE.

Possiamo, ad esempio, modificare la definizione della tabella Teacher nel seguente modo

CREATE TABLE Teacher (
    teacher_id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    birth_date DATE,
    hire_date DATE,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(15) UNIQUE
);

Notiamo che UNIQUE e NOT NULL possono essere usati insieme, infatti il vincolo di UNIQUE non si applica ai valori NULL (possono esistere più tuple con valore NULL per un attributo UNIQUE).

Dall’esempio capiamo che per una tuple di Teacher devono essere specificati (NOT NULL) nome, cognome e email. Inoltre l’email deve essere unica (quindi l’email potrebbe essere utilizzata come chiave primaria in quanto chiave candidata)

È possibile esprimere il vincolo UNIQUE su una combinazione di attributi. Ad esempio per essere sicuri che nella relazione Class esista un’unica tupla per ogni classe possiamo aggiungere il seguente vincolo

CREATE TABLE Class (
    -- Come sopra
    UNIQUE(level, section);
);

Ad esempio level=1 e section=A può esistere una sola volta nella relazione, tuttavia può esistere anche una tupla con level=1 e section=B, come anche la tupla con level=2 e section=A. Notare che se noi avessimo usato la seguente sintassi:

CREATE TABLE Class (
    -- ...
    level INT UNIQUE CHECK (level >= 1 AND level <= 5)
    section UNIQUE VARCHAR(8) NOT NULL,
    -- ...
);

solo una tupla potrebbe avere level=1 e solo una tupla potrebbe avere section=A.

Valore di default

La parola chiave DEFAULT in SQL è utilizzata per assegnare un valore predefinito a una colonna quando un nuovo record viene inserito nella tabella e nessun valore specifico è fornito per quella colonna. In altre parole, se non si specifica un valore per la colonna durante un’operazione di INSERT, il valore predefinito specificato con DEFAULT verrà utilizzato automaticamente.

CREATE TABLE Class (
    class_id INT PRIMARY KEY,
    level INT DEFAULT 1 CHECK (level >= 1 AND level <= 5),
    section VARCHAR(8) DEFAULT 'A' NOT NULL,
    location VARCHAR(16)
);

Quando un attributo non è dichiarato NOT NULL e non viene specificato un valore con DEFAULT, le tuple che non specificano il valore dell’attributo in fase di inserimento prendono il valore NULL.

Vincoli di integrità referenziale

L’opzione ON DELETE nelle chiavi esterne di SQL è utilizzata per specificare il comportamento desiderato quando viene eliminata una riga nella tabella padre (riferita dalla chiave esterna). Ci sono diverse strategie previste le più importanti sono:

• ON DELETE CASCADE: Quando una tupla nella tabella riferita, tutte le tuple che la riferiscono vengono automaticamente eliminate. Questa opzione garantisce che non ci siano record orfani.

• ON DELETE SET NULL: Quando una tupla nella tabella riferita, il valore nella colonna corrispondente della tabella figlio viene impostato su NULL. Questa opzione può essere utile quando si desidera consentire record orfani, ma si vuole mantenere la coerenza dei dati, ovviamente non è possibile utilizzare questa opzione se la chiave esterna è definita NOT NULL.

• ON DELETE RESTRICT: Questa opzione impedisce l’eliminazione di una riga nella tabella padre se esistono record corrispondenti nella tabella figlio.

Nel caso della tabella ClassTeacher, se si utilizza ON DELETE CASCADE per la chiave esterna teacher_id, quando un insegnante viene eliminato dalla tabella Teacher, tutte le associazioni nella tabella ClassTeacher relative a quel professore verranno automaticamente eliminate, garantendo che non ci siano riferimenti orfani nella tabella ClassTeacher.

CREATE TABLE ClassTeacher (
    teacher_id INT,
    class_id INT,
    year VARCHAR(4),
    PRIMARY KEY (teacher_id, class_id),
    FOREIGN KEY (teacher_id) REFERENCES Teacher(id) ON DELETE CASCADE,
    FOREIGN KEY (class_id) REFERENCES Class(id) ON DELETE CASCADE
);

CREATE SCHEMA

L’istruzione CREATE SCHEMA viene utilizzata per creare uno schema nel database. Uno schema è un contenitore logico che può contenere tabelle, viste, procedure, funzioni e altri oggetti del database. Gli schemi vengono utilizzati per organizzare e separare gli oggetti del database in gruppi logici.

Ecco un esempio di come utilizzare l’istruzione CREATE SCHEMA:

CREATE SCHEMA my_schema;

Domande ed Esercizi

Rispondi alle seguenti domande motivando la risposta.

1. Quando si progetta una nuova tabella utilizzando l’istruzione CREATE TABLE, quali sono alcune delle considerazioni importanti da tenere a mente per definire gli attributi, le chiavi primarie e le chiavi esterne in modo efficace? Come queste decisioni possono influenzare le prestazioni e la struttura del database?
2. Durante la creazione di una tabella tramite CREATE TABLE, quali tipi di vincoli di dominio e restrizioni possono essere applicati agli attributi? In che modo l’uso di vincoli come NOT NULL, UNIQUE, CHECK e DEFAULT può contribuire a garantire l’integrità dei dati e prevenire errori nel database?

Riferimenti

• CREATE TABLE (W3School)
• Tipi dati (W3School)