Vincoli di integrità in SQL: guida con esempi

Immagina un database ospedaliero in cui le allergie dei pazienti non possono essere lasciate vuote o un sistema finanziario in cui gli importi delle transazioni devono essere numeri positivi. In questi scenari, e in tanti altri, ci affidiamo ai vincoli di integrità per mantenere i dati accurati, coerenti e affidabili. 

In SQL, i vincoli di integrità sono regole che imponiamo alle tabelle del database per garantire la qualità dei dati. Aiutano a prevenire errori, far rispettare le regole di business e assicurare che i nostri dati riflettano le entità e le relazioni del mondo reale che rappresentano.

In questo articolo approfondiremo i tipi essenziali di vincoli di integrità in SQL, con spiegazioni chiare ed esempi pratici per illustrarne l'uso in un database PostgreSQL. Anche se useremo la sintassi di PostgreSQL, i concetti si applicano facilmente ad altri dialetti SQL.

Se vuoi saperne di più su SQL, dai un'occhiata a questo elenco di corsi SQL.

Cosa sono i vincoli di integrità in SQL?

Considera uno scenario in cui abbiamo una tabella che memorizza le informazioni degli utenti per un'applicazione web. Alcuni dati, come l'età dell'utente, potrebbero essere opzionali perché non impediscono l'accesso all'applicazione. Tuttavia, avere una password per ogni utente è essenziale per l'accesso. Per gestire questo aspetto, implementeremmo un vincolo di integrità sulla colonna della password della nostra tabella utenti per garantire che ogni record in questa tabella includa una password.

In sostanza, i vincoli di integrità sono fondamentali per:

• Prevenire dati mancanti.
• Assicurare che tutti i dati rispettino i tipi e gli intervalli di valori attesi.
• Mantenere collegamenti corretti tra dati in tabelle diverse.

In questo articolo esploreremo i seguenti vincoli di integrità essenziali in SQL:

• PRIMARY KEY: Identifica in modo univoco ogni record in una tabella.
• NOT NULL: Garantisce che una colonna non possa contenere valori NULL.
• UNIQUE: Garantisce che tutti i valori in una colonna o in un gruppo di colonne siano univoci.
• DEFAULT: Fornisce un valore predefinito per una colonna quando non ne viene specificato uno.
• CHECK: Garantisce che tutti i valori in una colonna soddisfino una condizione specifica.
• FOREIGN KEY: Stabilisce relazioni tra tabelle facendo riferimento a una chiave primaria in un'altra tabella.

Caso di studio: database universitario

Consideriamo un database relazionale per un'università. Questo database contiene tre tabelle: students, courses ed enrollments.

Tabella Students

La tabella students contiene le informazioni su tutti gli studenti dell'università.

• student_id: L'identificatore dello studente
• first_name: Il nome dello studente.
• last_name: Il cognome dello studente.
• email: L'indirizzo email dello studente.
• major: L'indirizzo di studi dello studente.
• enrollment_year: L'anno in cui lo studente si è immatricolato.

Tabella Courses

La tabella courses contiene le informazioni sui corsi disponibili all'università.

• course_id: L'identificatore del corso.
• course_name: Il nome del corso.
• department: Il dipartimento del corso.

Tabella Enrollments

La tabella enrollments memorizza le informazioni su quali studenti sono iscritti a quali corsi.

• student_id: L'identificatore dello studente iscritto al corso.
• course_id: L'identificatore del corso.
• year: L'anno di iscrizione.
• grade: Il voto dello studente in questo corso.
• is_passing_grade: Un booleano che indica se il voto è sufficiente.

Nel corso dell'articolo useremo questo database di esempio e mostreremo diversi modi per imporre l'integrità dei dati. Useremo la sintassi di PostgreSQL nelle query. Tuttavia, i concetti si applicano facilmente ad altre varianti di SQL.

Vincolo PRIMARY KEY

L'università vuole identificare ogni studente in modo univoco. Usare nome e cognome a questo scopo non è consigliabile a causa della possibile omonimia tra studenti. Analogamente, affidarsi agli indirizzi email non è ideale perché gli studenti potrebbero cambiarli.

La soluzione comune per garantire l'identificazione univoca è assegnare a ogni studente un identificatore univoco, che memorizziamo nella colonna student_id. Possiamo applicare un vincolo PRIMARY KEY alla colonna student_id per garantire che ogni studente abbia un identificatore univoco.

Questo vincolo è definito nel comando CREATE TABLE, dopo la specifica del tipo di dato della colonna:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    major TEXT,
    enrollment_year INT
);

La query sopra crea la tabella degli studenti con le sei colonne menzionate.

Il vincolo PRIMARY KEY garantisce che:

1. Ogni studente abbia un student_id.
2. Ogni student_id sia univoco.

Vincolo PRIMARY KEY su più colonne

In alcuni scenari dobbiamo utilizzare più colonne per identificare in modo univoco ogni riga. Prendiamo, ad esempio, la tabella enrollments. Uno studente può iscriversi a più corsi, con il risultato di diverse righe che condividono lo stesso student_id. Allo stesso modo, un corso può avere più studenti iscritti, portando a numerose righe con lo stesso course_id.

Dato che nessun singolo campo può identificare univocamente una riga, ogni record di iscrizione è determinato da una combinazione di student_id, course_id e year.

Quando sono coinvolte più colonne, il vincolo PRIMARY KEY viene specificato alla fine del comando CREATE TABLE.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT,
    grade INT,
    is_passing_grade BOOLEAN,
    PRIMARY KEY (student_id, course_id, year)
);

Vincoli di integrità dopo la creazione della tabella

Ci sono due modi per aggiungere vincoli di integrità. Abbiamo appena visto come farlo al momento della creazione della tabella.

Immagina che la tabella esista già, ma ti sia dimenticato di specificare il vincolo PRIMARY KEY. Puoi anche definirlo dopo la creazione della tabella usando il comando ALTER TABLE, così:

ALTER TABLE enrollments
ADD CONSTRAINT enroll_pk 
PRIMARY KEY (student_id, course_id, year);

Nella query ALTER TABLE abbiamo chiamato il vincolo enroll_pk (sta per enrollment primary key). Questo nome può essere qualsiasi identificatore a tua scelta, ma è consigliabile sceglierne uno che esprima in modo conciso lo scopo del vincolo.

È buona prassi assegnare un nome ai vincoli di integrità perché offre diversi vantaggi:

• Permette un riferimento più semplice, soprattutto quando in futuro dovrai modificare o eliminare il vincolo.
• Supporta la gestione e l'organizzazione dei vincoli, in particolare nei database con un gran numero di vincoli.

Vincolo NOT NULL

L'università vuole assicurarsi che il nome, il cognome e l'email di ogni studente siano registrati nel database. Non vuole che il personale dimentichi accidentalmente di inserire uno qualsiasi di questi campi.

Per ovviare a questo, possiamo usare vincoli NOT NULL su queste tre colonne quando creiamo la tabella:

CREATE TABLE students (
    student_id INT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL,
    major TEXT,
    enrollment_year INT
);

La query sopra usa i vincoli NOT NULL per garantire che le colonne first_name, last_name ed email non possano avere valori NULL (non definiti).

Aggiungere un vincolo NOT NULL a una tabella esistente si può fare con il comando ALTER TABLE. La sintassi per aggiungere vincoli NOT NULL è la seguente:

ALTER TABLE students
ALTER COLUMN first_name SET NOT NULL,
ALTER COLUMN last_name SET NOT NULL,
ALTER COLUMN email SET NOT NULL;

Vincolo UNIQUE

Gli indirizzi email sono intrinsecamente univoci per ogni individuo. Nei casi in cui siamo sicuri che un campo non debba mai avere valori duplicati, è buona pratica far rispettare questa regola a livello di database. Questo aiuta a prevenire errori e a garantire l'integrità dei dati.

L'aggiunta di questo vincolo si fa allo stesso modo degli altri.

CREATE TABLE students (
    ...
    email TEXT UNIQUE,
    ...
);

Tuttavia, nel nostro caso vogliamo anche imporre il vincolo NOT NULL. Possiamo applicare più vincoli sulla stessa colonna separandoli con uno spazio: 

CREATE TABLE students (
    ...
    email TEXT UNIQUE NOT NULL,
    ...
);

Nota che l'ordine non importa: avremmo potuto usare anche NOT NULL UNIQUE.

Aggiungere un vincolo UNIQUE a una tabella esistente si fa così:

ALTER TABLE students 
ADD CONSTRAINT unique_emails UNIQUE (email);

Vincolo UNIQUE su più colonne

Supponiamo di voler garantire che il nome del corso sia univoco per ogni dipartimento nella tabella courses. In questo caso, le colonne course_name e department insieme devono essere univoche.

Quando sono coinvolte più colonne, il vincolo viene aggiunto alla fine del comando CREATE TABLE:

CREATE TABLE courses (
    course_id INT,
    course_name TEXT,
    department TEXT,
    UNIQUE (course_name, department)
);

In alternativa, possiamo aggiungere il vincolo modificando una tabella esistente. In questo caso, forniamo una tupla con i nomi delle colonne:

ALTER TABLE courses 
ADD CONSTRAINT unique_course_name_department 
UNIQUE (course_name, department);

NOT NULL UNIQUE vs vincoli PRIMARY KEY 

Abbiamo visto che un vincolo PRIMARY KEY impone sia l'unicità sia l'assenza di valori mancanti. Potresti quindi chiederti qual è la differenza tra:

course_id INT PRIMARY KEY

course_id INT UNIQUE NOT NULL

La differenza tra NOT NULL UNIQUE e PRIMARY KEY in una tabella riguarda lo scopo e l'uso previsti. 

Sebbene entrambi impongano unicità e non nullabilità su una o più colonne, una tabella può avere una sola PRIMARY KEY, pensata per identificare in modo univoco ogni record nella tabella.

D'altra parte, la combinazione di vincoli NOT NULL UNIQUE può essere applicata a ulteriori colonne per imporre un valore univoco per riga, senza valori nulli, al servizio dell'integrità dei dati per specifiche regole di business. Una tabella può avere un numero qualsiasi di vincoli NOT NULL UNIQUE.

La coesistenza di entrambi consente maggiore flessibilità nella progettazione del database, permettendo più modi per imporre unicità e integrità dei dati, distinguendo tra l'identificatore primario di un record e altri attributi importanti e univoci all'interno di una tabella.

Vincolo DEFAULT

Gli studenti possono aver bisogno di un po' di tempo dopo l'immatricolazione per scegliere il proprio indirizzo di studi. L'università vorrebbe che il valore della colonna major fosse la stringa “Undeclared” per gli studenti che non hanno ancora scelto.

Per farlo, possiamo impostare un valore predefinito per questa colonna usando il vincolo DEFAULT. Possiamo modificare la tabella students così:

ALTER TABLE students
ALTER COLUMN major SET DEFAULT 'Undeclared';

Se invece vogliamo impostare un vincolo DEFAULT al momento della creazione della tabella, possiamo dichiararlo dopo il tipo di dato della colonna:

CREATE TABLE students (
    ...
    major TEXT DEFAULT 'Undeclared',
    ...
);

Vincolo CHECK

In questa specifica università, i voti vanno da 0 a 100. Senza alcun vincolo, la colonna grade della tabella enrollment accetta qualsiasi valore intero. Possiamo risolvere usando un vincolo CHECK per imporre che i valori siano compresi tra 0 e 100.

ALTER TABLE enrollments
ADD CONSTRAINT grade_range CHECK (grade BETWEEN 0 AND 100);

In generale, i vincoli CHECK ci permettono di validare condizioni specifiche che vogliamo che i dati soddisfino. Sono importanti per garantire coerenza e integrità dei dati.

Un vincolo CHECK può coinvolgere più di una colonna. Usiamolo per assicurarci che grade e is_passing_grade abbiano valori coerenti. Supponiamo che un voto sia sufficiente se è almeno 60. Possiamo allora garantire che is_passing_grade sia TRUE se e solo se il voto è almeno 60. Facciamolo nella creazione della tabella per mostrare come si dichiarano i vincoli CHECK nel comando CREATE TABLE:

CREATE TABLE enrollments (
    ...
    grade INT,
    is_passing_grade BOOLEAN,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    )
);

C'è un problema con il vincolo sopra. Quando uno studente si iscrive a un corso, non avrà ancora un voto. Dovremmo quindi consentire valori NULL per il voto. Facendolo, dobbiamo anche aggiornare il vincolo sul voto sufficiente affinché sia NULL quando il voto è ancora indefinito. Ecco come aggiornare il vincolo per tenerne conto:

CREATE TABLE enrollments (
    ...    
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    ...
);

Nota che sia sulla colonna grade sia su is_passing_grade abbiamo aggiunto NULL nel tipo di dato e come valore predefinito. Lo scopo è semplicemente quello di aumentare la leggibilità.

Vediamo ora quali condizioni possiamo imporre con un vincolo CHECK.

Condizioni di intervallo

Possiamo assicurarci che i valori in una colonna rientrino in un intervallo specifico.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name BETWEEN min_value AND max_value);

Condizioni di elenco

Possiamo convalidare che il valore di una colonna corrisponda a uno in un elenco di valori specifici.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name IN ('Value1', 'Value2', 'Value3'));

Condizioni di confronto

Possiamo confrontare i valori in una colonna per garantire che soddisfino una condizione specificata (maggiore di, minore di, ecc.).

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name > some_value);

Condizioni di pattern matching

Possiamo usare il pattern matching (ad esempio con LIKE o SIMILAR TO) per convalidare dati testuali.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name LIKE 'pattern');

Condizioni logiche

Possiamo consentire condizioni multiple usando operatori logici (AND, OR, NOT).

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (condition1 AND condition2 OR condition3);

Condizioni composite

Applica una condizione su più colonne.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column1 + column2 < some_value);

Vincolo FOREIGN KEY

I vincoli di chiave esterna vengono usati per collegare tra loro le colonne di due tabelle, garantendo l'integrità referenziale dei dati. In sostanza, una chiave esterna in una tabella punta a una chiave primaria in un'altra tabella, indicando che le righe di queste due tabelle sono correlate. Questo assicura che non si possa avere una riga in una tabella con una chiave esterna che non corrisponde a nessuna riga nella tabella collegata con la chiave primaria.

Nel nostro esempio, ogni record nella tabella enrollments fa riferimento a uno studente e a un corso rispettivamente tramite le colonne student_id e course_id. Senza alcun vincolo, nulla garantisce che i valori di questi identificatori nella tabella enrollments corrispondano a voci esistenti nelle tabelle students e courses.

Ecco come lo garantiamo quando creiamo la tabella enrollments:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    ...
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Nota che, perché queste siano chiavi esterne, devono essere chiavi primarie rispettivamente nelle tabelle students e courses.

Come per gli altri vincoli, potremmo anche dichiararli dopo la creazione della tabella usando il comando ALTER TABLE:

ALTER TABLE enrollments
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id)
REFERENCES students(student_id);

ALTER TABLE enrollments
ADD CONSTRAINT fk_course_id
FOREIGN KEY (course_id)
REFERENCES courses(course_id);

Mettere tutto insieme

In questo articolo abbiamo illustrato diversi vincoli di integrità e come usarli per migliorare un database universitario. Ecco la versione finale del comando CREATE TABLE, che combina tutto ciò che abbiamo imparato.

Per prima cosa, definiamo la tabella students:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    major TEXT DEFAULT 'Undeclared',
    enrollment_year INT,
    CONSTRAINT year_check CHECK (enrollment_year >= 1900),
    CHECK (major IN (
        'Undeclared',
        'Computer Science',
        'Mathematics',
        'Biology',
        'Physics',
        'Chemistry',
        'Biochemistry'
    ))
);

Poi definiamo la tabella courses:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name TEXT NOT NULL,
    department TEXT NOT NULL,
    UNIQUE (course_name, department),
    CHECK (department IN (
        'Physics & Mathematics',
        'Sciences'
    ))
);

Infine, definiamo la tabella enrollments, stabilendo le relazioni tra studenti e corsi:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT CHECK (year >= 1900),
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id, year)
);

Abbiamo aggiunto alcuni vincoli in questo esempio finale. Un'università ha un insieme noto di dipartimenti e indirizzi di studi. Pertanto, l'insieme di valori che le colonne major e department possono assumere è finito e noto in anticipo. In queste situazioni, si consiglia di usare un vincolo CHECK per garantire che le colonne possano assumere solo valori da quell'insieme noto.

Conclusione

In questo articolo abbiamo esplorato i diversi tipi di vincoli di integrità in SQL e come implementarli usando PostgreSQL. Abbiamo trattato chiavi primarie, vincoli NOT NULL, vincoli UNIQUE, vincoli DEFAULT, vincoli CHECK e vincoli FOREIGN KEY, fornendo esempi pratici per ciascuno.

Comprendendo questi concetti, possiamo garantire l'accuratezza, la coerenza e l'affidabilità dei nostri dati.

Se vuoi saperne di più su come organizzare i dati in modo efficiente, dai un'occhiata a questo corso sul Database Design.